本節主要針對MySQL數據庫的多表查詢操作常用SQL命令進行總結，主要包括内連接(INNER JOIN)，左外部連接(LEFT JOIN)，右外部連接(RIGHT JOIN)，全連接(FULL JOIN)，交叉連接(CROSS JOIN)，自然連接(NATURAL JOIN)，自連接(SELF JOIN)，聯合查詢(UNION)。
SQL JOIN
1.内連接-INNER JOININNER JOIN 主要是獲取兩個或多張表的交集，隻返回匹配的行；多張表之間沒有主表附表之分。
//INNER JOIN = JOIN
//規則1
SELECT column_list FROM table1 t1 INNER JOIN table2 t2 ON t1.column_name=t2.column_name;
SELECT * FROM table1 t1 INNER JOIN table2 t2 ON t1.主鍵=t2.外鍵; //常用寫法
//規則2
SELECT column_list FROM table1 t1 JOIN table2 t2 ON t1.column_name=t2.column_name;
SELECT * FROM table1 t1 JOIN table2 t2 ON t1.主鍵=t2.外鍵;//常用寫法
//規則3
SELECT column_list FROM table1 t1 JOIN table2 t2 USING (相同列名);
SELECT * FROM table1 t1 JOIN table2 t2 USING (id);//常用寫法
//規則4
SELECT column_list FROM table1 t1, table2 t2 where t1.column_name=t2.column_name;
SELECT * FROM table1 t1, table2 t2 where t1.主鍵=t2.外鍵;//常用寫法
//案例
SELECT t1.*, t2.* FROM user t1 INNER JOIN role t2 ON t1.id = t2.uid;
SELECT t1.*, t2.* FROM user t1 JOIN role t2 ON t1.id = t2.uid;
SELECT t1.*, t2.* FROM users1 t1 JOIN users2 t2 USING (id);
SELECT t1.*, t2.* FROM user t1, role t2 where t1.id = t2.uid;
SQL JOIN - INNER JOIN
2.左外部連接-LEFT JOINLEFT JOIN 以左表為主表，右表為附表；返回左表的全部行和右表滿足ON條件的行；
如果左表的行在右表中沒有匹配，那麼這一行右表中對應數據用NULL代替；
可以是一主一附(一個左表，一個右表)，也可以是一主多附(一個左表，多個右表)
//LEFT JOIN = LEFT OUTER JOIN
//規則1
SELECT column_list FROM table1 t1 LEFT JOIN table2 t2 ON t1.column_name=t2.column_name;
SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t1.主鍵=t2.外鍵; //常用寫法
//規則2
SELECT column_list FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t1.column_name=t2.column_name;
SELECT * FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t1.主鍵=t2.外鍵;//常用寫法
//案例
SELECT t1.*, t2.* FROM user t1 LEFT JOIN role t2 ON t1.id = t2.uid;
SELECT t1.*, t2.* FROM user t1 LEFT OUTER JOIN role t2 ON t1.id = t2.uid;
//user 是主表，role和resource是附表
SELECT t1.*, t2.*,t3.* FROM user t1
LEFT JOIN role t2 ON t1.id = t2.uid
LEFT JOIN resource t3 ON t1.id = t3.uid;
SQL JOIN - LEFT JOIN
3.右外部連接-RIGHT JOINRIGHT JOIN 以右表為主表，右表為附表；返回右表的全部行和左表滿足ON條件的行；
如果右表的行在左表中沒有匹配，那麼這一行左表中對應數據用NULL代替；
//RIGHT JOIN = RIGHT OUTER JOIN
//規則1
SELECT column_list FROM table1 t1 RIGHT JOIN table2 t2 ON t1.column_name=t2.column_name;
SELECT * FROM table1 t1 RIGHT JOIN table2 t2 ON t1.主鍵=t2.外鍵; //常用寫法
//規則2
SELECT column_list FROM table1 t1 RIGHT OUTER JOIN table2 t2 ON t1.column_name=t2.column_name;
SELECT * FROM table1 t1 RIGHT OUTER JOIN table2 t2 ON t1.主鍵=t2.外鍵;//常用寫法
//案例
SELECT t1.*, t2.* FROM user t1 RIGHT JOIN role t2 ON t1.id = t2.uid;
SELECT t1.*, t2.* FROM user t1 RIGHT OUTER JOIN role t2 ON t1.id = t2.uid;
SQL JOIN - RIGHT JOIN
4.全連接-FULL JOINFULL JOIN 會從左表和右表返回符合條件的所有行；
隻要左表（table1）和右表（table2）其中一個表中存在匹配，就返回結果；
如果其中一個表的數據行在另一個表中沒有匹配的行，那麼對面的數據用NULL代替；
FULL JOIN 結合了 LEFT JOIN 和 RIGHT JOIN 的結果;
SQLServer 和Oracle支持FULL JOIN；
MySQL不支持FULL JOIN,可以使用LEFT JOIN UNION RIGHT JOIN來達到效果
//FULL JOIN = FULL OUTER JOIN
//規則1
SELECT column_list FROM table1 t1 FULL JOIN table2 t2 ON t1.column_name=t2.column_name;
SELECT * FROM table1 t1 FULL JOIN table2 t2 ON t1.主鍵=t2.外鍵; //常用寫法
//規則2
SELECT column_list FROM table1 t1 FULL OUTER JOIN table2 t2 ON t1.column_name=t2.column_name;
SELECT * FROM table1 t1 FULL OUTER JOIN table2 t2 ON t1.主鍵=t2.外鍵;//常用寫法
//mysql中LEFT JOIN UNION RIGHT JOIN
SELECT column_list FROM table1 t1 LEFT JOIN table2 t2 ON t1.column_name=t2.column_name
UNION
SELECT column_list FROM table1 t1 RIGHT JOIN table2 t2 ON t1.column_name=t2.column_name;
//案例 SQLServer / Oracle下使用
SELECT t1.*, t2.* FROM user t1 FULL JOIN role t2 ON t1.id = t2.uid;
SELECT t1.*, t2.* FROM user t1 FULL OUTER JOIN role t2 ON t1.id = t2.uid;
//案例 MySQL下使用
SELECT * FROM user t1 LEFT JOIN role t2 ON t1.id = t2.uid
UNION
SELECT * FROM user t1 RIGHT JOIN role t2 ON t1.id = t2.uid;
SQL JOIN - FULL JOIN
5.交叉連接-CROSS JOINFULL JOIN 返回左表中的所有行，左表中的每一行與右表中的所有行組合；
交叉聯接也稱作笛卡爾積；
如果table1有5行記錄，table2有6行記錄，交叉連接結果為5*6=30條記錄；
//規則1 table1和table2兩表的笛卡爾積 = 兩個表行數的乘積
SELECT * FROM table1 CROSS JOIN table2;
//規則2 首先生成兩表的笛卡爾積，再根據ON條件來過濾
SELECT * FROM table1 t1 CROSS JOIN table2 ON t1.column_name=t2.column_name;
//規則3 首先生成兩表的笛卡爾積，再根據WHERE條件來過濾
SELECT * FROM table1 t1 CROSS JOIN table2 WHERE t1.column_name=t2.column_name;
//案例
SELECT * FROM user CROSS JOIN role;
SELECT * FROM user t1 CROSS JOIN role ON t1.id=t2.uid;
SELECT * FROM user t1 CROSS JOIN role WHERE t1.id=t2.uid;
SQL JOIN - CROSS JOIN
6.自然連接-NATURAL JOINNATURAL JOIN 等同于INNER JOIN或INNER USING 匹配兩個表中相同名稱的列；
具有相同名稱的關聯表的列将僅出現一次;
關聯的表具有一對或多對相同名稱的列;這些列必須是相同的數據類型;
不要在自然連接中使用ON子句;
//規則
SELECT * FROM table1 NATURAL JOIN table2;
//案例
SELECT * FROM table1 NATURAL JOIN table2;
SELF JOIN 是表與自身聯接（一元關系）的連接，特别是當表具有引用其自身主鍵的外鍵時；
連接表本身意味着表的每一行都與自身以及表的其他每一行結合在一起；
自連接可以看作是同一表的兩個副本的連接；
//規則
SELECT * FROM table t1, table t2 WHERE t1.column_name=t2.column_name;
SELECT * FROM table t1 INNER JOIN table t2 ON t1.column_name=t2.column_name;
//案例
SELECT * FROM resource t1, resource t2 WHERE t1.id=t2.pid;
SELECT * FROM resource t1 INNER JOIN resource t2 ON t1.id=t2.pid;
UNION 操作符用于合并兩個或多個 SELECT 語句的結果集;
UNION 内部的每個 SELECT 語句必須擁有相同數量的列,相似的數據類型，相同的列順序；
//規則1 UNION 操作符默認地選取不同的值
SELECT column_list FROM table1 UNION SELECT column_list FROM table2;
//規則2 UNION ALL允許選取重複的值
SELECT column_list FROM table1 UNION ALL SELECT column_list FROM table2;
//案例
select id,name,pass,age from user1 union select id,name,pass,age from user2;
select id,name,pass,age from user1 union all select id,name,pass,age from user2;
至此MySQL數據庫的多表查詢操作常用SQL命令總結完畢，編程就這麼簡單，小夥伴們不妨試試，總結不易，希望大家多點贊收藏轉發，在此謝謝！
,更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!
zpostcode 
Recruit 
weather 
mreligion 
Yellowpages 
sport 
constellation 
shopping 
name 
game 
directory 
literature 
Word 
tour 
furnish 
Lottery 
tftnews 
lyrics 
News 
digital 
car 
dir 
Edu 
Finance 
