大多数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
显然,当您需要外部连接时,需要使用第二种语法。但是,在进行内部连接时,为什么我应该更喜欢第二种语法而不是第一种语法(反之亦然)?
大多数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
显然,当您需要外部连接时,需要使用第二种语法。但是,在进行内部连接时,为什么我应该更喜欢第二种语法而不是第一种语法(反之亦然)?
对于数据库来说,它们最终是相同的。但是,对于您来说,在某些情况下必须使用第二种语法。为了编辑最终不得不使用它的查询(发现您需要一个左连接,而您有一个直连接),为了一致性,我只在第二个方法上设置模式。这将使阅读查询更容易。
JOIN语法将条件保存在它们应用的表附近。这在连接大量表时尤其有用。
顺便说一下,你也可以用第一个语法做一个外部连接:
WHERE a.x = b.x(+)
Or
WHERE a.x *= b.x
Or
WHERE a.x = b.x or a.x not in (select x from b)
当你需要一个外部连接时,第二个语法并不总是必需的:
Oracle:
SELECT a.foo, b.foo
FROM a, b
WHERE a.x = b.x(+)
MSSQLServer(尽管在2000版本中已弃用)/Sybase:
SELECT a.foo, b.foo
FROM a, b
WHERE a.x *= b.x
回到你的问题。我不知道答案,但它可能与这样一个事实有关:当您正在做连接时,连接比在where子句中添加表达式更自然(至少在语法上)。
第二种是首选的,因为它不太可能因为忘记放置where子句而导致意外交叉连接。没有on子句的连接将无法进行语法检查,没有where子句的旧式连接不会失败,它将进行交叉连接。
此外,当您以后必须使用左连接时,将它们都置于相同的结构中对维护是有帮助的。旧的语法自1992年以来就已经过时了,现在早就该停止使用它了。
另外,我发现许多只使用第一种语法的人并不真正理解连接,而理解连接对于在查询时获得正确的结果至关重要。
基本上,当你的FROM子句像这样列出表时:
SELECT * FROM
tableA, tableB, tableC
结果是表a, B, c中的所有行的叉乘。然后你应用限制WHERE表a。id = tableB。A_id将丢弃大量的行,然后进一步…和表b。id = tableC。B_id,然后你应该只得到那些你真正感兴趣的行。
dbms知道如何优化此SQL,以便使用join编写此SQL的性能差异可以忽略不计(如果有的话)。使用JOIN表示法使SQL语句更具可读性(恕我直言,不使用JOIN会使语句变得一团糟)。使用叉乘,您需要在WHERE子句中提供连接标准,这就是符号的问题所在。你在WHERE从句里塞满了
tableA.id = tableB.a_id
AND tableB.id = tableC.b_id
它只用于限制叉乘。WHERE子句应该只包含结果集的RESTRICTIONS。如果将表连接条件与结果集限制混合使用,您(和其他人)将发现查询更难阅读。您一定要使用join,并保留FROM子句和WHERE子句。
SELECT * FROM table1, table2,…对于几个表来说,语法是可以的,但是随着表数量的增加,它会变得越来越难读(不一定是一个精确的数学语句)。
JOIN语法较难编写(在开始时),但它明确了哪些标准影响哪些表。这让你更不容易犯错。
同样,如果所有的连接都是INNER,那么两个版本是等价的。然而,一旦在语句的任何地方有了OUTER连接,事情就变得复杂得多,实际上可以保证您所编写的内容不会查询您认为您所编写的内容。
在大多数现代数据库中,只列出表并使用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子句决定了哪些行在最终结果中结束,而不是行的各个部分。
在这种情况下,由于左连接,系。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 SQL Server 2005或2008上的数据库对话的唯一方法是将该数据库设置为8.0兼容模式(即SQL Server 2000)。
此外,旧的方法,通过向查询优化器抛出一堆表,加上一堆WHERE子句,类似于说“你在这里,尽你所能”。使用新的语法,查询优化器只需做更少的工作,就可以确定哪些部分可以组合在一起。
结果出来了。
LEFT和INNER JOIN是未来的潮流。
第一种方法是较老的标准。第二种方法是在SQL-92中引入的http://en.wikipedia.org/wiki/SQL。完整的标准可以在http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt上查看。
数据库公司花了很多年才采用SQL-92标准。
所以第二种方法是首选的原因,它是根据ANSI和ISO标准委员会的SQL标准。
我认为在本文中有一些很好的理由采用第二种方法——使用显式join。关键在于,当JOIN条件从WHERE子句中删除后,在WHERE子句中查看其余的选择条件就容易得多了。
在非常复杂的SELECT语句中,读者更容易理解正在发生的事情。
如果两者都是内部连接,则在SQL的语义或执行或性能上没有区别。这纯粹是一个偏好的问题,编码标准在你的工作小组。
在过去的25年里,我养成了一个习惯,如果我有一个相当复杂的SQL,我将使用INNER JOIN语法,因为它更容易让读者一眼看出查询的结构。它还通过从剩余条件中分离出连接条件来提供更多的清晰度,这可以节省时间(和错误),如果您在几个月后返回查询的话。
但是对于外部连接,为了清晰起见,我在任何情况下都不会使用非ansi扩展。