MySQL中INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL JOIN的区别

技术背景

在数据库操作中，经常需要从多个表中获取数据。SQL的JOIN子句就是用于根据表之间的共同字段，将多个表的行组合在一起，从而实现多表数据的联合查询。不同类型的JOIN可以满足不同的查询需求，理解它们之间的区别对于准确获取所需数据至关重要。

实现步骤

示例表数据

假设我们有两个表 TableA 和 TableB，数据如下：

TableA
id  firstName                  lastName
.......................................
1   arun                        prasanth                 
2   ann                         antony                   
3   sruthy                      abc                      
6   new                         abc

TableB
id2 age Place
................
1   24  kerala
2   24  usa
3   25  ekm
5   24  chennai

1. INNER JOIN

INNER JOIN 返回两个表中匹配的行，即两个表的交集。

• 语法：

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;

• 示例查询：

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
INNER JOIN TableB
ON TableA.id = TableB.id2;

• 查询结果：

firstName       lastName       age  Place
..............................................
arun            prasanth        24  kerala
ann             antony          24  usa
sruthy          abc             25  ekm

2. LEFT JOIN

LEFT JOIN 返回左表中的所有行，以及右表中匹配的行。如果右表中没有匹配的行，则右表的列将填充为 NULL。

• 语法：

SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field;

• 示例查询：

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
LEFT JOIN TableB
ON TableA.id = TableB.id2;

• 查询结果：

firstName                   lastName                    age   Place
...............................................................................
arun                        prasanth                    24    kerala
ann                         antony                      24    usa
sruthy                      abc                         25    ekm
new                         abc                         NULL  NULL

3. RIGHT JOIN

RIGHT JOIN 返回右表中的所有行，以及左表中匹配的行。如果左表中没有匹配的行，则左表的列将填充为 NULL。

• 语法：

SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;

• 示例查询：

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
RIGHT JOIN TableB
ON TableA.id = TableB.id2;

• 查询结果：

firstName                   lastName                    age     Place
...............................................................................
arun                        prasanth                    24     kerala
ann                         antony                      24     usa
sruthy                      abc                         25     ekm
NULL                        NULL                        24     chennai

4. FULL JOIN

FULL JOIN 结合了 LEFT JOIN 和 RIGHT JOIN 的结果，返回两个表中的所有行。如果某一行在另一个表中没有匹配的行，则对应的列将填充为 NULL。

• 语法：

SELECT table1.column1, table2.column2...
FROM table1
FULL JOIN table2
ON table1.common_field = table2.common_field;

• 示例查询：

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
FULL JOIN TableB
ON TableA.id = TableB.id2;

• 查询结果：

firstName                   lastName                    age    Place
...............................................................................
arun                        prasanth                    24    kerala
ann                         antony                      24    usa
sruthy                      abc                         25    ekm
new                         abc                         NULL  NULL
NULL                        NULL                        24    chennai

最佳实践

• 对于 INNER JOIN，由于其只返回匹配的行，在只需要获取两个表中共同数据时使用，可以提高查询效率。
• LEFT JOIN 和 RIGHT JOIN 在需要保留某一个表的所有数据，并关联另一个表的数据时使用。通常情况下，使用 LEFT JOIN 更常见，因为习惯上把主表放在左边。
• FULL JOIN 在需要获取两个表的所有数据时使用，但使用场景相对较少。

常见问题

• JOIN顺序问题：对于 INNER JOIN，连接表的顺序不影响结果；但对于 LEFT JOIN、RIGHT JOIN 和 FULL JOIN，连接表的顺序会影响结果。
• 性能问题：在进行多表连接时，如果表的数据量较大，可能会导致查询性能下降。可以通过创建合适的索引来优化查询性能。
• MySQL不支持标准的FULL JOIN：MySQL没有直接支持标准的 FULL JOIN 语法，可以使用 UNION 结合 LEFT JOIN 和 RIGHT JOIN 来模拟实现。例如：

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
LEFT JOIN TableB
ON TableA.id = TableB.id2
UNION
SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
RIGHT JOIN TableB
ON TableA.id = TableB.id2;