MySQL表连接

MySQL表连接

1、内连接 – 求交集

select t1.id id1,
t2.id id2
from A t1
inner join B t2 on t2.id=t1.id;
或
select t1.id id1,
t2.id id2
from A t1,B t2
where t1.id=t2.id;

2、左连接 – 求A的全集

select t1.id id1,
t2.id id2
from A t1
left join B t2 on t2.id=t1.id;

3、左连接 – 实现A-B的差集

select t1.id id1,
t2.id id2
from A t1
left join B t2 on t2.id=t1.id
where t2.id is null;

4、全连接 – A union B 求合集

select t1.id id1,
t2.id id2
from A t1
left join B t2 on t2.id=t1.id
union
select t1.id id1,
t2.id id2
from A t1
right join B t2 on t2.id=t1.id;

5、全连接 – 去交集

select t1.id id1,
t2.id id2
from A t1
left join B t2 on t2.id=t1.id
where t2.id is null
union
select t1.id id1,
t2.id id2
from A t1
right join B t2 on t2.id=t1.id
where t1.id is null;

6、右连接 – B-A 求差集

select t1.id id1,
t2.id id2
from A t1
right join B t2 on t2.id=t1.id
where t1.id is null;

7、右连接 – 求B的全部

select t1.id id1,
t2.id id2
from A t1
right join B t2 on t2.id=t1.id;

8、表的笛卡尔积

如果表连接没有带条件，则会产生笛卡尔积
假设A表和B表都是10条记录，且一一对应，这个时候A、B两个表无关联条件下的查询，会产生10*10 100条数据。

select t1.id id1,
t2.id id2
from A t1,B t2