SQL 左连接与 FROM 行上的多个表? [英] SQL left join vs multiple tables on FROM line?

问题描述

大多数 SQL 方言都接受以下两种查询:

Most SQL dialects accept both the following queries:

SELECT a.foo, b.foo
FROM a, b
WHERE a.x = b.x

SELECT a.foo, b.foo
FROM a
LEFT JOIN b ON a.x = b.x

现在很明显，当您需要外连接时，需要第二种语法.但是在进行内连接时，为什么我应该更喜欢第二种语法而不是第一种(反之亦然)?

Now obviously when you need an outer join, the second syntax is required. But when doing an inner join why should I prefer the second syntax to the first (or vice versa)?

推荐答案

仅列出表格并使用 WHERE 子句指定连接条件的旧语法在大多数情况下已被弃用现代数据库.

The old syntax, with just listing the tables, and using the WHERE clause to specify the join criteria, is being deprecated in most modern databases.

这不仅仅是为了展示，当您在同一查询中同时使用 INNER 和 OUTER 连接时，旧语法可能会产生歧义.

It's not just for show, the old syntax has the possibility of being ambiguous when you use both INNER and OUTER joins in the same query.

让我举个例子.

假设您的系统中有 3 个表:

Let's suppose you have 3 tables in your system:

Company
Department
Employee

每个表包含许多行，链接在一起.你有多个公司，每个公司可以有多个部门，每个部门可以有多个员工.

Each table contain numerous rows, linked together. You got multiple companies, and each company can have multiple departments, and each department can have multiple employees.

好的，现在您要执行以下操作:

Ok, so now you want to do the following:

列出所有公司，包括其所有部门和所有员工.请注意，有些公司还没有任何部门，但请确保将它们也包括在内.确保您只检索有员工的部门，但始终列出所有公司.

List all the companies, and include all their departments, and all their employees. Note that some companies don't have any departments yet, but make sure you include them as well. Make sure you only retrieve departments that have employees, but always list all companies.

所以你这样做:

SELECT * -- for simplicity
FROM Company, Department, Employee
WHERE Company.ID *= Department.CompanyID
  AND Department.ID = Employee.DepartmentID

请注意，最后一个是内部联接，为了满足您只需要有人员的部门的标准.

Note that the last one there is an inner join, in order to fulfill the criteria that you only want departments with people.

好的，那么现在会发生什么.嗯，问题是，它取决于数据库引擎、查询优化器、索引和表统计信息.让我解释一下.

Ok, so what happens now. Well, the problem is, it depends on the database engine, the query optimizer, indexes, and table statistics. Let me explain.

如果查询优化器确定这样做的方法是先取公司，然后找到部门，然后与员工进行内部联接，那么您将不会得到任何没有部门的公司.

If the query optimizer determines that the way to do this is to first take a company, then find the departments, and then do an inner join with employees, you're not going to get any companies that don't have departments.

这样做的原因是 WHERE 子句决定了哪些 行 最终出现在最终结果中，而不是行的各个部分.

The reason for this is that the WHERE clause determines which rows end up in the final result, not individual parts of the rows.

在这种情况下，由于左连接，Department.ID 列将为 NULL，因此当涉及到 Employee 的 INNER JOIN 时，没有办法满足 Employee 行的约束，所以它不会出现.

And in this case, due to the left join, the Department.ID column will be NULL, and thus when it comes to the INNER JOIN to Employee, there's no way to fulfill that constraint for the Employee row, and so it won't appear.

另一方面，如果查询优化器决定先处理部门-员工联接，然后与公司进行左联接，您将看到它们.

On the other hand, if the query optimizer decides to tackle the department-employee join first, and then do a left join with the companies, you will see them.

所以旧语法是模棱两可的.没有办法指定你想要什么，不处理查询提示，有些数据库根本没有办法.

So the old syntax is ambiguous. There's no way to specify what you want, without dealing with query hints, and some databases have no way at all.

输入新语法，您可以选择.

Enter the new syntax, with this you can choose.

例如，如果您想要所有公司，如问题描述所述，您可以这样写:

For instance, if you want all companies, as the problem description stated, this is what you would write:

SELECT *
FROM Company
     LEFT JOIN (
         Department INNER JOIN Employee ON Department.ID = Employee.DepartmentID
     ) ON Company.ID = Department.CompanyID

在此指定您希望将部门-员工联接作为一个联接完成，然后将其结果与公司进行左联接.

Here you specify that you want the department-employee join to be done as one join, and then left join the results of that with the companies.

此外，假设您只需要名称中包含字母 X 的部门.同样，使用旧式联接，您也有失去公司的风险，如果它的名称中没有任何带有 X 的部门，但使用新语法，您可以这样做:

Additionally, let's say you only want departments that contains the letter X in their name. Again, with old style joins, you risk losing the company as well, if it doesn't have any departments with an X in its name, but with the new syntax, you can do this:

SELECT *
FROM Company
     LEFT JOIN (
         Department INNER JOIN Employee ON Department.ID = Employee.DepartmentID
     ) ON Company.ID = Department.CompanyID AND Department.Name LIKE '%X%'

这个额外的子句用于连接，但不是整行的过滤器.因此，该行可能会显示公司信息，但该行的所有部门和员工列中可能都有 NULL，因为该公司的名称中没有带有 X 的部门.旧语法很难做到这一点.

This extra clause is used for the joining, but is not a filter for the entire row. So the row might appear with company information, but might have NULLs in all the department and employee columns for that row, because there is no department with an X in its name for that company. This is hard with the old syntax.

这就是为什么在其他供应商中，Microsoft 自 SQL Server 2005 及更高版本以来已弃用旧的外连接语法，而不是旧的内连接语法.使用旧式外连接语法与运行在 Microsoft SQL Server 2005 或 2008 上的数据库对话的唯一方法是将该数据库设置为 8.0 兼容模式(也称为 SQL Server 2000).

This is why, amongst other vendors, Microsoft has deprecated the old outer join syntax, but not the old inner join syntax, since SQL Server 2005 and upwards. The only way to talk to a database running on Microsoft SQL Server 2005 or 2008, using the old style outer join syntax, is to set that database in 8.0 compatibility mode (aka SQL Server 2000).

此外，旧的方式，通过在查询优化器中抛出一堆表，以及一堆 WHERE 子句，类似于说在这里，尽你所能".使用新语法，查询优化器无需做太多工作即可找出哪些部分组合在一起.

Additionally, the old way, by throwing a bunch of tables at the query optimizer, with a bunch of WHERE clauses, was akin to saying "here you are, do the best you can". With the new syntax, the query optimizer has less work to do in order to figure out what parts goes together.

所以你有它.

LEFT 和 INNER JOIN 是未来的潮流.

LEFT and INNER JOIN is the wave of the future.

这篇关于SQL 左连接与 FROM 行上的多个表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！