The difference between left and right connections in MySQL

In MYSQL, you can use internal and external key links to merge data in related tables together for conditional filtering:

First create two new tables, the data is as follows:

student table data:

score table data:

You can see that the stu_id in the students table is 16048008 The record corresponding to the score table has no data;

1. When performing an inner connection, the system will automatically ignore the data that does not correspond to the two tables:

-- 显示内连接所有数据:SELECT * FROM students st INNER JOIN score sc ON st.sid=sc.stu_id;

Too much data, only screenshots of the most At the end:

You can see that the data is only displayed up to 16048007, and 16048008 is not displayed, so the inner connection only displays all related data.

2. Left link, Display all the data in the table on the left side of the keyword left. If there is less data in the right table, NULL values ​​will be added. If there is too much data, it will not be displayed.

-- 左外链接 leftSELECT * FROM students st LEFT JOIN score sc ON st.sid=sc.stu_id;

You can see that 16048008 is on the right There is no data in the score table, and the system uses NUll to fill it in,

SELECT * FROM score sc LEFT JOIN students st ON st.sid=sc.stu_id;

The picture above is the result of changing the positions of the students table and the score table. It can be seen that the original record 16048008 in the right table is because it is on the left There is no data corresponding to the clock, so it was deleted. It can be seen that the left link uses the data in the left table as a reference and displays all the data in the left table. The right table only displays the data corresponding to the left table, without adding NULl. Delete if there are too many;

Similarly, the right link is based on the right side, fill in NULL if there is less on the left, and delete if there are too many, so I won’t say more here;

It can be concluded from the above:

1. Inner join, displays all data related to the two tables;

2. Left link, uses the left table as a reference, displays all data;

3. Right link, display data with the right table as a reference;

Simple, don’t be impatient, move forward fully and steadily every day!

Recommended learning:mysql tutorial

The above is the detailed content of The difference between left and right connections in MySQL. For more information, please follow other related articles on the PHP Chinese website!