How Do INNER, LEFT, RIGHT, and FULL JOINs Differ in SQL?

In-depth understanding of SQL JOIN: detailed explanation of INNER, LEFT, RIGHT and FULL JOIN

SQL JOIN is a core operation in data retrieval, allowing us to combine rows from multiple tables based on common fields. In MySQL, there are several JOIN types to choose from, each providing a specific way of handling matching and unmatched rows.

Differences between different SQL JOIN types

INNER JOIN

• Join two tables based on matching conditions.
• Only return rows whose common fields match in both tables.

LEFT JOIN

• Join two tables based on matching conditions.
• Returns all rows from the left table, even if there are no matching rows in the right table.
• Unmatched rows in the right table are filled with NULL values.

RIGHT JOIN

• Similar to LEFT JOIN, but returns all rows from the right table, even if there are no matching rows in the left table.
• Unmatched rows in the left table are filled with NULL values.

FULL JOIN

• The result of combining LEFT and RIGHT OUTER JOIN.
• Returns all rows from both tables, regardless of whether there is a match.
• Unmatched rows in either table are filled with NULL values.

Actual case

Consider the following example table:

<code>表A：
| id | firstName | lastName |
|---|---|---|
| 1 | Arun | Prasanth |
| 2 | Ann | Antony |
| 3 | Sruthy | Abc |
| 6 | New | Abc |

表B：
| id2 | age | place |
|---|---|---|
| 1 | 24 | Kerala |
| 2 | 24 | Usa |
| 3 | 25 | Ekm |
| 5 | 24 | Chennai |</code>

INNER JOIN:

<code>SELECT *
FROM 表A
INNER JOIN 表B
ON 表A.id = 表B.id2;</code>

Result:

firstName	lastName	age	place
Arun	Prasanth	24	Kerala
Ann	Antony	24	Usa
Sruthy	Abc	25	Ekm

LEFT JOIN:

<code>SELECT *
FROM 表A
LEFT JOIN 表B
ON 表A.id = 表B.id2;</code>

Result:

firstName	lastName	age	place
Arun	Prasanth	24	Kerala
Ann	Antony	24	Usa
Sruthy	Abc	25	Ekm
New	Abc	NULL	NULL

RIGHT JOIN:

<code>SELECT *
FROM 表A
RIGHT JOIN 表B
ON 表A.id = 表B.id2;</code>

Result:

firstName	lastName	age	place
Arun	Prasanth	24	Kerala
Ann	Antony	24	Usa
Sruthy	Abc	25	Ekm
NULL	NULL	24	Chennai

Full Join:

<code>表A：
| id | firstName | lastName |
|---|---|---|
| 1 | Arun | Prasanth |
| 2 | Ann | Antony |
| 3 | Sruthy | Abc |
| 6 | New | Abc |

表B：
| id2 | age | place |
|---|---|---|
| 1 | 24 | Kerala |
| 2 | 24 | Usa |
| 3 | 25 | Ekm |
| 5 | 24 | Chennai |</code>

<:> Result:

The above is the detailed content of How Do INNER, LEFT, RIGHT, and FULL JOINs Differ in SQL?. For more information, please follow other related articles on the PHP Chinese website!