这是Grouping JOIN Clauses In SQL中提到的分组JOIN case .如果使用得当,它可以使查询更清晰,更容易阅读.
这与下面的子句相同,不带可选的圆括号:
INNER JOIN Table2 t2
( INNER JOIN Table3 t3
ON t2.ID = t3.ID AND t2.Seq = t3.Seq
)
ON t1.ID = t2.ID
在这种情况下,它没有用处,只会增加整个查询的可读性.不过,在本文的示例中,它实际上构成了查询cleaner.
假设我们想要列出所有公司以及任何有电话号码的联系人.
DECLARE @company TABLE (
id INT,name VARCHAR( 30 )
);
DECLARE @contact TABLE (
id INT,
name VARCHAR( 30 ),
company_id INT
);
DECLARE @phone TABLE (
name VARCHAR( 30 ),
contact_id INT
);
INSERT INTO @company(id, name)
values
(1, 'Nylon Technology'),
(2, 'Edit.com'),
(3, 'HotKoko')
;
INSERT INTO @contact(id,name,company_id)
VALUES
(1, 'Maria Bello', 1 ),
(2, 'Christina Cox', 1),
(3, 'Julia Stiles', 2 ),
(4, 'Julie Ensike', 3 )
;
INSERT INTO @phone( name, contact_id)
VALUES
('123-456-1890', 1),
('123-456-5555', 4)
;
我们不能使用所有的LEFT JOIN
,这将返回没有数字的联系人.我们不能使用左联接后接内联接,这只会返回具有电话联系人的公司:
SELECT
c.id,
c.name,
( ct.name ) AS contact_name,
( p.name ) AS contact_phone
FROM
@company c
LEFT OUTER JOIN
@contact ct
ON
c.id = ct.company_id
INNER JOIN
@phone p
ON
ct.id = p.contact_id;
---
id name contact_name contact_phone
1 Nylon Technology Maria Bello 123-456-1890
3 HotKoko Julie Ensike 123-456-5555
通过使用分组联接,查询可以正常工作,并且易于阅读:
FROM
@company c
LEFT OUTER JOIN
(
@contact ct
INNER JOIN
@phone p
ON
ct.id = p.contact_id
)
ON
c.id = ct.company_id
---
id name contact_name contact_phone
1 Nylon Technology Maria Bello 123-456-1890
2 Edit.com NULL NULL
3 HotKoko Julie Ensike 123-456-5555
另一种 Select 是使用子查询,或者反转表顺序并使用Right Join
FROM
@phone p
INNER JOIN
@contact ct
ON
p.contact_id = ct.id
RIGHT OUTER JOIN
@company c
ON
ct.company_id = c.id
这一次,"主"表最后出现,使查询更难阅读.
我把子查询示例留到最后,因为它真的很难读.SELECT子句必须重复:
SELECT
c.id,
c.name,
t.contact_name,
t.contact_phone
FROM
@company c
LEFT OUTER JOIN
(
SELECT
ct.company_id,
( ct.name ) AS contact_name,
( p.name ) AS contact_phone
FROM
@contact ct
INNER JOIN
@phone p
ON
ct.id = p.contact_id
) AS t
ON
c.id = t.company_id
使用CTE可以对此示例进行一些简化,但它仍然不如分组连接那样简洁:
WITH contact_phones as (
SELECT
ct.company_id,
( ct.name ) AS contact_name,
( p.name ) AS contact_phone
FROM
@contact ct
INNER JOIN
@phone p
ON
ct.id = p.contact_id
)
SELECT
c.id,
c.name,
t.contact_name,
t.contact_phone
FROM
@company c
LEFT OUTER JOIN
contact_phones AS t
ON
c.id = t.company_id