mysql 自关联的子孙查询_sql – 自连接查询

请考虑下表：

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 |

+-------------+------+-----------+

我试图找到那些有家用电话而不是电池的人.

此查询有效：

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子句的一部分.

为什么第二个查询没有返回与第一个查询相同的结果？

解决方法:

在第二个SQL中,条件h.type =’home’是外连接条件的一部分,并且不是结果的过滤器.对于h.type =’cell’的所有记录,条件h.type =’home’为FALSE,因此找不到“匹配”c行 – 所以c.number为null,这是您唯一的过滤(WHERE)条件.

在伪代码中,您的第二个SQL的工作方式如下：

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;

标签：mysql,join,sql,self-join

来源： https://codeday.me/bug/20190527/1161084.html