Mysql基础学习（十六） -- JOIN查询详解

SQL执行顺序

1. 手写
   
2. 机读
3. 总结
   

7种JOIN理论

1. INNER JOIN
   

SELECT <select_list>
FROM TABLEA a
INNER JOIN TABLEB b
ON a.key = b.kye;

2. LEFT JOIN
   

SELECT <select_list>
FROM TABLEA a
LEFT JOIN TABLEB b
a.key = b.key;

3. RIGHT JOIN
   

SELECT <select_list>
FROM TABLEA a
RIGHT JOIN TABLEB b
ON a.key = b.key;

4. LEFT JOIN CONDITION IS NULL
   

SELECT <select_list>
FROM TABLE_A a
LEFT JOIN TABLE_B b
ON a.key = b.key 
WHERE b.key IS NULL;

5. RIGHT JOIN CODITION IS NULL
   

SELECT <select_list>
FROM TABLE_A a
RIGHT JOIN TABLE_B b
ON a.key = b.key
WHERE a.key IS NULL;

6. FULL OUTER JOIN
   

SELECT <select_list>
FROM TABLE_A a
FULL OUTER JOIN TABLE_B b
ON a.key = b.key;

7. FULL OUTER JOIN
   

SELECT <select_list>
FROM TABLE_A a
FULL OUTER JOIN TABLE_B b
ON a.key = b.key
WHERE a.key IS NULL
OR b.key IS NULl;

Mysql 种如何使用FULL OUTER JOIN

Mysql 中是无法使用 FULL OUTER JOIN的，那么我们应该如何使用 FULL OUTER JOIN 呢
**使用 UNION : ** union 的作用是联合两张表，并且去重
第六种图示查询方式

SELECT * FROM TABLE_A a LEFT JOIN TABLE_B b on a.key = b.key
UNION
SELECT * FROM TABLE_A a RIGHT JOIN TABLE_B b on a.key = b.key;

第七种图示查询方式

 SELECT * FROM tbl_emp a LEFT JOIN tbl_dept b ON a.deptId = b.id WHERE b.id IS NULL
 UNION
 SELECT * FROM tbl_emp a RIGHT JOIN tbl_dept b ON a.deptId = b.id WHERE a.id IS NULL;