自我联接查询 [英] self join query

问题描述

请考虑下表:

mysql> select * from phone_numbers;
+-------------+------+-----------+
| number      | type | person_id |
+-------------+------+-----------+
| 17182225465 | home |         1 |
| 19172225465 | cell |         1 |
| 12129876543 | home |         2 |
| 13049876543 | cell |         2 |
| 15064223454 | home |         3 |
| 15064223454 | cell |         3 |
| 18724356798 | home |         4 |
| 19174335465 | cell |         5 |
+-------------+------+-----------+

我正在寻找那些拥有家用电话但没有手机的人.

I'm trying to find those people who have home phones but not cells.

此查询有效:

mysql> select h.*
    -> from phone_numbers h
    -> left join phone_numbers c
    -> on h.person_id = c.person_id
    -> and c.type = 'cell'
    -> where h.type = 'home'
    -> and c.number is null;
+-------------+------+-----------+
| number      | type | person_id |
+-------------+------+-----------+
| 18724356798 | home |         4 |
+-------------+------+-----------+

但是这个没有:

mysql> select h.*
    -> from phone_numbers h
    -> left join phone_numbers c
    -> on h.person_id = c.person_id
    -> and h.type = 'home'
    -> and c.type = 'cell'
    -> where c.number is null;
+-------------+------+-----------+
| number      | type | person_id |
+-------------+------+-----------+
| 19172225465 | cell |         1 |
| 13049876543 | cell |         2 |
| 15064223454 | cell |         3 |
| 18724356798 | home |         4 |
| 19174335465 | cell |         5 |
+-------------+------+-----------+

两者之间的唯一区别是h.type = 'home'条件的位置-第一个是where子句，第二个是on子句的一部分.

The only difference between the two is the location of the h.type = 'home' condition - in the first it's in the where clause and in the second it's part of the on clause.

第二个查询为什么不返回与第一个查询相同的结果?

Why doesn't the second query return the same result as the first?

推荐答案

在第二个SQL中，条件h.type ='home'是外部联接条件的一部分，而不是结果的筛选器.对于h.type ='cell'的所有记录，条件h.type ='home'为FALSE，因此找不到匹配"的c行-因此c.number为null，这是您唯一的过滤(WHERE)条件

In the second SQL, the condition h.type = 'home' is part of the outer join conditions, and is not a filter on the results. For all records where h.type='cell', the condition h.type = 'home' is FALSE and so no "matching" c row is found - so c.number is null, which is your only filtering (WHERE) condition.

在伪代码中，第二个SQL的工作方式如下:

In pseudo-code your 2nd SQL works like this:

for each row in phone_numbers h /* Note this is ALL home AND cell phones */
   select c.number from phone_numbers c
   where h.person_id = c.person_id
   and h.type = 'home'
   and c.type = 'cell';
   if c.number is null (i.e. no row found)
     display h.*
   end if
end loop;

这篇关于自我联接查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！