left join, right join, full join, join 测试-优快云博客

本文链接：https://blog.youkuaiyun.com/iteye_17811/article/details/81953801

一、准备测试数据：

创建数据
Create database test;

应用数据库
use test;

创建表a

create table a(
ID int not null,
Name varchar(32)
);

创建表b

create table b(
DD int not null,
DM varchar(32)
);

insert a(ID, Name) values(1,'paul');

insert a(ID, Name) values(2,'jude');

insert a(ID, Name) values(3,'Tom');

mysql> select * from a;
+----+------+
| ID | Name |
+----+------+
|  1 | paul  |
|  2 | jude  |
|  3 | Tom   |
+----+------+
3 rows in set (0.00 sec)

insert b(DD,DM) values(1,'Retail');

insert b(DD,DM) values(2,'IVS');

insert b(DD,DM) values(3,'IT');

insert b(DD,DM) values(4,'HRD');

mysql> select * from b;
+----+--------+
| DD | DM     |
+----+--------+
|  1 | Retail |
|  2 | IVS    |
|  4 | IT     |
|  5 | HRD    |
+----+--------+
4 rows in set (0.00 sec)

二、测试数据

inner join

mysql> select a.id,a.name,b.dd,b.dm from a inner join b on a.id=b.dd;
+----+------+----+--------+
| id | name | dd | dm     |
+----+------+----+--------+
|  1 | paul |  1 | Retail |
|  2 | jude |  2 | IVS    |
+----+------+----+--------+
2 rows in set (0.00 sec)

mysql> select a.id,a.name,b.dd,b.dm from a,b where a.id=b.dd;
+----+------+----+--------+
| id | name | dd | dm     |
+----+------+----+--------+
|  1 | paul |  1 | Retail |
|  2 | jude |  2 | IVS    |
+----+------+----+--------+
2 rows in set (0.00 sec)

mysql> select a.id,a.name,b.dd,b.dm from a inner join b on a.id>b.dd;
+----+------+----+--------+
| id | name | dd | dm     |
+----+------+----+--------+
|  2 | jude |  1 | Retail |
|  3 | Tom  |  1 | Retail |
|  3 | Tom  |  2 | IVS    |
+----+------+----+--------+
3 rows in set (0.00 sec)

mysql> select a.id,a.name,b.dd,b.dm from a,b where a.id>b.dd;
+----+------+----+--------+
| id | name | dd | dm     |
+----+------+----+--------+
|  2 | jude |  1 | Retail |
|  3 | Tom  |  1 | Retail |
|  3 | Tom  |  2 | IVS    |
+----+------+----+--------+
3 rows in set (0.00 sec)

mysql> select a.id,a.name,b.dd,b.dm from a inner join b on a.id>=b.dd;
+----+------+----+--------+
| id | name | dd | dm     |
+----+------+----+--------+
|  1 | paul |  1 | Retail |
|  2 | jude |  1 | Retail |
|  3 | Tom  |  1 | Retail |
|  2 | jude |  2 | IVS    |
|  3 | Tom  |  2 | IVS    |
+----+------+----+--------+
5 rows in set (0.00 sec)

mysql> select a.id,a.name,b.dd,b.dm from a,b where a.id>=b.dd;
+----+------+----+--------+
| id | name | dd | dm     |
+----+------+----+--------+
|  1 | paul |  1 | Retail |
|  2 | jude |  1 | Retail |
|  3 | Tom  |  1 | Retail |
|  2 | jude |  2 | IVS    |
|  3 | Tom  |  2 | IVS    |
+----+------+----+--------+
5 rows in set (0.00 sec)

mysql> select a.id,a.name,b.dd,b.dm from a inner join b on a.id<b.dd;
+----+------+----+------+
| id | name | dd | dm   |
+----+------+----+------+
|  1 | paul |  2 | IVS  |
|  1 | paul |  4 | IT   |
|  2 | jude |  4 | IT   |
|  3 | Tom  |  4 | IT   |
|  1 | paul |  5 | HRD  |
|  2 | jude |  5 | HRD  |
|  3 | Tom  |  5 | HRD  |
+----+------+----+------+
7 rows in set (0.00 sec)

