笛卡爾積的理解
select id,department_name from employees,departments;#错的 select id,department_id,department_name from employees CROSS JOIN departments;#错的
每個員工和每個部門都符合了一遍（查出的條目數= id數*department數）
錯誤原因：缺少連接條件
編寫連接條件: 表1.列= 表二.列（若多個表連接，至少要用n-1個連接條件）
select id,employees.name,department_name from employees,departments WHERE employees.name = departments.name;
註：如果要顯示的列在要查詢的表中名字一樣，則要表明，是出自哪個表， eg: employees .name
建議在多表查詢時，標明顯示的是哪個表的資訊（優化）
優化：可以在FROM後使用表的別名，但是一旦使用別名，後續就一定要都用別名
等值連接：上述的帶有=的
非等值連接：沒有=的
select t1.id,t1.name,t2.grade from employees t1,departments t2 WHERE ti.salary BETWEEN t2.lowest_salary AND t2.highest_salary ;#非等值
非自連結：表1和表2連接
自連結：表1和自己連接
#显示员工（t1）和其管理者（t2）的基本信息 select t1.id,t1.name,t2.id，t2.name from employees t1,employees t2#一个表看作两个表 WHERE t1.manage_id = t2.id ;#自连接
內連接：合併含有相同列的表，結果不包含一個表與另一個表不符打的行
外連接：合併含有同一列的表，結果除了內連接的結果還查詢不匹配的行
外連接的分類：左外連接（左表多，補右邊），右外連線（右表多，補左邊），滿外連線
內連線：見上
#外連線：左表有資料不匹配，在右表加（ ）；反之，在左表加（ ），但是MySQL不支援
WHERE t1.department_id = t2.department_id(+)#左连接
select t1.id,t1.name,t2.department_name,t3.environment from employees t1 JOIN departments t2 ON t1.department_id = t2.department_id JOIN locations t3#加入第二个人表 ON t2.department_location = t3.department_location;
使用OUTER JOIN...ON...
#左外連接：LEFT OUTER JOIN
右外連線：RIGHT OUTER JOIN
滿外連線：FULL OUTER JOIN（MySQL不支援）
select t1.name,t2.department_name#左外连接 from employees t1 LEFT OUTER（可省略） JOIN departments t2 ON t1.department_id = t2.department_id；
合併查詢結果
SELECT colum... FROM table1 UNION (ALL) SELECT colum... FROM table2
UNION運算子
兩個查詢結果的並集，去重（效率低）
UNION ALL運算子（建議）
#兩個查詢結果的並集，不去重（效率高）
7種SQL JOINS的實作
中圖（內連接）：
select t1.name,t2.department_name from employees t1 JOIN departments t2 ON t1.department_id = t2.department_id；
左上圖(左外連接)：
select t1.name,t2.department_name from employees t1 LEFT JOIN departments t2 ON t1.department_id = t2.department_id；
右上圖(右外連接)：
select t1.name,t2.department_name from employees t1 RIGHT JOIN departments t2 ON t1.department_id = t2.department_id；
左中圖：
select t1.name,t2.department_name from employees t1 LEFT JOIN departments t2 ON t1.department_id = t2.department_id WHERE t2.department_id IS NULL;
右中圖：
select t1.name,t2.department_name from employees t1 RIGHT JOIN departments t2 ON t1.department_id = t2.department_id WHERE t1.department_id IS NULL;
左下圖(滿外連接) ：
#方式一：左上图 UNION ALL 右中图 select t1.name,t2.department_name from employees t1 LEFT JOIN departments t2 ON t1.department_id = t2.department_id UNION ALL select t1.name,t2.department_name from employees t1 RIGHT JOIN departments t2 ON t1.department_id = t2.department_id WHERE t1.department_id IS NULL; #方式二：左中图 UNION ALL 右上图 select t1.name,t2.department_name from employees t1 LEFT JOIN departments t2 ON t1.department_id = t2.department_id WHERE t2.department_id IS NULL UNION ALL select t1.name,t2.department_name from employees t1 RIGHT JOIN departments t2 ON t1.department_id = t2.department_id；
右下圖：
#左中图 UNION ALL 右中图 select t1.name,t2.department_name from employees t1 LEFT JOIN departments t2 ON t1.department_id = t2.department_id WHERE t2.department_id IS NULL UNION ALL select t1.name,t2.department_name from employees t1 RIGHT JOIN departments t2 ON t1.department_id = t2.department_id WHERE t1.department_id IS NULL;
使用關鍵字：NATURAL JOIN（不靈活），自動查詢表中所有相同字段，然後進行等值連接
#使用關鍵字：USING（同名字段），將表中相同名字的字段自動等值連接
select t1.name,t2.department_name from employees t1 JOIN departments t2 ON t1.department_id = t2.department_id; 等价于 select t1.name,t2.department_name from employees t1 JOIN departments t2 USING(department_id);
以上是MySQL多表查詢案例分析的詳細內容。更多資訊請關注PHP中文網其他相關文章！
