大多数 SQL 方言接受以下两种查询:
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
现在很明显,当您需要外连接时,需要第二种语法。但是在进行内部连接时,为什么我应该更喜欢第二种语法而不是第一种(反之亦然)?
在大多数现代数据库中,不推荐使用仅列出表并使用 WHERE
子句指定连接条件的旧语法。
这不仅仅是为了展示,当您在同一查询中同时使用 INNER 和 OUTER 联接时,旧语法可能会产生歧义。
让我给你举个例子。
假设您的系统中有 3 个表:
Company
Department
Employee
每个表包含许多行,链接在一起。你有多个公司,每个公司可以有多个部门,每个部门可以有多个员工。
好的,所以现在您要执行以下操作:
列出所有公司,包括他们的所有部门和所有员工。请注意,有些公司还没有任何部门,但请确保您也将它们包括在内。确保只检索有员工的部门,但始终列出所有公司。
所以你这样做:
SELECT * -- for simplicity
FROM Company, Department, Employee
WHERE Company.ID *= Department.CompanyID
AND Department.ID = Employee.DepartmentID
请注意,最后一个是内部连接,以满足您只希望部门有人员的标准。
好的,那么现在会发生什么。好吧,问题是,它取决于数据库引擎、查询优化器、索引和表统计信息。让我解释。
如果查询优化器确定这样做的方法是先获取公司,然后查找部门,然后与员工进行内部联接,那么您将不会得到任何没有部门的公司。
这样做的原因是 WHERE
子句确定最终结果中的 行,而不是行的各个部分。
在这种情况下,由于左连接,Department.ID 列将为 NULL,因此当涉及到 Employee 的 INNER JOIN 时,没有办法满足 Employee 行的约束,所以它不会出现。
另一方面,如果查询优化器决定先处理部门-员工联接,然后与公司进行左联接,您将看到它们。
所以旧的语法是模棱两可的。如果不处理查询提示,就无法指定您想要的内容,有些数据库根本没有办法。
输入新语法,您可以使用它进行选择。
例如,如果您想要所有公司,如问题描述所述,您将这样写:
SELECT *
FROM Company
LEFT JOIN (
Department INNER JOIN Employee ON Department.ID = Employee.DepartmentID
) ON Company.ID = Department.CompanyID
在这里,您指定您希望将部门-员工联接作为一个联接完成,然后将结果与公司联接。
此外,假设您只想要名称中包含字母 X 的部门。同样,如果使用旧式联接,您也可能会失去公司,如果它没有任何名称中带有 X 的部门,但使用新语法,您可以这样做:
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 的部门。旧语法很难做到这一点。
这就是为什么在其他供应商中,自 SQL Server 2005 及更高版本以来,Microsoft 已弃用旧的外连接语法,而不是旧的内连接语法。使用旧式外连接语法与运行在 Microsoft SQL Server 2005 或 2008 上的数据库通信的唯一方法是将该数据库设置为 8.0 兼容模式(又名 SQL Server 2000)。
此外,通过向查询优化器扔一堆表和一堆 WHERE 子句的旧方法类似于说“你在这里,尽你所能”。使用新的语法,查询优化器只需要做更少的工作来确定哪些部分组合在一起。
所以你有它。
LEFT 和 INNER JOIN 是未来的潮流。
JOIN 语法使条件靠近它们所应用的表。这在您连接大量表时特别有用。
顺便说一句,您也可以使用第一种语法进行外连接:
WHERE a.x = b.x(+)
或者
WHERE a.x *= b.x
或者
WHERE a.x = b.x or a.x not in (select x from b)
基本上,当您的 FROM 子句列出如下表时:
SELECT * FROM
tableA, tableB, tableC
结果是表 A、B、C 中所有行的叉积。然后你应用限制 WHERE tableA.id = tableB.a_id
这将丢弃大量行,然后进一步...... AND tableB.id = tableC.b_id
然后你应该只得到你真正感兴趣的那些行。
DBMS 知道如何优化此 SQL,以便使用 JOIN 编写此 SQL 的性能差异可以忽略不计(如果有的话)。使用 JOIN 表示法使 SQL 语句更具可读性(恕我直言,不使用连接会使语句变得一团糟)。使用叉积,您需要在 WHERE 子句中提供连接条件,这就是符号的问题。你在 WHERE 子句中塞满了类似的东西
tableA.id = tableB.a_id
AND tableB.id = tableC.b_id
仅用于限制叉积。 WHERE 子句应该只包含对结果集的 RESTRICTIONS。如果您将表连接条件与结果集限制混合在一起,您(和其他人)会发现您的查询更难阅读。您绝对应该使用 JOIN 并将 FROM 子句保留为 FROM 子句,将 WHERE 子句保留为 WHERE 子句。
第一种方法是旧标准。第二种方法是在 SQL-92 http://en.wikipedia.org/wiki/SQL 中引入的。完整的标准可以在 http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt 查看。
数据库公司采用 SQL-92 标准花了很多年。
所以首选第二种方法的原因是,它是根据 ANSI 和 ISO 标准委员会的 SQL 标准。
,
仍然是标准的。一旦还引入了子选择,只需为 outer join
引入 on
。
第二种是首选,因为它不太可能因忘记放入 where 子句而导致意外交叉连接。没有 on 子句的连接将无法通过语法检查,没有 where 子句的旧式连接不会失败,它将执行交叉连接。
此外,当您以后必须进行左连接时,它们都位于同一结构中有助于维护。旧的语法自 1992 年以来就已经过时了,现在应该停止使用它了。
另外,我发现许多专门使用第一种语法的人并不真正理解联接,而了解联接对于在查询时获得正确结果至关重要。
我认为此页面上有一些充分的理由采用第二种方法 - 使用显式 JOIN。关键是,当从 WHERE 子句中删除 JOIN 标准时,在 WHERE 子句中查看剩余的选择标准变得容易得多。
在非常复杂的 SELECT 语句中,读者更容易理解发生了什么。
SELECT * FROM table1, table2, ...
语法适用于几个表,但随着表数量的增加,它变得越来越难以阅读(不一定是数学上准确的语句)。
JOIN 语法更难编写(在开始时),但它明确了哪些条件会影响哪些表。这使得犯错变得更加困难。
此外,如果所有联接都是 INNER,则两个版本是等效的。但是,当您在语句中的任何位置使用 OUTER 连接时,事情就会变得更加复杂,并且几乎可以保证您编写的内容不会查询您认为自己编写的内容。
当您需要外连接时,并不总是需要第二种语法:
甲骨文:
SELECT a.foo, b.foo
FROM a, b
WHERE a.x = b.x(+)
MSSQLServer(虽然在 2000 版本中是 deprecated)/Sybase:
SELECT a.foo, b.foo
FROM a, b
WHERE a.x *= b.x
但回到你的问题。我不知道答案,但它可能与连接更自然(至少在语法上)比在 where 子句中添加表达式更自然(至少在语法上)这样做有关:连接。
我听到很多人抱怨第一个太难理解而且不清楚。我看不出它有什么问题,但是在进行了讨论之后,为了清楚起见,我什至在 INNER JOINS 上使用了第二个。
对于数据库,它们最终是相同的。但是,对您而言,在某些情况下您将不得不使用第二种语法。为了编辑最终不得不使用它的查询(发现你需要一个左连接,而你有一个直接连接),并且为了保持一致性,我只在第二种方法上进行模式化。这将使阅读查询更容易。
那么第一个和第二个查询可能会产生不同的结果,因为 LEFT JOIN 包括第一个表中的所有记录,即使右表中没有相应的记录。
OUTER JOIN
语法*=
/=*
/*=*
。