Simply learn various SQL joins

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

The most common JOIN types: SQL INNER JOIN (simple JOIN), SQL LEFT JOIN, SQL RIGHT JOIN, SQL FULL JOIN, the former is an inner join, and the latter three are outer joins.

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

INNER JOIN

An inner join is the most common type of join and only joins 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

Note : INNER JOIN is the same as JOIN

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

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

The output of executing the above SQL is as follows:

LEFT JOIN

LEFT JOIN returns all rows of the left table and rows of the right table that meet the ON condition. If a row of the left table has no match in the right table, the corresponding data in the right table for this row is replaced by NULL.

LEFT JOIN Syntax

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

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

LEFT JOIN produces a complete set of table 1, and the matching values ​​in table 2 are replaced by null values ​​if there is no match.

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

The output of executing the above SQL is as follows:

RIGHT JOIN

RIGHT JOIN returns all rows of the right table and rows of the left table that meet the ON condition. If a row of the right table has no match in the left table, the corresponding data in the left table is replaced by NULL.

RIGHT JOIN Syntax

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

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

RIGHT JOIN produces a complete set of table 2, and the matching values ​​in table 1 are replaced by null values ​​if there is no match.

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

The output of executing the above SQL is as follows:

FULL OUTER JOIN

A FULL JOIN returns all rows from both the left and right tables. If a row in one table does not have a 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 

FULL OUTER JOIN produces the union of 1 and 2. However, it should be noted that for records that do not have a match, null will be used as the value.

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

The output of executing the above SQL is as follows:

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.