Data table Association left join / right join / inner join / full join / cross join
1. Connection introduction
At present, there are five association methods: left join / right join / inner join / full join / cross join. Different association effects will be presented according to different association methods.
-
A left join B: all records in table a are reserved, and all records not associated in table B are discarded;
-
A right join B: all records in table B are reserved, and all records not associated in table a are discarded;
-
A inner join B: take the intersection of the two, which means that the data of the result set must exist in both tables a and B! In actual use, the inner can be omitted, that is, it can be written as A join B;
-
A full join B: keep all records of table a and table B;
-
A cross join B: cross join is special. It means that table a and table B are Cartesian products, and there is no need to follow the condition of on column A = column B.
Suppose that there are m records in table A and n records in table B, then there will be m*n records in the final correlation result. We will further understand this through examples.
II. Example comparison
1. data preparation
- Create table statement:
CREATE TABLE kwan.`score` ( `student_id` int(8) DEFAULT NULL, `score` int(8) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE kwan.`student` ( `student_id` int(8) NOT NULL, `student_name` varchar(20) DEFAULT NULL, PRIMARY KEY (`student_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
- Insert statement:
INSERT INTO kwan.`student` VALUES ( 1, 'Xiao Ming' ), ( 2, 'Xiao Wang' ), ( 3, 'Xiao Zhang' ), ( 4, 'petty thief' ); INSERT INTO kwan.`score` VALUES ( 1, 100 ), ( 2, 60 ), ( 3, 98 ), ( 5, 78 );
-
select * from kwan.student;
-
select * from kwan.score;
Effect comparison
left join
-
SQL statement:
SELECT * FROM kwan.score LEFT JOIN kwan.student ON score.student_id = student.student_id;
-
In the above statement, score will be used as the main table and all records of the score table will be retained. If there is another record (4, 'Xiao Li') in the student table that is not associated with the score table, it will be discarded!
right join
-
SQL statement:
SELECT * FROM kwan.score RIGHT JOIN kwan.student ON score.student_id = student.student_id;
-
The above statement will use the student table as the main table. The records discarded in the previous example (4, 'Xiao Li') are retained, while the records discarded in the score table (5, 78) are discarded.
-
If the sudent table and the score table of the above statement are interchanged, the same effect will be achieved:
SELECT * FROM kwan.student LEFT JOIN kwan.score ON score.student_id = student.student_id;
inner join
-
SQL statement:
SELECT * FROM kwan.score INNER JOIN kwan.student ON score.student_id = student.student_id;
-
After the inner join, the empty records will be discarded, and only the records contained in both tables will be retained;
full join
-
SQL statement:
SELECT * FROM kwan.score FULL JOIN kwan.student ON score.student_id = student.student_id;
-
be careful:
Full Join is not supported in Mysql. If you need to use Full Join, you can use the following statements (union the results of left join and right join):
SELECT * FROM kwan.score LEFT JOIN kwan.student ON score.student_id = student.student_id UNION SELECT * FROM kwan.score RIGHT JOIN kwan.student ON score.student_id = student.student_id;
-
All records of the two tables will be retained, and those not associated will be blank;
-
cross join
The difference between cross join and left join / right join / inner join / full join is that cross join does not need Association conditions (note that it is not necessary, but not impossible)! It's just a Cartesian product of two tables.
-
SQL statement:
SELECT * FROM kwan.score CROSS JOIN kwan.student;
-