MySQL series multi-table join query 92 and 99 syntax examples detailed tutorial

In the past two days, I changed the taste of the topic and taught everyone the principles of pyecharts and some graphics production. Today we continue to return to the series of learning MySQL and continue to learn MySQL database with you.

Multi-table query is a knowledge point that data analysts will definitely use in their daily work, which shows how important it is. Today's article comprehensively summarizes several situations of MySQL multi-table queries. Do you know MySQL's 92 syntax and 99 syntax? Do you know about equijoin and non-equijoin? Don’t think that you don’t need to know certain knowledge points because they are rarely used. In fact, it is not the case. At least after others write about them, you should know what they mean.

1. Cartesian product phenomenon

The results are as follows:

The analysis is as follows:
The above result is definitely wrong. Each person in the left table has 4 boyfriends. If you look closely at these 4 records, you will find that they are exactly the result of matching each record in the left table with each record in the right table.
The reason for the Cartesian product phenomenon: there is no valid connection condition between the two tables. Since you don't have a join condition, the first row in this table can definitely be matched one-to-one with all the rows in the other table. Similarly, the second row in this table can definitely be matched one-to-one with all the rows in the other table. And so on, the last row m in this table can also be matched one-to-one with all the rows in the other table. If the other table has n rows, then the number of rows displayed at the end must be m*n rows.
If you do not want to produce Cartesian product phenomenon, you need to add effective table join conditions. Taking the above example, the boyfriend_id in the left table only represents their boyfriends when it is equal to the id on the right.

After adding the table join condition:

It can be seen that the number of records finally generated by the Cartesian product is the product of the data in the two tables. When a join query is not used, if the data in the two tables is particularly large, your memory will explode, which is terrible, so we must learn to use join queries.

2. Summary of connection query knowledge points

1) What is a join query?

In actual development, in most cases, data is not queried from a single table, but multiple tables are usually queried together to obtain the final result. In other words: in 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 a join query.

2) Classification of connection queries

① Classification by era

MySQL does not support full joins, and the union keyword is generally used to complete the full join function. The cross join in MySQL is a cross join, which is rarely used and we don't need to care about it.

sq192 standard: only inner joins are supported; sq199 standard [recommended]: only inner joins + outer joins (only left outer and right outer) + cross joins are supported; ② Inner joins classified by function: equijoin, non-equijoin, self-join; outer joins: left outer join, right outer join, full outer join;

3. Inner connection explanation

The original data is as follows:

1) Equi-connection: The biggest feature is that the connection condition is an equal relationship.

Exercise: Query the employee name and the corresponding department name;

The sql92 syntax is as follows: (It is too old and generally not used. Just know what it means when you see it.)

sql99 syntax: (commonly used)

2) The difference between sql92 syntax and sql99 syntax.

 -- sql92 syntax select ename,dname
 from emp,dept
 where emp.deptno=dept.deptno;
 
-- sql99 syntax select ename,dname
from emp (inner) join dept
on emp.deptno=dept.deptno;

-- The difference between sql92 syntax and sql99 syntax 1) Comma (",") is replaced by (inner) join;
2) Replace "where" with "on";
Note: inner can be omitted. Writing inner can increase the readability of the code.

--The advantage of sql99 syntax is that the table connection and the subsequent where condition screening are separated.
For sql92 syntax, where is used for table join and where is also used for where filtering, which are mixed up and unclear.

3) Non-equivalue connection: The biggest feature is that the connection condition is a non-equivalue relationship.

Exercise: Find out the salary grade of each employee, and display the employee name, salary, and salary grade.

4) Self-join: The biggest feature is that one table is regarded as two tables.

What does it mean to treat one table as two tables? In other words, a self-join is a join between the same tables, and the join conditions are different fields in this table.

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

This requires an alias. For the same table, I give it two names, one is A and the other is B, so that the machine can distinguish them easily. When you take a field from table A, it is "A. field", and when you take a field from table B, it is "B. field".

Exercise: Find out the superior of each employee and display the employee name and the corresponding supervisor name.

4. External connection explanation

The original data is as follows:

1) What is an outer join and how is it different from an inner join?

① Inner join

Assuming that tables A and B are connected and an inner join is used, all records that match tables A and B will be queried. This is an inner join. There is no distinction between the main and the secondary tables AB; the two tables are equal.

② Outer connection

Assume that tables A and B are connected using an outer join, in which one of the two tables AB is the primary table and the other is the secondary table. The data in the primary table is mainly queried, and the secondary table is queried incidentally. When the data in the secondary table does not match the data in the primary table, the secondary table automatically simulates NULL to match it.

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

2) Classification of outer joins

There is a way to write a left join as a right join, and there is a corresponding way to write a right join as a left join. Therefore, in the process of learning MySQL, there is no need to learn both left and right joins.

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 Analysis

Exercise: Find out which department has no employees?

The above is the detailed content of the MySQL series of multi-table join query 92 and 99 syntax example detailed tutorials. For more information about MySQL query syntax, please pay attention to other related articles on 123WORDPRESS.COM!