MySQL 关联表查询问题？

Question

两个表：A 表, B 表

A

id	outer_id	...
1	1	...
2	1	...
3	2	...
4	4	...
5	6	...
6	10	...

B

id	type	...
1	99	...
2	0	...
3	99	...
4	1	...
5	3	...
6	99	...

想要查询出A表的两种数据，一种是用A表的outer_id关联B表的id，如果存在，type不能是 99 这个数据才需要，如果不存在，这个A表数据也要。

另外一个数据就是B表必须存在且 type 等于 99。

这个两个SQL好像是：

SELECT a.* FROM A AS a WHERE ((SELECT b.type FROM B AS b WHERE b.id = a.outer_id LIMIT 1) is null) OR (SELECT b.type FROM B AS b WHERE b.id = a.outer_id LIMIT 1) != 99

SELECT a.* FROM A AS a WHERE (SELECT b.type FROM B AS b WHERE b.id = a.outer_id LIMIT 1) = 99

这样对吗

Answer 1

你的第一种数据有点不好理解，我是这样写的，

SELECT a.*
FROM A a
LEFT JOIN B b ON b.id = a.outer_id
WHERE b.id IS NULL
   OR (b.id IS NOT NULL AND b.type != 99)

第二种数据

SELECT a.*
FROM A a
INNER JOIN B b ON b.id = a.outer_id AND b.type = 99

Answer 2

这个需求用left join实现更好

第一个数据：

select a.id, a.outer_id, b.id, b.type
from A a
left join (
    select id, type
    from B
    where type != 99
) b
on a.outer_id = b.id

第二个数据：

select a.id, a.outer_id, b.id, b.type
from A a
left join B b
on a.outer_id = b.id
where b.id is not null 
and b.type = 99