mysql> select a.id,a.name,b.dd,b.dm from a,b where a.id<b.dd;
+----+------+----+------+
| id | name | dd | dm   |
+----+------+----+------+
|  1 | paul |  2 | IVS  |
|  1 | paul |  4 | IT   |
|  2 | jude |  4 | IT   |
|  3 | Tom  |  4 | IT   |
|  1 | paul |  5 | HRD  |
|  2 | jude |  5 | HRD  |
|  3 | Tom  |  5 | HRD  |
+----+------+----+------+
7 rows in set (0.00 sec)

mysql> select a.id,a.name,b.dd,b.dm from a inner join b on a.id<=b.dd;
+----+------+----+--------+
| id | name | dd | dm     |
+----+------+----+--------+
|  1 | paul |  1 | Retail |
|  1 | paul |  2 | IVS    |
|  2 | jude |  2 | IVS    |
|  1 | paul |  4 | IT     |
|  2 | jude |  4 | IT     |
|  3 | Tom  |  4 | IT     |
|  1 | paul |  5 | HRD    |
|  2 | jude |  5 | HRD    |
|  3 | Tom  |  5 | HRD    |
+----+------+----+--------+
9 rows in set (0.00 sec)

mysql> select a.id,a.name,b.dd,b.dm from a,b where a.id<=b.dd;
+----+------+----+--------+
| id | name | dd | dm     |
+----+------+----+--------+
|  1 | paul |  1 | Retail |
|  1 | paul |  2 | IVS    |
|  2 | jude |  2 | IVS    |
|  1 | paul |  4 | IT     |
|  2 | jude |  4 | IT     |
|  3 | Tom  |  4 | IT     |
|  1 | paul |  5 | HRD    |
|  2 | jude |  5 | HRD    |
|  3 | Tom  |  5 | HRD    |
+----+------+----+--------+
9 rows in set (0.00 sec)

mysql> select a.id,a.name,b.dd,b.dm from a inner join b on a.id<>b.dd;
+----+------+----+--------+
| id | name | dd | dm     |
+----+------+----+--------+
|  2 | jude |  1 | Retail |
|  3 | Tom  |  1 | Retail |
|  1 | paul |  2 | IVS    |
|  3 | Tom  |  2 | IVS    |
|  1 | paul |  4 | IT     |
|  2 | jude |  4 | IT     |
|  3 | Tom  |  4 | IT     |
|  1 | paul |  5 | HRD    |
|  2 | jude |  5 | HRD    |
|  3 | Tom  |  5 | HRD    |
+----+------+----+--------+
10 rows in set (0.00 sec)

mysql> select a.id,a.name,b.dd,b.dm from a,b where a.id<>b.dd;
+----+------+----+--------+
| id | name | dd | dm     |
+----+------+----+--------+
|  2 | jude |  1 | Retail |
|  3 | Tom  |  1 | Retail |
|  1 | paul |  2 | IVS    |
|  3 | Tom  |  2 | IVS    |
|  1 | paul |  4 | IT     |
|  2 | jude |  4 | IT     |
|  3 | Tom  |  4 | IT     |
|  1 | paul |  5 | HRD    |
|  2 | jude |  5 | HRD    |
|  3 | Tom  |  5 | HRD    |
+----+------+----+--------+
10 rows in set (0.00 sec)

left join

mysql> select a.id,a.name,b.dd,b.dm from a left join b on a.id=b.dd;
+----+------+------+--------+
| id | name | dd   | dm     |
+----+------+------+--------+
|  1 | paul |    1 | Retail |
|  2 | jude |    2 | IVS    |
|  3 | Tom  | NULL | NULL   |
+----+------+------+--------+
3 rows in set (0.00 sec)

mysql> select a.id,a.name,b.dd,b.dm from a left join b on a.id>b.dd;
+----+------+------+--------+
| id | name | dd   | dm     |
+----+------+------+--------+
|  1 | paul | NULL | NULL   |
|  2 | jude |    1 | Retail |
|  3 | Tom  |    1 | Retail |
|  3 | Tom  |    2 | IVS    |
+----+------+------+--------+
4 rows in set (0.00 sec)

mysql> select a.id,a.name,b.dd,b.dm from a left join b on a.id>=b.dd;
+----+------+------+--------+
| id | name | dd   | dm     |
+----+------+------+--------+
|  1 | paul |    1 | Retail |
|  2 | jude |    1 | Retail |
|  2 | jude |    2 | IVS    |
|  3 | Tom  |    1 | Retail |
|  3 | Tom  |    2 | IVS    |
+----+------+------+--------+
5 rows in set (0.00 sec)

