在 SQL Server 中使用 LEFT JOIN 时 AND 子句与 ON 和 WHERE 子句的用法区别
4.83/5 (148投票s)
本文阐述了 LEFT JOIN 中 ON 子句和 WHERE 子句的区别。
引言
本文旨在说明在 SQL Server 中使用 LEFT JOIN 时，AND 子句与 ON 子句和 WHERE 子句之间的区别。
背景
我在 SQL Server 数据库方面工作了 5 年多，但却不知道在 LEFT JOIN 中 ON 子句和 WHERE 子句的区别。有一次，我问我们的一位 DBA（他有 10 多年的经验），他说有区别，但他无法解释。然后我开始自己探索这个话题，并且在 LEFT JOIN 中使用这两种子句时得到了一些有趣的观察。所以今天我决定分享我的知识，以便大家都能从我的工作中受益。
使用代码
今天我将解释在 SQL Server 中使用 LEFT JOIN 时 ON 子句和 WHERE 子句的区别。当 OUTTER JOIN 中使用 ON 子句时，外层表不会影响 ON 子句，所有来自外层表的行都会被返回，而 ON 子句决定了辅助表的哪些行可以与外层表进行连接。外层表中不满足 JOIN 中 ON 子句指定条件的行，其辅助列（辅助表的列）将用 NULL 值扩展；而 WHERE 子句则过滤实际返回到最终输出中的行。
从上面的定义很难理解，所以让我们通过一个例子来理解这种区别。假设我们有两个表 Departments（deptId, deptName）和 Employees（empID, DeptID, EmpName, Salary），其中 deptID 是部门表的外键。一个员工只能有一个部门，而一个部门可以有许多员工。
我们在 Departments 表中有这些示例数据
DeptId DeptName
----------- ---------------
1 HR
2 Payroll
3 Admin
4 Marketing
5 HR & Accounts
(5 rows)
以下是 Employees 表的示例数据
EmpId EmpName DeptId EmpSalary
----------- --------------- ----------- ---------------------
1 John 1 5000.00
2 Albert 1 4500.00
3 Crain 2 6000.00
4 Micheal 2 5000.00
5 David NULL 34.00
6 Kelly NULL 457.00
7 Rudy 1 879.00
8 Smith 2 7878.00
9 Karsen 5 878.00
10 Stringer 5 345.00
11 Cheryl NULL NULL
(11 rows)
案例 1
假设我们被要求显示所有员工及其相关部门，那么我们应该这样写查询
select * from employees e
<p>left join departments d on e.deptid = d.deptid</p>
(query1)
此查询的结果是
EmpId EmpName DeptId EmpSalary DeptId DeptName
----------- --------------- ----------- --------------------- ----------- ---------------
1 John 1 5000.00 1 HR
2 Albert 1 4500.00 1 HR
3 Crain 2 6000.00 2 Payroll
4 Micheal 2 5000.00 2 Payroll
5 David NULL 34.00 NULL NULL
6 Kelly NULL 457.00 NULL NULL
7 Rudy 1 879.00 1 HR
8 Smith 2 7878.00 2 Payroll
9 Karsen 5 878.00 5 HR & Accounts
10 Stringer 5 345.00 5 HR & Accounts
11 Cheryl NULL NULL NULL NULL
(11 rows)
(Table 1)
在这里，您可以看到所有 Employees 表的行都已列出，以及他们的部门，如果他们有部门的话；否则，deptid 和 deptname 为 null。
案例 2
假设我们被要求显示所有员工以及仅属于“HR”或“HR & Accounts”部门的员工的部门名称列表；那么我们应该这样写查询
select * from employees e
left join departments d on e.deptid = d.deptid
and ( d.deptname = 'HR' or d.deptname = 'HR & Accounts')
(query 2)
此查询的结果是
EmpId EmpName DeptId EmpSalary DeptId DeptName
----------- --------------- ----------- --------------------- ----------- ---------------
1 John 1 5000.00 1 HR
2 Albert 1 4500.00 1 HR
3 Crain 2 6000.00 NULL NULL
4 Micheal 2 5000.00 NULL NULL
5 David NULL 34.00 NULL NULL
6 Kelly NULL 457.00 NULL NULL
7 Rudy 1 879.00 1 HR
8 Smith 2 7878.00 NULL NULL
9 Karsen 5 878.00 5 HR & Accounts
10 Stringer 5 345.00 5 HR & Accounts
11 Cheryl NULL NULL NULL NULL
(11 rows)
Table (2)
您会注意到这里返回的行数与查询 1 相同，但这里只列出了部门名称为“HR”或“HR & Accounts”的员工的部门。正如您所见，“Crain”、“Micheal”和“Smith”拥有“Payroll”部门（参见表 1），但它没有出现在上面的结果集中，因为我们在这里将过滤器应用于“On 子句”；因此，只有部门名称为“HR”或“HR & Employee”的行才成为 JOIN 的一部分，结果是，所有其他没有“HR”和“HR & Accoutns”部门名称的员工都显示其部门名称为 null。这是 LEFT JOIN 中 On 子句的示例。
案例 3
如果我们被要求仅显示部门名称为“HR”或“HR & Accounts”的员工，该怎么办？我们应该这样写查询
select * from employees e
left join departments d on e.deptid = d.deptid
where ( d.deptname = 'HR' or d.deptname = 'HR & Accounts')
(query 3)
此查询的结果是
EmpId EmpName DeptId EmpSalary DeptId DeptName
----------- --------------- ----------- --------------------- ----------- ---------------
1 John 1 5000.00 1 HR
2 Albert 1 4500.00 1 HR
7 Rudy 1 879.00 1 HR
9 Karsen 5 878.00 5 HR & Accounts
10 Stringer 5 345.00 5 HR & Accounts
(5 rows)
Table (3)
您可以在此处看到，只列出了部门为“HR”或“HR & Accounts”的员工，所以我们这里做的是与查询 1 相同的查询。您可以查看表 1 中查询 1 的结果，然后我们只是在 Where 子句中应用了一个过滤器来过滤行，并仅返回部门名称为“HR”或“HR & Accounts”的员工。这是 LEFT JOIN 中 Where 子句的示例。
现在您已经看到了所有三种情况，并且可以看到它们之间是如何不同的。这是 (1:1) 一对一表的一个示例，因为一个员工只能有一个部门。让我们用 (1:M) 来执行上述 3 种情况，即一对多表关系，其中一个部门可以有多个员工（或者一个部门可以有多个员工）。
案例 4
假设我们的需求是获取所有部门以及这些部门中列出的员工，那么我们将编写如下查询
select * from departments d
left join employees e on e.deptId = d.deptId
(query 4)
它将返回 Departments 表的所有行，以及 Employees 表中只有部门的那些行。结果将是
DeptId DeptName EmpId EmpName DeptId EmpSalary
----------- --------------- ----------- --------------- ----------- ---------------------
1 HR 1 John 1 5000.00
1 HR 2 Albert 1 4500.00
1 HR 7 Rudy 1 879.00
2 Payroll 3 Crain 2 6000.00
2 Payroll 4 Micheal 2 5000.00
2 Payroll 8 Smith 2 7878.00
3 Admin NULL NULL NULL NULL
4 Marketing NULL NULL NULL NULL
5 HR & Accounts 9 Karsen 5 878.00
5 HR & Accounts 10 Stringer 5 345.00
(10 rows)
(Table 4)
您可以在结果中看到，所有部门都已列出，即使是那些没有员工的部门，如“Admin”和“Marketing”。
案例 5
但是，如果我们被要求显示所有部门以及仅属于“HR 或 “HR & Accounting”部门的员工，该怎么办？然后我们应该这样写查询
select * from departments d
left join employees e on e.deptId = d.deptId and
( d.deptname = 'HR' or d.deptname = 'HR & Accounts')
(query 5)
它将显示以下记录
DeptId DeptName EmpId EmpName DeptId EmpSalary
----------- --------------- ----------- --------------- ----------- ---------------------
1 HR 1 John 1 5000.00
1 HR 2 Albert 1 4500.00
1 HR 7 Rudy 1 879.00
2 Payroll NULL NULL NULL NULL
3 Admin NULL NULL NULL NULL
4 Marketing NULL NULL NULL NULL
5 HR & Accounts 9 Karsen 5 878.00
5 HR & Accounts 10 Stringer 5 345.00
(8 rows)
Table (5)
您可以在结果中看到，这是 **On 子句在 LEFT JOIN 中**；它只返回 Departments 表的所有行，以及 Employees 表中只有“HR”或“HR & Accounts”部门的那些行。我们在 Payroll 部门有员工（表 4 是查询 4 的结果），但它没有出现在上面的结果集中，因为我们在“On 子句”中增加了一个条件，即部门名称应该是“HR”或“HR & Accounts”。因此，只有部门名称为“HR”和“HR & Accounts”的行才属于此 JOIN。因此，Departments 表的所有行都已返回，而 Employees 表中只有那些部门为“HR”或“HR & Accounts”的匹配行已被返回。
案例 6
如果我们被要求仅显示“HR”和“HR & Accounts”部门及其相关员工，该怎么办？然后我们应该这样写查询
select * from departments d
left join employees e on e.deptId = d.deptId
where ( d.deptname = 'HR' or d.deptname = 'HR & Accounts')
(query 6)
上述查询的结果将是
DeptId DeptName EmpId EmpName DeptId EmpSalary
----------- --------------- ----------- --------------- ----------- ---------------------
1 HR 1 John 1 5000.00
1 HR 2 Albert 1 4500.00
1 HR 7 Rudy 1 879.00
5 HR & Accounts 9 Karsen 5 878.00
5 HR & Accounts 10 Stringer 5 345.00
(5 rows)
(Table 6)
这里发生了什么？我们只是做了查询 4 中的 LEFT JOIN，您可以在表 4 中看到结果。然后我们应用一个过滤器来过滤结果集，只返回 deptNmae 为“HR”或“HR&Accounts”的行。**这是 LEFT JOIN 中的 Where 子句**。现在您可以看到它与我们在 On 子句中放置此条件的查询 5 的结果有何不同。
当与 INNER JOIN 一起使用时，Where 子句和 On 子句之间没有区别。
我向您展示了六种不同的情况来解释这个概念。希望您喜欢阅读这篇文章。我附上了一个脚本，可用于创建表和示例数据，然后您可以自行练习。我期待您的反馈。