MySQL入门篇-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 t2on 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 t2on 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 t2on 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 t2on 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 t2on 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)