mysql> select a.id,a.name,b.dd,b.dm from a left join b on a.id<b.dd;
+----+------+------+------+
| id | name | dd   | dm   |
+----+------+------+------+
|  1 | paul |    2 | IVS  |
|  1 | paul |    4 | IT   |
|  1 | paul |    5 | HRD  |
|  2 | jude |    4 | IT   |
|  2 | jude |    5 | HRD  |
|  3 | Tom  |    4 | IT   |
|  3 | Tom  |    5 | HRD  |
+----+------+------+------+
7 rows in set (0.00 sec)

mysql> select a.id,a.name,b.dd,b.dm from a left join b on a.id<=b.dd;
+----+------+------+--------+
| id | name | dd   | dm     |
+----+------+------+--------+
|  1 | paul |    1 | Retail |
|  1 | paul |    2 | IVS    |
|  1 | paul |    4 | IT     |
|  1 | paul |    5 | HRD    |
|  2 | jude |    2 | IVS    |
|  2 | jude |    4 | IT     |
|  2 | jude |    5 | HRD    |
|  3 | Tom  |    4 | IT     |
|  3 | Tom  |    5 | HRD    |
+----+------+------+--------+
9 rows in set (0.00 sec)

mysql> select a.id,a.name,b.dd,b.dm from a left join b on a.id<>b.dd;
+----+------+------+--------+
| id | name | dd   | dm     |
+----+------+------+--------+
|  1 | paul |    2 | IVS    |
|  1 | paul |    4 | IT     |
|  1 | paul |    5 | HRD    |
|  2 | jude |    1 | Retail |
|  2 | jude |    4 | IT     |
|  2 | jude |    5 | HRD    |
|  3 | Tom  |    1 | Retail |
|  3 | Tom  |    2 | IVS    |
|  3 | Tom  |    4 | IT     |
|  3 | Tom  |    5 | HRD    |
+----+------+------+--------+
10 rows in set (0.00 sec)

right join

mysql> select a.id,a.name,b.dd,b.dm from a right join b on a.id=b.dd;
+------+------+----+--------+
| id   | name | dd | dm     |
+------+------+----+--------+
|    1 | paul |  1 | Retail |
|    2 | jude |  2 | IVS    |
| NULL | NULL |  4 | IT     |
| NULL | NULL |  5 | HRD    |
+------+------+----+--------+
4 rows in set (0.00 sec)

mysql> select a.id,a.name,b.dd,b.dm from a right join b on a.id>b.dd;
+------+------+----+--------+
| id   | name | dd | dm     |
+------+------+----+--------+
|    2 | jude |  1 | Retail |
|    3 | Tom  |  1 | Retail |
|    3 | Tom  |  2 | IVS    |
| NULL | NULL |  4 | IT     |
| NULL | NULL |  5 | HRD    |
+------+------+----+--------+
5 rows in set (0.00 sec)

mysql> select a.id,a.name,b.dd,b.dm from a right join b on a.id>=b.dd;
+------+------+----+--------+
| id   | name | dd | dm     |
+------+------+----+--------+
|    1 | paul |  1 | Retail |
|    2 | jude |  1 | Retail |
|    3 | Tom  |  1 | Retail |
|    2 | jude |  2 | IVS    |
|    3 | Tom  |  2 | IVS    |
| NULL | NULL |  4 | IT     |
| NULL | NULL |  5 | HRD    |
+------+------+----+--------+
7 rows in set (0.00 sec)

mysql> select a.id,a.name,b.dd,b.dm from a right join b on a.id<b.dd;
+------+------+----+--------+
| id   | name | dd | dm     |
+------+------+----+--------+
| NULL | NULL |  1 | Retail |
|    1 | paul |  2 | IVS    |
|    1 | paul |  4 | IT     |
|    2 | jude |  4 | IT     |
|    3 | Tom  |  4 | IT     |
|    1 | paul |  5 | HRD    |
|    2 | jude |  5 | HRD    |
|    3 | Tom  |  5 | HRD    |
+------+------+----+--------+
8 rows in set (0.00 sec)

