SQL_7种JOIN理论与实例

---

实例

现在有两张表，分别是employee表和dept表

mysql> select * from employee;
+----+---------------+---------+
| id | employee_name | dept_id |
+----+---------------+---------+
|  1 | zhangsan      |       1 |
|  2 | lisi          |       3 |
|  3 | wangwu        |       2 |
|  4 | zhaoliu       |       4 |
|  5 | liuqi         |     100 |
+----+---------------+---------+
5 rows in set (0.00 sec)

mysql> select * from dept;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | 人事部    |
|       2 | 测试部    |
|       3 | 生产部    |
|       4 | 研发部    |
|       5 | 运营部    |
|       6 | 财务部    |
+---------+-----------+

---

#理论：
SELECT
	< select_list >
FROM
	tableA A
INNER JOIN tableB B ON A.key = B.key

#实例sql：
SELECT * FROM `employee` INNER JOIN `dept` ON `employee`.dept_id = `dept`.dept_id;
+----+---------------+---------+---------+-----------+
| id | employee_name | dept_id | dept_id | dept_name |
+----+---------------+---------+---------+-----------+
|  1 | zhangsan      |       1 |       1 | 人事部    |
|  2 | lisi          |       3 |       3 | 生产部    |
|  3 | wangwu        |       2 |       2 | 测试部    |
|  4 | zhaoliu       |       4 |       4 | 研发部    |
+----+---------------+---------+---------+-----------+

---

#理论：
SELECT
	< select_list >
FROM
	tableA A
LEFT JOIN tableB B ON A.key = B.key

#实例sql：
SELECT * FROM `employee` LEFT JOIN `dept` ON `employee`.dept_id = `dept`.dept_id;
+----+---------------+---------+---------+-----------+
| id | employee_name | dept_id | dept_id | dept_name |
+----+---------------+---------+---------+-----------+
|  1 | zhangsan      |       1 |       1 | 人事部    |
|  2 | lisi          |       3 |       3 | 生产部    |
|  3 | wangwu        |       2 |       2 | 测试部    |
|  4 | zhaoliu       |       4 |       4 | 研发部    |
|  5 | liuqi         |     100 |    NULL | NULL      |
+----+---------------+---------+---------+-----------+

---

#理论：
SELECT
	< select_list >
FROM
	tableA A
RIGHT JOIN tableB B ON A.key = B.key

#实例sql：
SELECT * FROM `employee` RIGHT JOIN `dept` ON `employee`.dept_id = `dept`.dept_id;
+------+---------------+---------+---------+-----------+
| id   | employee_name | dept_id | dept_id | dept_name |
+------+---------------+---------+---------+-----------+
|    1 | zhangsan      |       1 |       1 | 人事部    |
|    2 | lisi          |       3 |       3 | 生产部    |
|    3 | wangwu        |       2 |       2 | 测试部    |
|    4 | zhaoliu       |       4 |       4 | 研发部    |
| NULL | NULL          |    NULL |       5 | 运营部    |
| NULL | NULL          |    NULL |       6 | 财务部    |
+------+---------------+---------+---------+-----------+

---

#理论：
SELECT
	< select_list >
FROM
	tableA A
LEFT JOIN tableB B ON A.key = B.key
WHERE B.key IS NULL;

#实例sql：
SELECT * FROM `employee` LEFT JOIN `dept` ON `employee`.dept_id = `dept`.dept_id WHERE `dept`.dept_id IS NULL;
+----+---------------+---------+---------+-----------+
| id | employee_name | dept_id | dept_id | dept_name |
+----+---------------+---------+---------+-----------+
|  5 | liuqi         |     100 |    NULL | NULL      |
+----+---------------+---------+---------+-----------+

---

#理论：
SELECT
	< select_list >
FROM
	tableA A
RIGHT JOIN tableB B ON A.key = B.key
WHERE A.key IS NULL

#实例sql：
SELECT * FROM `employee` RIGHT JOIN `dept` ON `employee`.dept_id = `dept`.dept_id WHERE `employee`.dept_id IS NULL;
+------+---------------+---------+---------+-----------+
| id   | employee_name | dept_id | dept_id | dept_name |
+------+---------------+---------+---------+-----------+
| NULL | NULL          |    NULL |       5 | 运营部    |
| NULL | NULL          |    NULL |       6 | 财务部    |
+------+---------------+---------+---------+-----------+

---

#理论：
SELECT
	< select_list >
FROM
	tableA A
FULL OUTER JOIN tableB B ON A.key = B.key

#实例：
SELECT * FROM `employee` LEFT JOIN `dept` ON `employee`.dept_id = `dept`.dept_id
UNION
SELECT * FROM `employee` RIGHT JOIN `dept` ON `employee`.dept_id = `dept`.dept_id;
+------+---------------+---------+---------+-----------+
| id   | employee_name | dept_id | dept_id | dept_name |
+------+---------------+---------+---------+-----------+
|    1 | zhangsan      |       1 |       1 | 人事部    |
|    2 | lisi          |       3 |       3 | 生产部    |
|    3 | wangwu        |       2 |       2 | 测试部    |
|    4 | zhaoliu       |       4 |       4 | 研发部    |
|    5 | liuqi         |     100 |    NULL | NULL      |
| NULL | NULL          |    NULL |       5 | 运营部    |
| NULL | NULL          |    NULL |       6 | 财务部    |
+------+---------------+---------+---------+-----------+

---

#理论：
SELECT
	< select_list >
FROM
	tableA A
FULL OUTER JOIN tableB B ON A.key = B.key
WHERE A.key IS NULL OR B.key IS NULL

#实例sql：
SELECT * FROM `employee` LEFT JOIN `dept` ON `employee`.dept_id = `dept`.dept_id WHERE `dept`.dept_id IS NULL
UNION
SELECT * FROM `employee` RIGHT JOIN `dept` ON `employee`.dept_id = `dept`.dept_id WHERE `employee`.dept_id IS NULL;
+------+---------------+---------+---------+-----------+
| id   | employee_name | dept_id | dept_id | dept_name |
+------+---------------+---------+---------+-----------+
|    5 | liuqi         |     100 |    NULL | NULL      |
| NULL | NULL          |    NULL |       5 | 运营部    |
| NULL | NULL          |    NULL |       6 | 财务部    |
+------+---------------+---------+---------+-----------+