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.

这就是为什么在其他供应商中，自SQL Server 2005及更高版本以来，Microsoft已弃用旧的外部联接语法，而不弃用旧的内部联接语法.使用旧样式的外部联接语法与运行在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 and INNER JOIN is the wave of the future.

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