The JOIN can be roughly divided into the following three categories according to its functions:
A INNER JOIN B on condition (inner join, or equivalent join): gets the record of the matching relationship between the fields in two tables.
A LEFT JOIN B on condition (left join): gets all records in the left table, even if the right table does not have corresponding matching records.
A RIGHT JOIN B on condition (right join): opposite to LEFT JOIN, it is used to get all records in the right table, even if there is no corresponding matching record in the left table.
Internal connection query
The syntax of the INNER JOIN clause is as follows:
SELECT column_list FROM t1 INNER JOIN t2 ON join_condition1 INNER JOIN t3 ON join_condition2 ... WHERE where_conditions;
MySQL INNER JOIN supports operators other than equal, but it can also use other operators greater than (>), less than (<) and not equal to (<) operators to form join predicates.
Example:
#The data of the two tables are as follows:
mysql> select * from girl; +-----+--------+ | hid | bname | +-----+--------+ | 3 | silently | | 2 | Blush and go red | | 5 | Petrel | +-----+--------+ 3 rows in set (0.00 sec) mysql> select * from boy;
+-----+--------+ | hid | bname | +-----+--------+ | 1 | lisi | | 2 | Wang Wu | | 3 | Zhao Liu | +-----+--------+ 3 rows in set (0.00 sec)
#Query the bname in boy table and girl table where hid wants to be the same.
mysql> select boy.hid,boy.bname,girl.hid,girl.bname -> from -> boy inner join girl on boy.hid=girl.hid; +-----+--------+-----+--------+ | hid | bname | hid | bname | +-----+--------+-----+--------+ | 3 | Zhao Liu | 3 | silently | | 2 | Wang Wu | 2 | Blush and go red | +-----+--------+-----+--------+ 2 rows in set (0.01 sec)
#Query the data in the boy and girl tables, the row with boy.hid=3
mysql> select boy.hid,boy.bname,girl.hid,girl.bname from boy inner join girl on boy.hid=girl.hid where boy.hid=3; +-----+--------+-----+--------+ | hid | bname | hid | bname | +-----+--------+-----+--------+ | 3 | Zhao Liu | 3 | silently | +-----+--------+-----+--------+ 1 row in set (0.00 sec)
#Query the data in the boy and girl tables, "boy.hid=girl.hid=3" rows
mysql> select boy.hid,boy.bname,girl.hid,girl.bname from boy inner join girl on boy.hid=girl.hid where boy.hid and girl.hid=3; +-----+--------+-----+--------+ | hid | bname | hid | bname | +-----+--------+-----+--------+ | 3 | Zhao Liu | 3 | silently | +-----+--------+-----+--------+ 1 row in set (0.00 sec)
Note: the conditions of WHERE clause will not be used in the matching phase. The conditions of WHERE clause will not be used until the matching phase is completed. It will retrieve the filter from the data generated in the matching phase
Left connection query
MySQL LEFT JOIN is different from join. MySQL LEFT JOIN will read all the data in the left data table, even if there is no corresponding data in the right table
Example:
#All data in the left table shall prevail. If all data in the right table are queried, null will not be used instead of matching.
mysql> select boy.hid,boy.bname,girl.hid,girl.bname from boy left join girl on boy.hid=girl.hid; +-----+--------+------+--------+ | hid | bname | hid | bname | +-----+--------+------+--------+ | 3 | Zhao Liu | 3 | silently | | 2 | Wang Wu | 2 | Blush and go red | | 1 | lisi | NULL | NULL | +-----+--------+------+--------+ 3 rows in set (0.00 sec)
Note: if no row data in table B matches the condition of ON, an extra row of NULL data will be generated.
Right connection query
MySQL RIGHT JOIN will read all the data in the right data table, even if there is no corresponding data in the left side table.
mysql> select boy.hid,boy.bname,girl.hid,girl.bname from boy right join girl on boy.hid=girl.hid; +------+--------+-----+--------+ | hid | bname | hid | bname | +------+--------+-----+--------+ | 2 | Wang Wu | 2 | Blush and go red | | 3 | Zhao Liu | 3 | silently | | NULL | NULL | 5 | Petrel | +------+--------+-----+--------+ 3 rows in set (0.00 sec)
The difference between the three connections:
