The SQL JOIN clause is used to JOIN rows from two or more tables based on the common fields between the tables.

The most common JOIN types are: SQL INNER JOIN (simple JOIN), SQL LEFT JOIN, SQL RIGHT JOIN, and SQL FULL JOIN.

Suppose we have two tables, Table A is the left Table, and Table B is the right Table.

One, the INNER JOIN

Inner joins are the most common type of join, joining only matching rows.

Inner join syntax

select column_name(s)  
from table 1  
INNER JOIN table 2  
ON  
table 1.column_name=table 2.column_name  Copy the code

INNER JOIN is the same as JOIN

The result set generated by an INNER JOIN is the intersection of 1 and 2.

select * from Table A inner join Table B  
on Table A.id=Table B.id  Copy the code

The following output is displayed:

Second, LEFT to JOIN

LEFT JOIN returns all rows of the LEFT table and those of the right table that meet the ON condition. If the LEFT table does not match the right table, the corresponding rows of the right table are replaced with NULL.

LEFT JOIN syntax

select column_name(s)  
from table 1  
LEFT JOIN table 2  
ON table 1.column_name=table 2.column_name  Copy the code

Note: In some databases, a LEFT JOIN is called a LEFT OUTER JOIN

The LEFT JOIN produces the complete set of table 1, and the matches in table 2 have values, and the matches in table 2 are replaced by null values.

select * from Table A left join Table B  
on Table A.id=Table B.id  Copy the code

The following output is displayed:

Three, RIGHT to JOIN

RIGHT JOIN returns all the rows in the RIGHT table and the rows in the left table that meet the ON condition. If the rows in the RIGHT table do not match the rows in the left table, the corresponding rows in the left table are replaced with NULL.

RIGHT JOIN syntax

select column_name(s)  
from table 1  
RIGHT JOIN table 2  
ON table 1.column_name=table 2.column_name  Copy the code

Note: In some databases, a RIGHT JOIN is called a RIGHT OUTER JOIN

The RIGHT JOIN produces the complete set of table 2, while those in table 1 have values that match, and those that do not are replaced by null values.

select * from Table A right join Table B  
on Table A.id=Table B.id  Copy the code

The following output is displayed:

OUTER JOIN FULL OUTER JOIN

FULL JOIN returns all rows from the left and right tables. If a row in one table has no matching row in the other table, the opposite row is replaced with NULL

FULL OUTER JOIN syntax

select column_name(s)  
from table 1  
FULL OUTER JOIN table 2  
ON table 1.column_name=table 2.column_name  Copy the code

The FULL OUTER JOIN produces the union of 1 and 2. Note, however, that null is used for records that do not match.

select * from Table A full outer join Table B  
on Table A.id=Table B.id  Copy the code

The following output is displayed:


Author: Zhang Shuo

Source: cnblogs.com/reaptomorrow-flydream/