在SQL中,在多个JOINS结束后,加入vs后的ON条件有什么区别

这里是一个例子http://sqlfiddle.com/#!3/e0a0f/3

CREATE TABLE TableA (Email VARCHAR(100),SomeNameA VARCHAR(100))
CREATE TABLE Tableb (Email VARCHAR(100),SomeNameB VARCHAR(100))
CREATE TABLE Tablec (Email VARCHAR(100),SomeNameC VARCHAR(100))

INSERT INTO TableA SELECT 'joe@test.com','JoeA'
INSERT INTO TableA SELECT 'jan@test.com','JaneA'
INSERT INTO TableA SELECT 'dave@test.com','DaveA'
INSERT INTO TableB SELECT 'joe@test.com','JoeB'
INSERT INTO TableB SELECT 'dave@test.com','DaveB'
INSERT INTO TableC SELECT 'joe@test.com','JoeC'
INSERT INTO TableC SELECT 'dave@test.com','DaveC'


SELECT TOP 2 a.*,b.*,c.*
FROM   TableA a
       LEFT OUTER JOIN TableB b
                    ON a.email = b.email
       INNER JOIN TableC c
                    ON c.Email = b.email;

SELECT TOP 2 a.*,c.*
FROM   TableA a
       LEFT OUTER JOIN TableB b
       INNER JOIN TableC c
                    ON c.Email = b.email
                    ON a.email = b.email;

我不明白为什么这两个SELECT语句产生不同的结果.

解决方法

所以当你写

FROM TableA a 
LEFT OUTER JOIN TableB b ON a.email = b.email
INNER JOIN TableC c ON c.Email = b.email ;

那么订单如下：

> TableA与TableB相连接,生成临时关系V1
> V1内部连接到TableC.

当你写的时候,

FROM TableA a 
LEFT OUTER JOIN TableB b 
INNER JOIN TableC c ON c.Email = b.email ON a.email = b.email;

那么订单如下：

> TableB内部连接到TableC生成临时关系V1.
> TableA被连接到V1.

因此,结果是不同的.通常建议在这种情况下使用括号来提高查询的可读性：

FROM TableA a 
LEFT OUTER JOIN
  (TableB b INNER JOIN TableC c ON c.Email = b.email)
ON a.email = b.email;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!