Inner and Outer Join SQL statement

最新推荐文章于 2025-08-01 16:31:22 发布

原创最新推荐文章于 2025-08-01 16:31:22 发布 · 140 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SQL

技术专栏收录该内容

16 篇文章

订阅专栏

本文详细介绍了SQL中的三种连接类型：内连接(INNER JOIN)、左外连接(LEFT OUTER JOIN)和右外连接(RIGHT OUTER JOIN)。通过实例展示了如何使用这些连接来检索不同场景下的数据。

INNER JOIN: Retrieves customers with orders only. For example, you want to determine the amount ordered by each customer and you only want to see those who have ordered something

SELECT Customers.*, Orders.*
FROM Customers INNER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID

------------------------------------------------------------------------

LEFT OUTER JOIN: Retrieves all customers with or without orders. Order data for customers without orders appears as NULL values. For example, you want to determine the amount ordered by each customer and you need to see who has not ordered anything as well. You can also see the LEFT OUTER JOIN as a mirror image of the RIGHT OUTER JOIN if you switch the side of each table.

SELECT Customers.*, Orders.*
FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID

------------------------------------------------------------------------

RIGHT OUTER JOIN: Retrieves all orders with or without matching customer records. Customer data for orders without customers appears as NULL values. For example, you want to determine if there are any orders in the data with undefined CustomerID values (say, after a conversion or something like it). You can also see the RIGHT OUTER JOIN as a mirror image of the LEFT OUTER JOIN if you switch the side of each table.

SELECT Customers.*, Orders.*
FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID