MySql join那些事

创建数据库

create database business;
use business;

创建表

create table dept(
 id int(11) not null auto_increment,
 deptName varchar(30) default null,
 floor varchar(40) default null,
 primary key(id)
) engine=innodb auto_increment=1 default charset=utf8;

create table emp(
 id int(11) not null auto_increment,
 name varchar(20) default null,
 deptId int(11) default null,
 primary key(id)
) engine=innodb auto_increment=1 default charset=utf8;

insert into dept(deptName,floor) values('Develop',1);
insert into dept(deptName,floor) values('HuamanResource',2);
insert into dept(deptName,floor) values('Market',3);

insert into emp(name,deptId) values('zs',1);
insert into emp(name,deptId) values('ls',1);
insert into emp(name,deptId) values('ww',1);
insert into emp(name,deptId) values('zl',2);
insert into emp(name,deptId) values('qq',4);

员工表

select * from emp;

+----+------+--------+
| id | name | deptId |
+----+------+--------+
|  1 | zs   |      1 |
|  2 | ls   |      1 |
|  3 | ww   |      1 |
|  4 | zl   |      2 |
|  5 | qq   |      4 |
+----+------+--------+
5 rows in set (0.02 sec)

部门表

select * from dept;

+----+----------------+-------+
| id | deptName       | floor |
+----+----------------+-------+
|  1 | Develop        | 1     |
|  2 | HuamanResource | 2     |
|  3 | Market         | 3     |
+----+----------------+-------+
3 rows in set (0.02 sec)

select * from dept,emp;  -- 笛卡尔积

+----+----------------+-------+----+------+--------+
| id | deptName       | floor | id | name | deptId |
+----+----------------+-------+----+------+--------+
|  1 | Develop        | 1     |  1 | zs   |      1 |
|  2 | HuamanResource | 2     |  1 | zs   |      1 |
|  3 | Market         | 3     |  1 | zs   |      1 |
|  1 | Develop        | 1     |  2 | ls   |      1 |
|  2 | HuamanResource | 2     |  2 | ls   |      1 |
|  3 | Market         | 3     |  2 | ls   |      1 |
|  1 | Develop        | 1     |  3 | ww   |      1 |
|  2 | HuamanResource | 2     |  3 | ww   |      1 |
|  3 | Market         | 3     |  3 | ww   |      1 |
|  1 | Develop        | 1     |  4 | zl   |      2 |
|  2 | HuamanResource | 2     |  4 | zl   |      2 |
|  3 | Market         | 3     |  4 | zl   |      2 |
|  1 | Develop        | 1     |  5 | qq   |      4 |
|  2 | HuamanResource | 2     |  5 | qq   |      4 |
|  3 | Market         | 3     |  5 | qq   |      4 |
+----+----------------+-------+----+------+--------+
15 rows in set (0.00 sec)

select * from  emp a inner join dept b on a.deptId=b.id; -- 公有

+----+------+--------+----+----------------+-------+
| id | name | deptId | id | deptName       | floor |
+----+------+--------+----+----------------+-------+
|  1 | zs   |      1 |  1 | Develop        | 1     |
|  2 | ls   |      1 |  1 | Develop        | 1     |
|  3 | ww   |      1 |  1 | Develop        | 1     |
|  4 | zl   |      2 |  2 | HuamanResource | 2     |
+----+------+--------+----+----------------+-------+
4 rows in set (0.01 sec)

select * from  emp a left join dept b on a.deptId=b.id; -- 全a

+----+------+--------+------+----------------+-------+
| id | name | deptId | id   | deptName       | floor |
+----+------+--------+------+----------------+-------+
|  1 | zs   |      1 |    1 | Develop        | 1     |
|  2 | ls   |      1 |    1 | Develop        | 1     |
|  3 | ww   |      1 |    1 | Develop        | 1     |
|  4 | zl   |      2 |    2 | HuamanResource | 2     |
|  5 | qq   |      4 | NULL | NULL           | NULL  |
+----+------+--------+------+----------------+-------+
5 rows in set (0.01 sec)

select * from  emp a right join dept b on a.deptId=b.id; -- 全b

+------+------+--------+----+----------------+-------+
| id   | name | deptId | id | deptName       | floor |
+------+------+--------+----+----------------+-------+
|    1 | zs   |      1 |  1 | Develop        | 1     |
|    2 | ls   |      1 |  1 | Develop        | 1     |
|    3 | ww   |      1 |  1 | Develop        | 1     |
|    4 | zl   |      2 |  2 | HuamanResource | 2     |
| NULL | NULL |   NULL |  3 | Market         | 3     |
+------+------+--------+----+----------------+-------+
5 rows in set (0.00 sec)

select * from  emp a left join dept b on a.deptId=b.id where b.id is null; -- 独a

+----+------+--------+------+----------+-------+
| id | name | deptId | id   | deptName | floor |
+----+------+--------+------+----------+-------+
|  5 | qq   |      4 | NULL | NULL     | NULL  |
+----+------+--------+------+----------+-------+
1 row in set (0.01 sec)

select * from  emp a right join dept b on a.deptId=b.id where a.deptId is null; -- 独b

+------+------+--------+----+----------+-------+
| id   | name | deptId | id | deptName | floor |
+------+------+--------+----+----------+-------+
| NULL | NULL |   NULL |  3 | Market   | 3     |
+------+------+--------+----+----------+-------+
1 row in set (0.00 sec)

select * from  emp a left join dept b on a.deptId=b.id
union
select * from  emp a right join dept b on a.deptId=b.id; -- 全有

+------+------+--------+------+----------------+-------+
| id   | name | deptId | id   | deptName       | floor |
+------+------+--------+------+----------------+-------+
|    1 | zs   |      1 |    1 | Develop        | 1     |
|    2 | ls   |      1 |    1 | Develop        | 1     |
|    3 | ww   |      1 |    1 | Develop        | 1     |
|    4 | zl   |      2 |    2 | HuamanResource | 2     |
|    5 | qq   |      4 | NULL | NULL           | NULL  |
| NULL | NULL |   NULL |    3 | Market         | 3     |
+------+------+--------+------+----------------+-------+
6 rows in set (0.02 sec)

select * from  emp a left join dept b on a.deptId=b.id where b.id is null
union
select * from  emp a right join dept b on a.deptId=b.id where a.deptId is null; -- a、b两者独有

+------+------+--------+------+----------+-------+
| id   | name | deptId | id   | deptName | floor |
+------+------+--------+------+----------+-------+
|    5 | qq   |      4 | NULL | NULL     | NULL  |
| NULL | NULL |   NULL |    3 | Market   | 3     |
+------+------+--------+------+----------+-------+
2 rows in set (0.00 sec)