mysql> select a.id,a.name,b.dd,b.dm from a right join b on a.id<=b.dd;
+------+------+----+--------+
| id   | name | dd | dm     |
+------+------+----+--------+
|    1 | paul |  1 | Retail |
|    1 | paul |  2 | IVS    |
|    2 | jude |  2 | IVS    |
|    1 | paul |  4 | IT     |
|    2 | jude |  4 | IT     |
|    3 | Tom  |  4 | IT     |
|    1 | paul |  5 | HRD    |
|    2 | jude |  5 | HRD    |
|    3 | Tom  |  5 | HRD    |
+------+------+----+--------+
9 rows in set (0.00 sec)

mysql> select a.id,a.name,b.dd,b.dm from a right join b on a.id<>b.dd;
+------+------+----+--------+
| id   | name | dd | dm     |
+------+------+----+--------+
|    2 | jude |  1 | Retail |
|    3 | Tom  |  1 | Retail |
|    1 | paul |  2 | IVS    |
|    3 | Tom  |  2 | IVS    |
|    1 | paul |  4 | IT     |
|    2 | jude |  4 | IT     |
|    3 | Tom  |  4 | IT     |
|    1 | paul |  5 | HRD    |
|    2 | jude |  5 | HRD    |
|    3 | Tom  |  5 | HRD    |
+------+------+----+--------+
10 rows in set (0.00 sec)

full join

错误执行代码：

mysql> select a.name,b.dm from a full join b on a.id=b.dd;
ERROR 1054 (42S22): Unknown column 'a.name' in 'field list'

mysql> select id,name,dd,dm from a full join b on id=dd;
+----+------+----+--------+
| id | name | dd | dm     |
+----+------+----+--------+
|  1 | paul |  1 | Retail |
|  2 | jude |  2 | IVS    |
+----+------+----+--------+
2 rows in set (0.00 sec)

mysql> select id,name,dd,dm from a full join b on id<dd;
+----+------+----+------+
| id | name | dd | dm   |
+----+------+----+------+
|  1 | paul |  2 | IVS  |
|  1 | paul |  4 | IT   |
|  2 | jude |  4 | IT   |
|  3 | Tom  |  4 | IT   |
|  1 | paul |  5 | HRD  |
|  2 | jude |  5 | HRD  |
|  3 | Tom  |  5 | HRD  |
+----+------+----+------+
7 rows in set (0.00 sec)

mysql> select id,name,dd,dm from a full join b on id<=dd;
+----+------+----+--------+
| id | name | dd | dm     |
+----+------+----+--------+
|  1 | paul |  1 | Retail |
|  1 | paul |  2 | IVS    |
|  2 | jude |  2 | IVS    |
|  1 | paul |  4 | IT     |
|  2 | jude |  4 | IT     |
|  3 | Tom  |  4 | IT     |
|  1 | paul |  5 | HRD    |
|  2 | jude |  5 | HRD    |
|  3 | Tom  |  5 | HRD    |
+----+------+----+--------+
9 rows in set (0.00 sec)

mysql> select id,name,dd,dm from a full join b on id>dd;
+----+------+----+--------+
| id | name | dd | dm     |
+----+------+----+--------+
|  2 | jude |  1 | Retail |
|  3 | Tom  |  1 | Retail |
|  3 | Tom  |  2 | IVS    |
+----+------+----+--------+
3 rows in set (0.00 sec)

mysql> select id,name,dd,dm from a full join b on id>=dd;
+----+------+----+--------+
| id | name | dd | dm     |
+----+------+----+--------+
|  1 | paul |  1 | Retail |
|  2 | jude |  1 | Retail |
|  3 | Tom  |  1 | Retail |
|  2 | jude |  2 | IVS    |
|  3 | Tom  |  2 | IVS    |
+----+------+----+--------+
5 rows in set (0.00 sec)

mysql> select id,name,dd,dm from a full join b on id<>dd;
+----+------+----+--------+
| id | name | dd | dm     |
+----+------+----+--------+
|  2 | jude |  1 | Retail |
|  3 | Tom  |  1 | Retail |
|  1 | paul |  2 | IVS    |
|  3 | Tom  |  2 | IVS    |
|  1 | paul |  4 | IT     |
|  2 | jude |  4 | IT     |
|  3 | Tom  |  4 | IT     |
|  1 | paul |  5 | HRD    |
|  2 | jude |  5 | HRD    |
|  3 | Tom  |  5 | HRD    |
+----+------+----+--------+
10 rows in set (0.00 sec)