Explanation of various Joins in SQL (Inner/Left/Right diagram)

Preface

When talking about the differences between various JOINs in SQL in various Q&A communities, the most widely cited one is on CodeProject C.L. Moffatt articles Visual Representation of SQL Joins, he really explained it simply and clearly, using Venn diagrams to help understanding, and the effect was obvious. This article will follow his explanation method, with a little deduction, and can be regarded as a rough Chinese translation of the article.

Agreement

Two database tables Table_A and Table_B will be used below for example explanation. Their structures and data are as follows:

mysql> SELECT * FROM Table_A ORDER BY PK ASC; +----+---------+ | PK | Value | +----+---------+ | 1 | both ab | | 2 | only a | both ab | | 3 | only b | +----+---------+ 2 rows in set (0.00 sec)  

The record with PK 1 exists in both Table_A and Table_B, 2 is unique to Table_A, and 3 is unique to Table_B.

Commonly used JOINs

INNER JOIN

INNER JOIN is generally translated as inner connection. Inner join queries can return data that can be associated in the left table (Table A) and the right table (Table B).

Venn diagram:

Example query:

SELECT A.PK AS A_PK, B.PK AS B_PK, A.Value AS A_Value, B.Value AS B_Value FROM Table_A A INNER JOIN Table_B B ON A.PK = B.PK; 

Query results:

+------+------+---------+---------+ | A_PK | B_PK | A_Value | B_Value | +------+------+---------+---------+ | 1 | 1 | both ab | both ab |  

Note: Among them A 为 Table_A alias,B 为 Table_B The alias of , the same below.

LEFT JOIN

LEFT JOIN is generally translated as left join, also written as LEFT OUTER JOIN. A left join query will return all records in the left table (Table A), regardless of whether there is related data in the right table (Table B). Related data columns found in the right table will also be returned together.

Venn diagram:

Example query:

SELECT A.PK AS A_PK, B.PK AS B_PK, A.Value AS A_Value, B.Value AS B_Value FROM Table_A A LEFT JOIN Table_B B ON A.PK = B.PK; 

Query results:

+------+------+---------+---------+ | A_PK | B_PK | A_Value | B_Value | +------+------+---------+---------+ | 1 | 1 | both ab | both ba | | 2 | NULL | only a |  

RIGHT JOIN

RIGHT JOIN is generally translated as right join, also written as RIGHT OUTER JOIN. A right join query will return all records in the right table (Table B), regardless of whether there is related data in the left table (Table A). Related data columns found in the left table are also returned together.

Venn diagram:

Example query:

SELECT A.PK AS A_PK, B.PK AS B_PK, A.Value AS A_Value, B.Value AS B_Value FROM Table_A A RIGHT JOIN Table_B B ON A.PK = B.PK; 

Query results:

+------+------+---------+---------+ | A_PK | B_PK | A_Value | B_Value | +------+------+---------+---------+ | 1 | 1 | both ab | both ba |  

FULL OUTER JOIN

FULL OUTER JOIN is generally translated as outer join or full join. In the actual query statement, it can be written as FULL OUTER JOIN 或 FULL JOIN. The outer join query can return all the records in the left and right tables, and the records that can be related in the left and right tables are returned after being connected.

Venn diagram:

Example query:

SELECT A.PK AS A_PK, B.PK AS B_PK, A.Value AS A_Value, B.Value AS B_Value FROM Table_A A FULL OUTER JOIN Table_B B ON A.PK = B.PK; 

Query results:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL OUTER JOIN Table_B B ON A.PK = B.PK' at line 4  

Note: My current example uses MySQL which does not support FULL OUTER JOIN。

What should be returned (simulated using UNION):

mysql> SELECT * -> FROM Table_A -> LEFT JOIN Table_B -> ON Table_A.PK = Table_B.PK -> UNION ALL -> SELECT * -> FROM Table_A -> RIGHT JOIN Table_B -> ON Table_A.PK = Table_B.PK -> WHERE Table_A.PK IS NULL; +-------+----------+-------+----------+ | PK | Value | PK | Value | +------+---------+------+---------+ | 1 | both ab | 1 | both ba | | 2 | only a | NULL | NULL |  

summary

The above four are the common types and concepts of JOIN in SQL. Take a look at their group photo:

Have you ever felt that something is missing? There are more than just these situations when learning mathematical sets? Indeed, keep reading.

Extended usage

LEFT JOIN EXCLUDING INNER JOIN

Returns a record set that has associated data in the left table but not in the right table.

Venn diagram:

Example query:

SELECT A.PK AS A_PK, B.PK AS B_PK, A.Value AS A_Value, B.Value AS B_Value FROM Table_A A LEFT JOIN Table_B B ON A.PK = B.PK WHERE B.PK IS NULL; 

Query results:

