The original link: www.codeproject.com/Articles/33…

1.Left JOIN

This query returns all records in the left table (Table A), regardless of whether they match any records in the right table (table B). It will also return any matching records in the right table. The join is written as follows:

SELECT <select_list>
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
Copy the code

2.Right JOIN

This query returns all records in the right table (Table B), regardless of whether they match any records in the left table (Table A). It will also return any matching records in the left table. The connection is written as follows:

SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key
Copy the code

3.Inner JOIN

This is the simplest, most understandable connection, and the most common. This query will return all records in the left table (table A) and the right table (table B) that have matching records.

SELECT <select_list> 
FROM Table_A A
INNER JOIN Table_B B
ON A.Key = B.Key
Copy the code

4.Left Excluding inner JOIN

This query will return all records in the left table (table A) that do not match any records in the right table (table B).

SELECT <select_list> 
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
WHERE B.Key IS NULL
Copy the code

5.Right th JOIN (Right th JOIN Excluding inner JOIN)

This query will return all records in the right table (table B) that do not match any records in the left table (table A).

SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL
Copy the code

6.Outer Excluding JOIN

This query will return all records in the left table (table A) and all mismatched records in the right table (table B). I still need to use this type of connection, but I often use all the others. The join is written as follows:

SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL OR B.Key IS NULL
Copy the code

Since mysql does not support full Join, this can only be implemented through the following code simulation

SELECT * FROM stu_movie A LEFT JOIN stu_student B ON A.cid = B.cid WHERE B.cid IS NULL UNION ALL SELECT * FROM stu_movie  A RIGHT JOIN stu_student B ON A.cid = B.cid WHERE A.cid IS NULL;Copy the code

Outer JOIN Outer JOIN Outer JOIN Outer JOIN

This join can also be called a full outer join or a full join. This query returns all the records from both tables, joining the records from the left table (Table A) that match the records from the right table (table B).

SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key
Copy the code

Mysql > select * from ‘left join’; select * from ‘right join’; select * from ‘right join’;

SELECT  A.cid AS A_cid,B.cid AS B_cid FROM stu_movie A LEFT JOIN  stu_student B ON A.cid = B.cid UNION SELECT  A.cid AS A_cid,B.cid AS B_cid FROM stu_movie A RIGHT JOIN  stu_student B ON A.cid= B.cid
Copy the code