Shulou(Shulou.com)06/02 Report--

This article mainly explains "how to understand multi-table join query in MySQL series". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Now let the editor to take you to learn "MySQL series of how to understand multi-table join query"!

Catalogue

1. Cartesian product phenomenon

two。 Summary of connection query knowledge points

1) what is a join query?

2) Classification of join queries

3. Internal connection explanation

1) equivalent connection: the most important feature is that the connection condition is equivalent.

2) the difference between sql92 grammar and sql99 grammar.

3) non-equivalent connection: the most important feature is that the connection condition is non-equivalent.

4) self-join: the most important feature is that one table is treated as two tables.

4. External connection explanation

1) what is an external connection and what is the difference between an external connection and an internal connection?

2) Classification of external connections

1. Cartesian product phenomenon

The results are as follows:

The analysis is as follows:

The above results of    are definitely wrong. Each person in the left table has four boyfriends. A careful observation of these four records is exactly the result of matching each record in the left table with that in the right table.

The cause of the    Cartesian product phenomenon: there is no valid join condition between the two tables. Since you have no join conditions, the first row in this table must match all the rows in the other appearance, similarly, the second row in this table must be able to match all the rows in the other appearance, and so on, the last row m in this table can also match all the rows in the other appearance. If the other table has n rows, then the last number of rows displayed must be masked n rows.

If    does not want to produce Cartesian product, it needs to add valid table join conditions. For example, the boyfriend_id on the left represents their boyfriend only if it is equal to the id on the right.

After adding table join conditions:

As you can see, the final number of records generated by Cartesian product is the product of the respective data in the two tables. When the join query is not used, if the data in the two tables is particularly large, it will burst your memory, which is terrible. So we have to learn to use join queries.

two。 Summary of knowledge points of join query 1) what is a join query?

In the actual development, in most cases, the data is not queried from a single table, but the final result is generally extracted by the joint query of multiple tables. That is to say: in the actual business, a business is also composed of multiple tables, and different information is stored in different tables. If the information we want to obtain comes from multiple tables, you need to use join query.

2) Classification of join queries

① is classified by chronology

Full connection (full join) is not supported in MySQL, and the union keyword is generally used to complete the full connection function. The cross connection in MySQL is cross join, which is used less, and we don't have to worry about it.

Sq192 standard: only support inner connection; sq199 standard [recommended]: only support inner connection + outer connection (only support left outer connection and right outer connection) + cross connection; ② classifies inner connection by function: equivalent connection, non-equivalent connection, self-connection; outer connection: left outer connection, right outer connection, full outer connection (full join)

3. Internal connection explanation

The original data are as follows:

1) equivalent connection: the most important feature is that the connection condition is equivalent.

Exercise: query the employee name and the corresponding department name

The syntax of sql92 is as follows: (too old, generally not, just know what it means when you see it. )

Sql99 syntax: (commonly used)

2) the difference between sql92 grammar and sql99 grammar. -- sql92 syntax select ename,dnamefrom emp,dept where emp.deptno=dept.deptno;-- sql99 grammar select ename,dnamefrom emp (inner) join depton emp.deptno=dept.deptno;-- sql92 grammar and sql99 grammar 1) comma (",") replace (inner) join;2) where with on; Note: inner can be omitted, inner can increase the readability of the code. -- the advantage of sql99 syntax is the separation of table joins and subsequent where condition filtering. For sql92 syntax, table joins use where,where filtering as well as where, which is confused. 3) non-equivalent connection: the most important feature is that the connection condition is non-equivalent.

Exercise: find out the salary grade of each employee and show the employee's name, salary and salary grade.

4) self-join: the most important feature is that one table is treated as two tables.

What is meant by one table as two tables? In other words, a self-join is a join between the same table, and the join condition is the different fields in the table.

The biggest difference between people and machines is that people have the ability to judge. You know how to distinguish between different fields of a table, but machines don't know that they are all the same table and the field names are all the same. So how can the machine tell which table is which (for the same table).

This needs an alias. For the same table, I give it two names, one is An and the other is B, so that the machine can distinguish it very well. When you take the field in table A, it is "A. field", and the field in table B is "B. field".

Exercise: find out the superior leader of each employee, and ask to display the employee name and the corresponding leader name.

4. External connection explanation

The original data are as follows:

1) what is an external connection and what is the difference between an external connection and an internal connection?

① intra-connection

Assuming that tables An and B are joined and inner joins are used, whenever tables An and B can match the records on them, they will be queried. This is the inner join. There is no distinction between master and deputy in the two tables of AB, and the two tables are equal.

② external connection

Suppose tables An and B are joined and external joins are used, one of the two tables in AB is the main table and the other is the secondary table, which mainly queries the data in the main table, accompanied by the query secondary table. When the data in the secondary table does not match the data in the main table, the secondary table automatically simulates that the NULL matches it.

The most important feature of the external join is that all the data of the main table can be queried unconditionally.

2) Classification of external connections

The left connection has the right connection, and the right connection will also have the corresponding left connection. Therefore, in the process of learning MySQL, there is no need to learn both left and right connections.

Left outer join (left join): indicates that the table on the left is the main table. Right outer join (right join): indicates that the table on the right is the main table. 3) case study

Exercise: find out which department has no employees?

At this point, I believe you have a deeper understanding of "MySQL series of how to understand multi-table join query". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.