当前表中的数据
mysql> select * from t_dept;
+----+----------+-----------+
| id | deptName | address |
+----+----------+-----------+
| 1 | 华山 | 华山 |
| 2 | 丐帮 | 洛阳 |
| 3 | 峨眉 | 峨眉山 |
| 4 | 武当 | 武当山 |
| 5 | 明教 | 光明顶 |
| 6 | 少林 | 少林寺 |
+----+----------+-----------+
6 rows in set (0.00 sec)
mysql> select * from t_emp;
+----+--------------+------+--------+--------+
| id | name | age | deptId | empno |
+----+--------------+------+--------+--------+
| 1 | 风清扬 | 90 | 1 | 100001 |
| 2 | 岳不群 | 50 | 1 | 100002 |
| 3 | 令狐冲 | 24 | 1 | 100003 |
| 4 | 洪七公 | 70 | 2 | 100004 |
| 5 | 乔峰 | 35 | 2 | 100005 |
| 6 | 灭绝师太 | 70 | 3 | 100006 |
| 7 | 周芷若 | 20 | 3 | 100007 |
| 8 | 张三丰 | 100 | 4 | 100008 |
| 9 | 张无忌 | 25 | 5 | 100009 |
| 10 | 韦小宝 | 18 | NULL | 100010 |
+----+--------------+------+--------+--------+
10 rows in set (0.00 sec)
该查询可以查询出A,B中共有数据
mysql> select * from t_dept a inner join t_emp b on a.id= b.deptId;
+----+----------+-----------+----+--------------+------+--------+--------+
| id | deptName | address | id | name | age | deptId | empno |
+----+----------+-----------+----+--------------+------+--------+--------+
| 1 | 华山 | 华山 | 1 | 风清扬 | 90 | 1 | 100001 |
| 1 | 华山 | 华山 | 2 | 岳不群 | 50 | 1 | 100002 |
| 1 | 华山 | 华山 | 3 | 令狐冲 | 24 | 1 | 100003 |
| 2 | 丐帮 | 洛阳 | 4 | 洪七公 | 70 | 2 | 100004 |
| 2 | 丐帮 | 洛阳 | 5 | 乔峰 | 35 | 2 | 100005 |
| 3 | 峨眉 | 峨眉山 | 6 | 灭绝师太 | 70 | 3 | 100006 |
| 3 | 峨眉 | 峨眉山 | 7 | 周芷若 | 20 | 3 | 100007 |
| 4 | 武当 | 武当山 | 8 | 张三丰 | 100 | 4 | 100008 |
| 5 | 明教 | 光明顶 | 9 | 张无忌 | 25 | 5 | 100009 |
+----+----------+-----------+----+--------------+------+--------+--------+
9 rows in set (0.01 sec)
查询左边表即from的表的全部数据和与右边表的共有数据,此时左边表的私有数据在右边表会被null补全
mysql> select * from t_dept a left join t_emp b on a.id=b.deptId;
+----+----------+-----------+------+--------------+------+--------+--------+
| id | deptName | address | id | name | age | deptId | empno |
+----+----------+-----------+------+--------------+------+--------+--------+
| 1 | 华山 | 华山 | 1 | 风清扬 | 90 | 1 | 100001 |
| 1 | 华山 | 华山 | 2 | 岳不群 | 50 | 1 | 100002 |
| 1 | 华山 | 华山 | 3 | 令狐冲 | 24 | 1 | 100003 |
| 2 | 丐帮 | 洛阳 | 4 | 洪七公 | 70 | 2 | 100004 |
| 2 | 丐帮 | 洛阳 | 5 | 乔峰 | 35 | 2 | 100005 |
| 3 | 峨眉 | 峨眉山 | 6 | 灭绝师太 | 70 | 3 | 100006 |
| 3 | 峨眉 | 峨眉山 | 7 | 周芷若 | 20 | 3 | 100007 |
| 4 | 武当 | 武当山 | 8 | 张三丰 | 100 | 4 | 100008 |
| 5 | 明教 | 光明顶 | 9 | 张无忌 | 25 | 5 | 100009 |
| 6 | 少林 | 少林寺 | NULL | NULL | NULL | NULL | NULL |
+----+----------+-----------+------+--------------+------+--------+--------+
10 rows in set (0.00 sec)
查询右边表和左边表的共有数据和右边表的私有数据,左边表在右边表私有数据的体现为null
| deptName | address | id | name | age | deptId | empno |
+------+----------+-----------+----+--------------+------+--------+--------+
| 1 | 华山 | 华山 | 1 | 风清扬 | 90 | 1 | 100001 |
| 1 | 华山 | 华山 | 2 | 岳不群 | 50 | 1 | 100002 |
| 1 | 华山 | 华山 | 3 | 令狐冲 | 24 | 1 | 100003 |
| 2 | 丐帮 | 洛阳 | 4 | 洪七公 | 70 | 2 | 100004 |
| 2 | 丐帮 | 洛阳 | 5 | 乔峰 | 35 | 2 | 100005 |
| 3 | 峨眉 | 峨眉山 | 6 | 灭绝师太 | 70 | 3 | 100006 |
| 3 | 峨眉 | 峨眉山 | 7 | 周芷若 | 20 | 3 | 100007 |
| 4 | 武当 | 武当山 | 8 | 张三丰 | 100 | 4 | 100008 |
| 5 | 明教 | 光明顶 | 9 | 张无忌 | 25 | 5 | 100009 |
| NULL | NULL | NULL | 10 | 韦小宝 | 18 | NULL | 100010 |
+------+----------+-----------+----+--------------+------+--------+--------+
10 rows in set (0.00 sec)
查询左边表的私有数据
mysql> select * from t_dept a left join t_emp b on a.id=b.deptId where b.deptId is null;
+----+----------+-----------+------+------+------+--------+-------+
| id | deptName | address | id | name | age | deptId | empno |
+----+----------+-----------+------+------+------+--------+-------+
| 6 | 少林 | 少林寺 | NULL | NULL | NULL | NULL | NULL |
+----+----------+-----------+------+------+------+--------+-------+
1 row in set (0.00 sec)
查询右边表的私有数据
mysql> select * from t_dept a right join t_emp b on a.id=b.deptId where b.deptId is null;
+------+----------+---------+----+-----------+------+--------+--------+
| id | deptName | address | id | name | age | deptId | empno |
+------+----------+---------+----+-----------+------+--------+--------+
| NULL | NULL | NULL | 10 | 韦小宝 | 18 | NULL | 100010 |
+------+----------+---------+----+-----------+------+--------+--------+
1 row in set (0.01 sec)
查询两张表的所有数据。私有数据通过null来补全
由于mysql
不支持full outer
所以通过union
来连接查询
mysql> select * from t_dept a right join t_emp b on a.id=b.deptId
-> union
-> select * from t_dept a left join t_emp b on a.id=b.deptId;
+------+----------+-----------+------+--------------+------+--------+--------+
| id | deptName | address | id | name | age | deptId | empno |
+------+----------+-----------+------+--------------+------+--------+--------+
| 1 | 华山 | 华山 | 1 | 风清扬 | 90 | 1 | 100001 |
| 1 | 华山 | 华山 | 2 | 岳不群 | 50 | 1 | 100002 |
| 1 | 华山 | 华山 | 3 | 令狐冲 | 24 | 1 | 100003 |
| 2 | 丐帮 | 洛阳 | 4 | 洪七公 | 70 | 2 | 100004 |
| 2 | 丐帮 | 洛阳 | 5 | 乔峰 | 35 | 2 | 100005 |
| 3 | 峨眉 | 峨眉山 | 6 | 灭绝师太 | 70 | 3 | 100006 |
| 3 | 峨眉 | 峨眉山 | 7 | 周芷若 | 20 | 3 | 100007 |
| 4 | 武当 | 武当山 | 8 | 张三丰 | 100 | 4 | 100008 |
| 5 | 明教 | 光明顶 | 9 | 张无忌 | 25 | 5 | 100009 |
| NULL | NULL | NULL | 10 | 韦小宝 | 18 | NULL | 100010 |
| 6 | 少林 | 少林寺 | NULL | NULL | NULL | NULL | NULL |
+------+----------+-----------+------+--------------+------+--------+--------+
11 rows in set (0.00 sec)
查询两张表中的所有的私有数据
mysql> select * from t_dept a left join t_emp b on a.id=b.deptId where b.deptId is null
-> union
-> select * from t_dept a right join t_emp b on a.id=b.deptId where a.id is null ;
+------+----------+-----------+------+-----------+------+--------+--------+
| id | deptName | address | id | name | age | deptId | empno |
+------+----------+-----------+------+-----------+------+--------+--------+
| 6 | 少林 | 少林寺 | NULL | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | 10 | 韦小宝 | 18 | NULL | 100010 |
+------+----------+-----------+------+-----------+------+--------+--------+
2 rows in set (0.00 sec)