SQL LEFT JOIN

本文深入讲解了SQL中的LEFT JOIN关键字,解释了如何从左表返回所有行,即使右表中没有匹配行。通过实例展示了如何使用LEFT JOIN列出所有人及其订单信息,即便某些人没有订单。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

SQL LEFT JOIN 关键字

SQL LEFT JOIN 关键字

LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行,即使在右表 (table_name2) 中没有匹配的行。

LEFT JOIN 关键字语法

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

注释:在某些数据库中, LEFT JOIN 称为 LEFT OUTER JOIN。

原始的表 (用在例子中的):

"Persons" 表:

Id_PLastNameFirstNameAddressCity
1AdamsJohnOxford StreetLondon
2BushGeorgeFifth AvenueNew York
3CarterThomasChangan StreetBeijing

"Orders" 表:

Id_OOrderNoId_P
1778953
2446783
3224561
4245621
53476465

左连接(LEFT JOIN)实例

现在,我们希望列出所有的人,以及他们的定购 - 如果有的话。

您可以使用下面的 SELECT 语句:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

结果集:

LastNameFirstNameOrderNo
AdamsJohn22456
AdamsJohn24562
CarterThomas77895
CarterThomas44678
BushGeorge 

LEFT JOIN 关键字会从左表 (Persons) 那里返回所有的行,即使在右表 (Orders) 中没有匹配的行。

转载于:https://www.cnblogs.com/youngerger/p/8471461.html

The SQL LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, when there is no match. The basic syntax for a LEFT JOIN is as follows: ``` SELECT column1, column2, ... FROM table1 LEFT JOIN table2 ON table1.column = table2.column; ``` In this example, the columns selected from table1 and table2 are listed in the SELECT statement. The ON clause specifies the join condition, which specifies how the tables are related. For example, consider the following tables: **Customers** | CustomerID | CustomerName | ContactName | Country | |------------|--------------|-------------|---------| | 1 | Alfreds | Maria | Germany | | 2 | Ana Trujillo | Ana | Mexico | | 3 | Antonio | Antonio | Mexico | | 4 | Around the Horn | Thomas | UK | | 5 | Berglunds | Christina | Sweden | **Orders** | OrderID | CustomerID | OrderDate | |---------|------------|-----------| | 1 | 3 | 2021-01-01 | | 2 | 2 | 2021-01-02 | | 3 | 3 | 2021-01-03 | | 4 | 1 | 2021-01-04 | | 5 | 2 | 2021-01-05 | To get a list of all customers and their orders (if any), we can use the following SQL statement: ``` SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID; ``` This would return the following result: ``` | CustomerName | OrderID | |-----------------|---------| | Alfreds | 4 | | Ana Trujillo | 2 | | Ana Trujillo | 5 | | Antonio | 1 | | Antonio | 3 | | Around the Horn | NULL | | Berglunds | NULL | ``` Note that customers who have not placed an order yet appear in the result with NULL values for the OrderID column.
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值