1.背景:连接查询无法满足需求
我们都知道连接查询有内连接和外连接,那几种集合图看过不少,
-
INNER JOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录。
-
LEFT JOIN(左连接):获取左表所有记录,即使右表没有对应匹配的记录。
-
RIGHT JOIN(右连接): 与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。
但是最近遇到一种场景使用连接查询怎样都不能满足需求。具体如下
A表:
id | content |
---|---|
1 | aaa |
2 | bbb |
3 | ccc |
B表:foreign_id连接A表的id
id | name | foreign_id |
---|---|---|
1 | xiaoming1 | 2 |
2 | xiaoming2 | 3 |
3 | xiaoming3 | 1 |
C表:foreign_id连接A表的id
id | name | foreign_id |
---|---|---|
1 | xiaoming1 | 3 |
2 | xiaoming2 | 2 |
3 | xiaoming3 | 2 |
现在需要查询xiaoming1对应的A表的记录,
需要的查询结果:
id | content |
---|---|
2 | bbb |
3 | ccc |
使用连接能想到的写法,B.*和C.*是为了便于分析
select
A.*,B*,C*
from A
left join B on B.foreign_id = A.id
left join C on C.foreign_id = A.id
where B.name = 'xiaoming1' or C.name = 'xiaoming1';
查询结果:
id | content | id | name | foreign_id | id | name | foreign_id |
---|---|---|---|---|---|---|---|
2 | bbb | 1 | xiaoming1 | 2 | 2 | xiaoming2 | 2 |
2 | bbb | 1 | xiaoming1 | 2 | 3 | xiaoming3 | 2 |
3 | ccc | 2 | xiaoming2 | 3 | 1 | xiaoming1 | 3 |
这样得到的结果A表结果有一条重复的(如红色),不论是内连接还是右外连接,均无法满足需求
2.联合查询 union
联合查询比较容易理解,即将多个查询语句的结果集合在一起显示
语法:select column_name(s) from table_name1 UNION select column_name(s) from table_name2
上面的需求SQL语句可以这样写
select
A.*
from A
left join B on B.foreign_id = A.id
where B.name = 'xiaoming1'
UNION
select
A.*
from A
left join C on C.foreign_id = A.id
where C.name = 'xiaoming1'
注意:1.两个查询结果字段数量必须相同 2.重复的数据的会合并为一条(如果不想合并可以用union all)
使用上,如果需要对合并后的结果集 order by 、limit 、count,需要这样写,将合并后结果集当作子查询
select userid from
(select userid from testa
union
select userid from testb) t
order by userid limit 0,1;