文章目录
SQL中的Join用法
#1 环境
MySQL5.7
Mac
#2 创建表及插入数据
新建两个表tableA及tableB
mysql> create table tableA ( id int(10), name varchar(100) );
mysql> create table tableB ( id int(10), name varchar(100) );
插入数据
tableA:
mysql> insert into tableA values(1,'布鲁日');
mysql> insert into tableA values(2,'巴黎');
mysql> insert into tableA values(3,'伦敦');
mysql> insert into tableA values(4,'柏林');
mysql> insert into tableA values(5,'耶路撒冷');
tableB:
mysql> insert into tableB values(1,'多哈');
mysql> insert into tableB values(2,'耶路撒冷');
mysql> insert into tableB values(3,'新德里');
mysql> insert into tableB values(4,'马尼拉');
mysql> insert into tableB values(5,'吉隆坡');
#3 开始
#3.1 inner join
产生的结果是A和B的交集, inner join也可以简写成join
select * from tableA join tableB ON tableA.name=tableB.name;
输出结果:
+------+--------------+------+--------------+
| id | name | id | name |
+------+--------------+------+--------------+
| 5 | 耶路撒冷 | 2 | 耶路撒冷 |
+------+--------------+------+--------------+
#3.2 left join
产生表A的完全集，而B表中匹配的则有值(没有匹配的则以null值取代)。left join返回左表的全部行和右表满足ON条件的行，如果左表的行在右表中没有匹配，那么这一行右表中对应数据用NULL代替。
select * from tableA left join tableB ON tableA.name=tableB.name;
输出结果:
+------+--------------+------+--------------+
| id | name | id | name |
+------+--------------+------+--------------+
| 5 | 耶路撒冷 | 2 | 耶路撒冷 |
| 1 | 布鲁日 | NULL | NULL |
| 2 | 巴黎 | NULL | NULL |
| 3 | 伦敦 | NULL | NULL |
| 4 | 柏林 | NULL | NULL |
+------+--------------+------+--------------+
左表独有:
select * from tableA left join tableB ON tableA.name=tableB.name where tableB.name is null;
输出结果:
+------+-----------+------+------+
| id | name | id | name |
+------+-----------+------+------+
| 1 | 布鲁日 | NULL | NULL |
| 2 | 巴黎 | NULL | NULL |
| 3 | 伦敦 | NULL | NULL |
| 4 | 柏林 | NULL | NULL |
+------+-----------+------+------+
#3.3 right join
与left join相反,产生表B的完全集，而A表中匹配的则有值(没有匹配的则以null值取代)。left join返回左表的全部行和右表满足ON条件的行，如果左表的行在右表中没有匹配，那么这一行右表中对应数据用NULL代替。
select * from tableA right join tableB ON tableA.name=tableB.name;
输出结果:
+------+--------------+------+--------------+
| id | name | id | name |
+------+--------------+------+--------------+
| 5 | 耶路撒冷 | 2 | 耶路撒冷 |
| NULL | NULL | 1 | 多哈 |
| NULL | NULL | 3 | 新德里 |
| NULL | NULL | 4 | 马尼拉 |
| NULL | NULL | 5 | 吉隆坡 |
+------+--------------+------+--------------+
右表独有:
select * from tableA right join tableB ON tableA.name=tableB.name where tableA.name is null;
输出结果:
+------+------+------+-----------+
| id | name | id | name |
+------+------+------+-----------+
| NULL | NULL | 1 | 多哈 |
| NULL | NULL | 3 | 新德里 |
| NULL | NULL | 4 | 马尼拉 |
| NULL | NULL | 5 | 吉隆坡 |
+------+------+------+-----------+
#3.4 full join
A和B的并集,oracle里面有full join,但是在mysql中没有full join 。我们可以使用union来达到目的。
select * from tableA left join tableB ON tableA.name=tableB.name
union
select * from tableA right join tableB ON tableA.name=tableB.name;
输出结果:
+------+--------------+------+--------------+
| id | name | id | name |
+------+--------------+------+--------------+
| 5 | 耶路撒冷 | 2 | 耶路撒冷 |
| 1 | 布鲁日 | NULL | NULL |
| 2 | 巴黎 | NULL | NULL |
| 3 | 伦敦 | NULL | NULL |
| 4 | 柏林 | NULL | NULL |
| NULL | NULL | 1 | 多哈 |
| NULL | NULL | 3 | 新德里 |
| NULL | NULL | 4 | 马尼拉 |
| NULL | NULL | 5 | 吉隆坡 |
+------+--------------+------+--------------+