# SQL 連接 > 原文：[https://www.bookbookmark.ds100.org/ch/09/sql_joins.html](https://www.bookbookmark.ds100.org/ch/09/sql_joins.html) ``` # HIDDEN # Clear previously defined variables %reset -f # Set directory for data loading to work properly import os os.chdir(os.path.expanduser('~/notebooks/09')) ``` ``` # HIDDEN import warnings # Ignore numpy dtype warnings. These warnings are caused by an interaction # between numpy and Cython and can be safely ignored. # Reference: https://stackoverflow.com/a/40846742 warnings.filterwarnings("ignore", message="numpy.dtype size changed") warnings.filterwarnings("ignore", message="numpy.ufunc size changed") import numpy as np import matplotlib.pyplot as plt import pandas as pd import seaborn as sns %matplotlib inline import ipywidgets as widgets from ipywidgets import interact, interactive, fixed, interact_manual import nbinteract as nbi sns.set() sns.set_context('talk') np.set_printoptions(threshold=20, precision=2, suppress=True) pd.options.display.max_rows = 7 pd.options.display.max_columns = 8 pd.set_option('precision', 2) # This option stops scientific notation for pandas # pd.set_option('display.float_format', '{:.2f}'.format) ``` ``` # HIDDEN # Make names table sql_expr = """ CREATE TABLE names( cat_id INTEGER PRIMARY KEY, name TEXT); """ result = sqlite_engine.execute(sql_expr) # Populate names table sql_expr = """ INSERT INTO names VALUES (0, "Apricot"), (1, "Boots"), (2, "Cally"), (4, "Eugene"); """ result = sqlite_engine.execute(sql_expr) ``` ``` # HIDDEN # Make colors table sql_expr = """ CREATE TABLE colors( cat_id INTEGER PRIMARY KEY, color TEXT); """ result = sqlite_engine.execute(sql_expr) # Populate colors table sql_expr = """ INSERT INTO colors VALUES (0, "orange"), (1, "black"), (2, "calico"), (3, "white"); """ result = sqlite_engine.execute(sql_expr) ``` ``` # HIDDEN # Make ages table sql_expr = """ CREATE TABLE ages( cat_id INTEGER PRIMARY KEY, age INT); """ result = sqlite_engine.execute(sql_expr) # Populate ages table sql_expr = """ INSERT INTO ages VALUES (0, 4), (1, 3), (2, 9), (4, 20); """ result = sqlite_engine.execute(sql_expr) ``` 在`pandas`中，我們使用`pd.merge`方法在兩個表的列中使用匹配的值連接兩個表。例如： ``` pd.merge(table1, table2, on='common_column') ``` 在本節中，我們將介紹 SQL 連接。SQL 連接用于組合關系數據庫中的多個表。 假設我們是貓店的老板，有一個我們店里貓的數據庫。我們有**兩個**不同的表：`names`和`colors`。`names`表包含列`cat_id`、分配給每個 cat 的唯一編號和 cat 的名稱`name`。`colors`表包含列`cat_id`和`color`，每只貓的顏色。 請注意，兩個表中都有一些缺少的行-`names`表中缺少一行帶有`cat_id`3，而`colors`表中缺少一行帶有`cat_id`4。 <header style="text-align:center">**names**</header> | 貓科動物 | 名稱 | | --- | --- | | 零 | 杏 | | 1 個 | 靴子 | | 二 | 凱利 | | 四 | 尤金 | <header style="text-align:center">**colors**</header> | cat_id | 顏色 | | --- | --- | | 0 | 橙色 | | 1 | 黑色 | | 2 | 印花布 | | 三 | 白色 | 要計算名為杏的貓的顏色，我們必須在兩個表中使用信息。我們可以將 _ 連接到 _ 列上的表，用`name`和`color`創建一個新表。 ## 連接[?](#Joins) 連接通過在表的列中匹配值來組合表。 連接有四種主要類型：內部連接、外部連接、左側連接和右側連接。盡管這四個表都是組合表，但每個表對不匹配的值的處理方式不同。 ### 內部連接 **定義：**在內部連接中，最終表只包含在**兩個**表中具有匹配列的行。  **示例：**我們希望將`names`和`colors`表連接在一起，以匹配每只貓的顏色。因為這兩個表都包含一個`cat_id`列，該列是 cat 的唯一標識符，所以我們可以在`cat_id`列上使用內部連接。 **sql:**要在 sql 中編寫內部連接，我們修改了`FROM`子句以使用以下語法： ``` SELECT ... FROM <TABLE_1> INNER JOIN <TABLE_2> ON <...> ``` 例如： ``` SELECT * FROM names AS N INNER JOIN colors AS C ON N.cat_id = C.cat_id; ``` | | cat_id | name | cat_id | color | | --- | --- | --- | --- | --- | | 零 | 0 | Apricot | 0 | orange | | --- | --- | --- | --- | --- | | 1 個 | 1 | Boots | 1 | black | | --- | --- | --- | --- | --- | | 二 | 2 | Cally | 2 | calico | | --- | --- | --- | --- | --- | 您可以驗證每個 cat 名稱是否與其顏色匹配。請注意，結果表中不存在帶有`cat_id`3 和 4 的 cats，因為`colors`表沒有帶有`cat_id`4 的行，而`names`表沒有帶有`cat_id`3 的行。在內部連接中，如果一行在另一個表中沒有匹配的值，則該行不包括在最終結果中。 假設我們有一個名為`names`的數據幀和一個名為`colors`的數據幀，我們可以通過編寫以下內容在`pandas`中執行內部連接： ``` pd.merge(names, colors, how='inner', on='cat_id') ``` ### 全/外接 **定義：**在完全連接（有時稱為外部連接）中，**兩個表**中的所有記錄都包含在連接表中。如果一行在另一個表中沒有匹配項，則用`NULL`填充缺少的值。  **示例：**和前面一樣，我們將`names`和`colors`表連接在一起，以匹配每只貓的顏色。這一次，我們希望將所有行保留在兩個表中，即使沒有匹配的行。 **sql:**要在 sql 中編寫外部連接，我們修改了`FROM`子句，以使用以下語法： ``` SELECT ... FROM <TABLE_1> FULL JOIN <TABLE_2> ON <...> ``` For example: ``` SELECT name, color FROM names N FULL JOIN colors C ON N.cat_id = C.cat_id; ``` | cat_id | name | color | | --- | --- | --- | | 0 | Apricot | orange | | 1 | Boots | black | | 2 | Cally | calico | | 3 | 無效的 | white | | 4 | Eugene | NULL | 請注意，最終輸出包含帶有`cat_id`3 和 4 的條目。如果一行沒有匹配項，它仍然包含在最終輸出中，并且任何缺少的值都用`NULL`填充。 在`pandas`中： ``` pd.merge(names, colors, how='outer', on='cat_id') ``` ### 左連接[?](#Left-Join) **定義：**在左連接中，來自**左表**的所有記錄都包含在連接表中。如果行在右表中沒有匹配項，則缺少的值將用`NULL`填充。  **示例：**和前面一樣，我們將`names`和`colors`表連接在一起，以匹配每只貓的顏色。這次，我們要保留所有的貓名，即使一只貓沒有匹配的顏色。 **sql:**要在 sql 中編寫左連接，我們修改了`FROM`子句以使用以下語法： ``` SELECT ... FROM <TABLE_1> LEFT JOIN <TABLE_2> ON <...> ``` For example: ``` SELECT name, color FROM names N LEFT JOIN colors C ON N.cat_id = C.cat_id; ``` | cat_id | name | color | | --- | --- | --- | | 0 | Apricot | orange | | 1 | Boots | black | | 2 | Cally | calico | | 4 | Eugene | NULL | 請注意，最終輸出包括所有四個 cat 名稱。`names`關系中的三個`cat_id`s 在`colors`表中與`cat_id`s 匹配，一個不匹配（eugene）。沒有匹配顏色的貓名的顏色為`NULL`。 In `pandas`: ``` pd.merge(names, colors, how='left', on='cat_id') ``` ### 右連接[?](#Right-Join) **定義：**在右連接中，來自**右表**的所有記錄都包含在連接表中。如果左表中的行不匹配，則用`NULL`填充缺少的值。  **示例：**和前面一樣，我們將`names`和`colors`表連接在一起，以匹配每只貓的顏色。這一次，我們要保留所有的貓的顏色，即使一只貓沒有匹配的名字。 **sql:**要在 sql 中編寫正確的 join，我們修改了`FROM`子句以使用以下語法： ``` SELECT ... FROM <TABLE_1> RIGHT JOIN <TABLE_2> ON <...> ``` For example: ``` SELECT name, color FROM names N RIGHT JOIN colors C ON N.cat_id = C.cat_id; ``` | cat_id | name | color | | --- | --- | --- | | 0 | Apricot | orange | | 1 | Boots | black | | 2 | Cally | calico | | 3 | NULL | white | 這一次，觀察最終輸出包括所有四種 CAT 顏色。`colors`關系中的三個`cat_id`s 與`names`表中的`cat_id`s 匹配，一個不匹配（白色）。沒有匹配名稱的 cat 顏色的名稱為`NULL`。 您還可能注意到，右連接產生的結果與交換表順序的左連接相同。即，`names`左接`colors`與`colors`右接`names`相同。因此，一些 SQL 引擎（如 sqlite）不支持右連接。 In `pandas`: ``` pd.merge(names, colors, how='right', on='cat_id') ``` ### 隱式內部連接[?](#Implicit-Inner-Joins) 在 SQL 中通常有多種方法來完成同一個任務，就像在 Python 中有多種方法來完成同一個任務一樣。我們指出了另一種編寫內部連接的方法，這種方法在實踐中出現，稱為 _ 隱式連接 _。回想一下，我們之前編寫了以下內容來進行內部連接： ``` SELECT * FROM names AS N INNER JOIN colors AS C ON N.cat_id = C.cat_id; ``` 隱式內部連接的語法稍有不同。請特別注意，`FROM`子句使用逗號從兩個表中進行選擇，并且查詢包含一個`WHERE`子句來指定連接條件。 ``` SELECT * FROM names AS N, colors AS C WHERE N.cat_id = C.cat_id; ``` 當在`FROM`子句中指定多個表時，SQL 將創建一個表，其中包含每個表中的每一行組合。例如： ``` sql_expr = """ SELECT * FROM names N, colors C """ pd.read_sql(sql_expr, sqlite_engine) ``` | | cat_id | name | cat_id | color | | --- | --- | --- | --- | --- | | 0 | 0 | Apricot | 0 | orange | | --- | --- | --- | --- | --- | | 1 | 0 | Apricot | 1 | black | | --- | --- | --- | --- | --- | | 2 | 0 | Apricot | 2 | calico | | --- | --- | --- | --- | --- | | 三 | 0 | Apricot | 3 | white | | --- | --- | --- | --- | --- | | 四 | 1 | Boots | 0 | orange | | --- | --- | --- | --- | --- | | 5 個 | 1 | Boots | 1 | black | | --- | --- | --- | --- | --- | | 六 | 1 | Boots | 2 | calico | | --- | --- | --- | --- | --- | | 七 | 1 | Boots | 3 | white | | --- | --- | --- | --- | --- | | 8 個 | 2 | Cally | 0 | orange | | --- | --- | --- | --- | --- | | 九 | 2 | Cally | 1 | black | | --- | --- | --- | --- | --- | | 10 個 | 2 | Cally | 2 | calico | | --- | --- | --- | --- | --- | | 11 個 | 2 | Cally | 3 | white | | --- | --- | --- | --- | --- | | 12 個 | 4 | Eugene | 0 | orange | | --- | --- | --- | --- | --- | | 十三 | 4 | Eugene | 1 | black | | --- | --- | --- | --- | --- | | 十四 | 4 | Eugene | 2 | calico | | --- | --- | --- | --- | --- | | 15 個 | 4 | Eugene | 3 | white | | --- | --- | --- | --- | --- | 此操作通常稱為 _ 笛卡爾積 _：第一個表中的每一行都與第二個表中的每一行成對出現。請注意，許多行包含的 cat 顏色與它們的名稱不匹配。隱式連接中的附加`WHERE`子句篩選出沒有匹配`cat_id`值的行。 ``` SELECT * FROM names AS N, colors AS C WHERE N.cat_id = C.cat_id; ``` | | cat_id | name | cat_id | color | | --- | --- | --- | --- | --- | | 0 | 0 | Apricot | 0 | orange | | --- | --- | --- | --- | --- | | 1 | 1 | Boots | 1 | black | | --- | --- | --- | --- | --- | | 2 | 2 | Cally | 2 | calico | | --- | --- | --- | --- | --- | ## 連接多個表[?](#Joining-Multiple-Tables) 若要連接多個表，請使用附加的`JOIN`運算符擴展`FROM`子句。例如，下表`ages`包括每只貓的年齡數據。 | cat_id | 年齡 | | --- | --- | | 0 | 4 | | 1 | 3 | | 2 | 九 | | 4 | 20 個 | 要在`names`、`colors`和`ages`表上執行內部連接，我們編寫： ``` # Joining three tables sql_expr = """ SELECT name, color, age FROM names n INNER JOIN colors c ON n.cat_id = c.cat_id INNER JOIN ages a ON n.cat_id = a.cat_id; """ pd.read_sql(sql_expr, sqlite_engine) ``` | | name | color | age | | --- | --- | --- | --- | | 0 | Apricot | orange | 4 | | --- | --- | --- | --- | | 1 | Boots | black | 3 | | --- | --- | --- | --- | | 2 | Cally | calico | 9 | | --- | --- | --- | --- | ## 摘要[?](#Summary) 我們已經介紹了四種主要的 SQL 連接類型：內部連接、完整連接、左連接和右連接。我們使用所有四個連接將信息組合在單獨的關系中，并且每個連接只在處理輸入表中不匹配行的方式上有所不同。