MySQL表连接小结

备注:测试数据库版本为MySQL 8.0

这个blog我们来聊聊常见的表连接的方法

测试数据:

create table t1(id int);
create table t2(id int);

insert into t1 values(1);
insert into t1 values(2);

insert into t2 values(2);
insert into t2 values(3);
commit;

一.内连接 --求交集

select t1.id,t2.id id2
from t1
inner join t2
     on t1.id = t2.id;
--或者
select t1.id,t2.id id2
from t1,t2
where t1.id = t2.id;

mysql> select t1.id,t2.id id2
    -> from t1
    -> inner join t2
    ->      on t1.id = t2.id;
+------+------+
| id   | id2  |
+------+------+
|    2 |    2 |
+------+------+
1 row in set (0.01 sec)

mysql> select t1.id,t2.id id2
    -> from t1,t2
    -> where t1.id = t2.id;
+------+------+
| id   | id2  |
+------+------+
|    2 |    2 |
+------+------+
1 row in set (0.00 sec)

二.左连接 --求A的全部

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

mysql> select t1.id,t2.id id2
    -> from t1
    -> left join t2
    ->   on t1.id = t2.id;
+------+------+
| id   | id2  |
+------+------+
|    2 |    2 |
|    1 | NULL |
+------+------+
2 rows in set (0.00 sec)

三.左连接 --实现A-B的差集

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

mysql> select t1.id,t2.id id2
    -> from t1
    -> left join t2
    ->   on t1.id = t2.id
    -> where t2.id is null;
+------+------+
| id   | id2  |
+------+------+
|    1 | NULL |
+------+------+
1 row in set (0.00 sec)

四.全连接 – A union B 求合集

oracle里面有full join,但是在mysql中没有full join。我们可以使用union来达到目的。

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

mysql> select t1.id id1,t2.id id2
    -> from t1
    -> left join t2
    -> on t1.id = t2.id
    -> union
    -> select t1.id id1,t2.id id2
    -> from t1
    -> right join t2
    -> on t1.id = t2.id;
+------+------+
| id1  | id2  |
+------+------+
|    2 |    2 |
|    1 | NULL |
| NULL |    3 |
+------+------+
3 rows in set (0.00 sec)

五.全连接实现-去交集

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

mysql> select t1.id id1,t2.id id2
    -> from t1
    -> left join t2
    -> on t1.id = t2.id
    -> where t2.id is null
    -> union
    -> select t1.id id1,t2.id id2
    -> from t1
    -> right join t2
    -> on t1.id = t2.id
    -> where t1.id is null;
+------+------+
| id1  | id2  |
+------+------+
|    1 | NULL |
| NULL |    3 |
+------+------+
2 rows in set (0.00 sec)

六.右连接实现-B-A 求差集

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

mysql> select t1.id,t2.id id2
    -> from t1
    -> right join t2
    ->   on t1.id = t2.id
    -> where t1.id is null;
+------+------+
| id   | id2  |
+------+------+
| NULL |    3 |
+------+------+
1 row in set (0.00 sec)

七.右连接 --求B的全部

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

mysql> select t1.id,t2.id id2
    -> from t1
    -> right join t2
    ->  on t1.id = t2.id;
+------+------+
| id   | id2  |
+------+------+
|    2 |    2 |
| NULL |    3 |
+------+------+
2 rows in set (0.00 sec)

八.表的笛卡尔积

如果表连接没有带条件，则会产生笛卡尔积
假设A表和B表都是10条记录，且一一对应，这个时候A、B两个表无关联条件下的查询，会产生10*10 100条数据。
笛卡尔积在开发中，大多数时候都是不可取的，严重的影响性能，我就优化过不写表关联条件再来distinct去重的开发写的sql。

今天我们来讲讲一个笛卡尔积的例子。

测试数据:

create table test1(c  varchar(200));
insert into test1 values('中国');
insert into test1 values('美国');
insert into test1 values('日本');
insert into test1 values('韩国');
commit;

现在有4个国家要进行比赛，每个国家都要和除了自己之外的另外3个国家进行比赛，这个sql如何写?

--通过表连接构造一个笛卡尔积
select t1.c c1,t2.c c2
from test1 t1,test1 t2
where 1 = 1
order by t1.c,t2.c;
--去掉自己和自己的
select t1.c c1,t2.c c2
from test1 t1,test1 t2
where t1.c != t2.c
order by t1.c,t2.c;

mysql> select t1.c c1,t2.c c2
    -> from test1 t1,test1 t2
    -> where 1 = 1
    -> order by t1.c,t2.c;
+--------+--------+
| c1     | c2     |
+--------+--------+
| 中国   | 中国   |
| 中国   | 日本   |
| 中国   | 美国   |
| 中国   | 韩国   |
| 日本   | 中国   |
| 日本   | 日本   |
| 日本   | 美国   |
| 日本   | 韩国   |
| 美国   | 中国   |
| 美国   | 日本   |
| 美国   | 美国   |
| 美国   | 韩国   |
| 韩国   | 中国   |
| 韩国   | 日本   |
| 韩国   | 美国   |
| 韩国   | 韩国   |
+--------+--------+
16 rows in set (0.00 sec)

mysql> select t1.c c1,t2.c c2
    -> from test1 t1,test1 t2
    -> where t1.c != t2.c
    -> order by t1.c,t2.c;
+--------+--------+
| c1     | c2     |
+--------+--------+
| 中国   | 日本   |
| 中国   | 美国   |
| 中国   | 韩国   |
| 日本   | 中国   |
| 日本   | 美国   |
| 日本   | 韩国   |
| 美国   | 中国   |
| 美国   | 日本   |
| 美国   | 韩国   |
| 韩国   | 中国   |
| 韩国   | 日本   |
| 韩国   | 美国   |
+--------+--------+
12 rows in set (0.00 sec)