什么是外连接?
外连接(outer join)是一种在数据库中使用的关联操作,可以检索两个或多个表中的数据。它可以保留左表和右表中没有匹配的行,并将它们组合成一组结果。
左外连接
概念
左外连接(left outer join)是外连接的一种,它返回左表中的所有行和右表中匹配的行,如果右表中没有匹配的行,则返回 NULL 值。
语法
使用 LEFT OUTER JOIN 关键字来执行左外连接:
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name;
注意:LEFT OUTER JOIN 关键字可以简写为 LEFT JOIN。
实例
假设我们有两个表:orders 和 customers。orders 表包含订单信息(order_id, customer_id, order_date), customers 表包含客户信息(id, name, city)。
现在我们要查询每个订单所属的客户信息,如果订单没有对应的客户信息,则显示 NULL 值。
SQL 语句如下:
SELECT orders.order_id, customers.name, customers.city
FROM orders
LEFT JOIN customers
ON orders.customer_id = customers.id;
执行结果如下:
order_id | name | city |
---|---|---|
1 | Tom | New York |
2 | Mary | Los Angeles |
3 | John | Chicago |
4 | NULL | NULL |
5 | NULL | NULL |
分析:orders 表中有 5 条记录,其中前三条记录都可以在 customers 表中找到对应的客户信息,最后两条记录在 customers 表中找不到对应的客户信息,所以在执行左外连接时,这两条记录的 name 和 city 字段显示 NULL 值。
右外连接
概念
右外连接(right outer join)是外连接的一种,它返回右表中的所有行和左表中匹配的行,如果左表中没有匹配的行,则返回 NULL 值。
语法
使用 RIGHT OUTER JOIN 关键字来执行右外连接:
SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name = table2.column_name;
注意:RIGHT OUTER JOIN 关键字可以简写为 RIGHT JOIN。
实例
继续以上面的 orders 和 customers 表为例,如果我们要查询每个客户的订单信息,如果客户没有订单信息,则显示 NULL 值。
SQL 语句如下:
SELECT orders.order_id, customers.name, customers.city
FROM orders
RIGHT JOIN customers
ON orders.customer_id = customers.id;
执行结果如下:
order_id | name | city |
---|---|---|
1 | Tom | New York |
2 | Mary | Los Angeles |
3 | John | Chicago |
NULL | Jane | San Francisco |
分析:customers 表中有 4 条记录,其中前三条记录都可以在 orders 表中找到对应的订单信息,最后一条记录在 orders 表中找不到对应的订单信息,所以在执行右外连接时,这条记录的 order_id 字段显示 NULL 值。
全外连接
概念
全外连接(full outer join)是外连接的一种,它返回左表和右表中所有的行,如果左表和右表中没有匹配的行,则返回 NULL 值。
语法
在 MySQL 和 SQLite 中,没有直接支持全外连接的关键字,可以通过左外连接和右外连接来模拟。
在 SQL Server 和 Oracle 中,可以使用 FULL OUTER JOIN 关键字来执行全外连接:
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;
注意:FULL OUTER JOIN 关键字可以简写为 FULL JOIN。
实例
使用上面的 orders 和 customers 表为例,如果我们要查询每个客户对应的订单信息,如果客户没有订单信息或订单没有对应的客户信息,则显示 NULL 值。
在 MySQL 和 SQLite 中,可以使用 UNION 和左右外连接来模拟全外连接:
SELECT orders.order_id, customers.name, customers.city
FROM orders
LEFT JOIN customers
ON orders.customer_id = customers.id
UNION
SELECT orders.order_id, customers.name, customers.city
FROM orders
RIGHT JOIN customers
ON orders.customer_id = customers.id;
在 SQL Server 和 Oracle 中,可以使用 FULL OUTER JOIN 关键字来执行全外连接:
SELECT orders.order_id, customers.name, customers.city
FROM orders
FULL JOIN customers
ON orders.customer_id = customers.id;
执行结果如下:
order_id | name | city |
---|---|---|
1 | Tom | New York |
2 | Mary | Los Angeles |
3 | John | Chicago |
4 | NULL | NULL |
5 | NULL | NULL |
NULL | Jane | San Francisco |
分析:在 MySQL 和 SQLite 中,使用 UNION 将左右外连接的结果合并在一起,从而得到全外连接的结果;在 SQL Server 和 Oracle 中,使用 FULL OUTER JOIN 直接得到全外连接的结果。
总结
外连接是一种在数据库中使用的关联操作,可以在两个或多个表中检索数据。左外连接(left outer join)返回左表中的所有行和右表中匹配的行,如果右表中没有匹配的行,则返回 NULL 值;右外连接(right outer join)返回右表中的所有行和左表中匹配的行,如果左表中没有匹配的行,则返回 NULL 值;全外连接(full outer join)返回左表和右表中所有的行,如果左表和右表中没有匹配的行,则返回 NULL 值。