+------+------+---------+---------+ | A_PK | B_PK | A_Value | B_Value | +------+------+---------+---------+ | 2 | NULL | only a | NULL |  

RIGHT JOIN EXCLUDING INNER JOIN

Returns a record set with data in the right table but no related data in the left table.

Venn diagram:

Example query:

SELECT A.PK AS A_PK, B.PK AS B_PK, A.Value AS A_Value, B.Value AS B_Value FROM Table_A A RIGHT JOIN Table_B B ON A.PK = B.PK WHERE A.PK IS NULL; 

Query results:

+------+------+---------+---------+ | A_PK | B_PK | A_Value | B_Value | +------+------+---------+---------+ | NULL | 3 | NULL | only b |  

FULL OUTER JOIN EXCLUDING INNER JOIN

Returns a set of records that are not related to each other in the left table and the right table.

Venn diagram:

Example query:

SELECT A.PK AS A_PK, B.PK AS B_PK, A.Value AS A_Value, B.Value AS B_Value FROM Table_A A FULL OUTER JOIN Table_B B ON A.PK = B.PK WHERE A.PK IS NULL OR B.PK IS NULL; 

Because FULL OUTER JOIN is used, MySQL reports an error again when executing this query.

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL OUTER JOIN Table_B B ON A.PK = B.PK WHERE A.PK IS NULL OR B.PK IS NULL' at line 4  

The result that should be returned (simulated with UNION):

mysql> SELECT * -> FROM Table_A -> LEFT JOIN Table_B -> ON Table_A.PK = Table_B.PK -> WHERE Table_B.PK IS NULL -> UNION ALL -> SELECT * -> FROM Table_A -> RIGHT JOIN Table_B -> ON Table_A.PK = Table_B.PK -> WHERE Table_A.PK IS NULL; +------+--------+------+--------+ | PK | Value | PK | Value | +------+--------+------+--------+ | 2 | only a | NULL | NULL |  

Summarize

The above seven usages can basically cover various JOIN queries. Seven ways to use family portraits:

Looking at them, I seem to have returned to the days when I was learning mathematics and finding intersections and unions...

Post it by the way C.L. Moffatt Pictures with SQL statements, combined with learning, have a better flavor:

Update: More JOINs

In addition to the above, there are more JOIN usages, such as CROSS JOIN (Decartes set), SELF JOIN, you can refer to SQL JOINS Slide Presentation study.

CROSS JOIN

Returns the Cartesian set of matching records between the left and right tables.

Illustration:

Example query:

SELECT A.PK AS A_PK, B.PK AS B_PK, A.Value AS A_Value, B.Value AS B_Value FROM Table_A A CROSS JOIN Table_B B; 

Query results:

+------+------+---------+----------+ | A_PK | B_PK | A_Value | B_Value | +------+------+---------+---------+ | 1 | 1 | both ab | both ba | | 2 | 1 | only a | both ba | +------+------+---------+----------+ 4 rows in set (0.00 sec)  

The results of several JOIN queries mentioned above can be simulated using CROSS JOIN and conditions, such as INNER JOIN corresponding CROSS JOIN ... WHERE A.PK = B.PK。

SELF JOIN

Returns the records that meet the conditions after the table is connected to itself. It is generally used when a field in the table uses the primary key as a foreign key.

For example, the structure and data of Table_C are as follows:

+--------+----------+-------------+ | EMP_ID | EMP_NAME | EMP_SUPV_ID | +--------+----------+-------------+ | 1001 | Ma | NULL |  

The EMP_ID field represents the employee ID, the EMP_NAME field represents the employee name, and EMP_SUPV_ID represents the supervisor ID.

Example query:

Now if we want to query all employees with supervisors and their corresponding supervisor IDs and names, we can use SELF JOIN to achieve this.

SELECT A.EMP_ID AS EMP_ID, A.EMP_NAME AS EMP_NAME, B.EMP_ID AS EMP_SUPV_ID, B.EMP_NAME AS EMP_SUPV_NAME FROM Table_C A, Table_C B WHERE A.EMP_SUPV_ID = B.EMP_ID; 

Query results:

+--------+----------+-------------+---------------+ | EMP_ID | EMP_NAME | EMP_SUPV_ID | EMP_SUPV_NAME | +--------+----------+-------------+---------------+ | 1002 |  

Additional information

1. The figures in this article were drawn using Keynote;
2. My personal experience is that the JOIN query in SQL is very similar to the intersection, union, etc. in mathematics;
3. SQLite does not support RIGHT JOIN and FULL OUTER JOIN, you can use LEFT JOIN and UNION to achieve the same effect;
4. MySQL does not support FULL OUTER JOIN, you can use LEFT JOIN and UNION to achieve the same effect;

Original text from: https://mazhuang.org/2017/09/11/joins-in-sql/