one. Overview of Join syntax
join is used to connect fields in multiple tables. The syntax is as follows:
... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona
table1: left table; table2: right table.
JOIN is roughly divided into the following three categories according to its functions:
INNER JOIN (inner join, or equivalent join): obtains records with a connection matching relationship in two tables.
LEFT JOIN (Left Join): Get the complete records of the left table (table1), that is, there is no corresponding matching record in the right table (table2).
RIGHT JOIN (right join): Contrary to LEFT JOIN, complete records of the right table (table2) are obtained, that is, there is no matching corresponding record in the left table (table1).
Note: mysql does not support Full join, but you can use the UNION keyword to combine LEFT JOIN and RIGHT JOIN to simulate a FULL join.
First look at the two tables in the experiment:
EXPLAIN SELECT * FROM comments gc JOIN comments_for gcf ON gc.comments_id=gcf.comments_id;
SELECT * FROM comments gc JOIN comments_for gcf ON gc.comments_id=gcf.comments_id WHERE gc.comments_id =2056
EXPLAIN SELECT * FROM comments gc JOIN comments_for gcf ON gc.order_id=gcf.product_id
EXPLAIN SELECT * FROM comments gc LEFT JOIN comments_for gcf ON gc.comments_id=gcf.comments_id
EXPLAIN SELECT * FROM comments_for gcf LEFT JOIN comments gc ON gc.comments_id=gcf.comments_id WHERE gcf.comments_id =2056
The above is the detailed content of Detailed introduction to the principle of MySQL JOIN. For more information, please follow other related articles on the PHP Chinese website!