mysql-joins具體用法說明
JOIN對於接觸過數據庫的人,這個詞都不陌生,而且很多人很清楚各種JOIN,還有很多人對這個理解也不是很透徹。
假設我們有兩個表,Table_A和Table_B。這兩個表中的數據如下所示:
TABLE_A | TABLE_B PK Value | PK Value ---- ---------- | ---- ---------- 1 FOX | 1 TROT 2 COP | 2 CAR 3 TAXI | 3 CAB 6 WASHINGTON | 6 MONUMENT 7 DELL | 7 PC 5 ARIZONA | 8 MICROSOFT 4 LINCOLN | 9 APPLE 10 LUCENT | 11 SCOTCH
Join 語法:
join_table: table_reference JOIN table_factor [join_condition] //內連接 | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition //外連接 | table_reference LEFT SEMI JOIN table_reference join_condition //左半連接 | table_reference CROSS JOIN table_reference [join_condition] (as of Hive 0.10) table_reference: table_factor //表 | join_table //join語句 table_factor: tbl_name [alias] //表名[別名] | table_subquery alias //子查尋[別名] | ( table_references ) //帶空號的table_reference join_condition: ON expression //on開頭的條件語句
1、Inner JOIN: (內連接)
這是最簡單、最容易理解的連接,也是最常見的連接。此查詢將返回左表(表A)中具有右表(表B)中匹配記錄的所有記錄。此連接寫成如下:
SELECT <select_list> FROM Table_A A INNER JOIN Table_B B ON A.Key = B.Key
-- Inner JOIN SELECT A.PK AS A_PK, A.Value AS A_Value, B.Value AS B_Value, B.PK AS B_PK FROM Table_A A INNER JOIN Table_B B ON A.PK = B.PK A_PK A_Value B_Value B_PK ---- ---------- ---------- ---- 1 FOX TROT 1 2 COP CAR 2 3 TAXI CAB 3 6 WASHINGTON MONUMENT 6 7 DELL PC 7 (5 row(s) affected)
2、Left JOIN: (左連接)
此查詢將返回左表(表A)中的所有記錄,而不管這些記錄是否與右表(表B)中的任何記錄匹配。它還將從正確的表中返回任何匹配的記錄。此連接寫成如下:
SELECT <select_list> FROM Table_A A LEFT JOIN Table_B B ON A.Key = B.Key
-- Left JOIN SELECT A.PK AS A_PK, A.Value AS A_Value, B.Value AS B_Value, B.PK AS B_PK FROM Table_A A LEFT JOIN Table_B B ON A.PK = B.PK A_PK A_Value B_Value B_PK ---- ---------- ---------- ---- 1 FOX TROT 1 2 COP CAR 2 3 TAXI CAB 3 4 LINCOLN NULL NULL 5 ARIZONA NULL NULL 6 WASHINGTON MONUMENT 6 7 DELL PC 7 10 LUCENT NULL NULL (8 row(s) affected)
3、Left Excluding JOIN: (左連接排除內連接結果)
此查詢將返回左表(表A)中與右表(表B)中的任何記錄都不匹配的所有記錄。此連接寫成如下:
SELECT <select_list> FROM Table_A A LEFT JOIN Table_B B ON A.Key = B.Key WHERE B.Key IS NULL
-- Left Excluding JOIN SELECT A.PK AS A_PK, A.Value AS A_Value, B.Value AS B_Value, B.PK AS B_PK FROM Table_A A LEFT JOIN Table_B B ON A.PK = B.PK WHERE B.PK IS NULL A_PK A_Value B_Value B_PK ---- ---------- ---------- ---- 4 LINCOLN NULL NULL 5 ARIZONA NULL NULL 10 LUCENT NULL NULL (3 row(s) affected)
4、Right JOIN: (右連接)
此查詢將返回右表(表B)中的所有記錄,而不管這些記錄中是否有任何記錄與左表(表A)中的記錄相匹配。它還將返回左表中的任何匹配記錄。此連接寫成如下:
SELECT <select_list> FROM Table_A A RIGHT JOIN Table_B B ON A.Key = B.Key
-- Right JOIN SELECT A.PK AS A_PK, A.Value AS A_Value, B.Value AS B_Value, B.PK AS B_PK FROM Table_A A RIGHT JOIN Table_B B ON A.PK = B.PK A_PK A_Value B_Value B_PK ---- ---------- ---------- ---- 1 FOX TROT 1 2 COP CAR 2 3 TAXI CAB 3 6 WASHINGTON MONUMENT 6 7 DELL PC 7 NULL NULL MICROSOFT 8 NULL NULL APPLE 9 NULL NULL SCOTCH 11 (8 row(s) affected)
5、Right Excluding JOIN: (右連接排除內連接結果)
此查詢將返回右表(表B)中與左表(表A)中的任何記錄都不匹配的所有記錄。此連接寫成如下:
SELECT <select_list> FROM Table_A A RIGHT JOIN Table_B B ON A.Key = B.Key WHERE A.Key IS NULL
-- Right Excluding JOIN SELECT A.PK AS A_PK, A.Value AS A_Value, B.Value AS B_Value, B.PK AS B_PK FROM Table_A A RIGHT JOIN Table_B B ON A.PK = B.PK WHERE A.PK IS NULL A_PK A_Value B_Value B_PK ---- ---------- ---------- ---- NULL NULL MICROSOFT 8 NULL NULL APPLE 9 NULL NULL SCOTCH 11 (3 row(s) affected)
6、Outer JOIN: (外連接)
此聯接也可以稱為完全外聯接或完全聯接。此查詢將返回兩個表中的所有記錄,連接左表(表A)中與右表(表B)中的記錄相匹配的記錄。此連接寫成如下:
SELECT <select_list> FROM Table_A A FULL OUTER JOIN Table_B B ON A.Key = B.Key
-- Outer JOIN SELECT A.PK AS A_PK, A.Value AS A_Value, B.Value AS B_Value, B.PK AS B_PK FROM Table_A A FULL OUTER JOIN Table_B B ON A.PK = B.PK A_PK A_Value B_Value B_PK ---- ---------- ---------- ---- 1 FOX TROT 1 2 COP CAR 2 3 TAXI CAB 3 6 WASHINGTON MONUMENT 6 7 DELL PC 7 NULL NULL MICROSOFT 8 NULL NULL APPLE 9 NULL NULL SCOTCH 11 5 ARIZONA NULL NULL 4 LINCOLN NULL NULL 10 LUCENT NULL NULL (11 row(s) affected)
7、Outer Excluding JOIN: (外連接排除內連接結果)
此查詢將返回左表(表A)中的所有記錄和右表(表B)中不匹配的所有記錄。我還不需要使用這種類型的聯接,但所有其他類型的聯接我都相當頻繁地使用。此連接寫成如下:
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
-- Outer Excluding JOIN SELECT A.PK AS A_PK, A.Value AS A_Value, B.Value AS B_Value, B.PK AS B_PK FROM Table_A A FULL OUTER JOIN Table_B B ON A.PK = B.PK WHERE A.PK IS NULL OR B.PK IS NULL A_PK A_Value B_Value B_PK ---- ---------- ---------- ---- NULL NULL MICROSOFT 8 NULL NULL APPLE 9 NULL NULL SCOTCH 11 5 ARIZONA NULL NULL 4 LINCOLN NULL NULL 10 LUCENT NULL NULL (6 row(s) affected)
註意,在外部聯接上,首先返回內部連接記錄,然後返回右連接記錄,最後返回左連接記錄(至少,我的Microsoft SQL Server就是這樣做的;當然,這不需要使用任何ORDERBY語句)。您可以訪問維基百科文章以獲得更多信息(但是,條目不是圖形化的)。我還創建瞭一個備忘單,您可以在需要時打印出來。如果您右鍵單擊下面的圖像並選擇“將目標保存為.”,您將下載完整大小的圖像。
到此這篇關於mysql-joins具體用法說明的文章就介紹到這瞭,更多相關mysql-joins用法內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!