# 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 連接類型:內部連接、完整連接、左連接和右連接。我們使用所有四個連接將信息組合在單獨的關系中,并且每個連接只在處理輸入表中不匹配行的方式上有所不同。
- 一、數據科學的生命周期
- 二、數據生成
- 三、處理表格數據
- 四、數據清理
- 五、探索性數據分析
- 六、數據可視化
- Web 技術
- 超文本傳輸協議
- 處理文本
- python 字符串方法
- 正則表達式
- regex 和 python
- 關系數據庫和 SQL
- 關系模型
- SQL
- SQL 連接
- 建模與估計
- 模型
- 損失函數
- 絕對損失和 Huber 損失
- 梯度下降與數值優化
- 使用程序最小化損失
- 梯度下降
- 凸性
- 隨機梯度下降法
- 概率與泛化
- 隨機變量
- 期望和方差
- 風險
- 線性模型
- 預測小費金額
- 用梯度下降擬合線性模型
- 多元線性回歸
- 最小二乘-幾何透視
- 線性回歸案例研究
- 特征工程
- 沃爾瑪數據集
- 預測冰淇淋評級
- 偏方差權衡
- 風險和損失最小化
- 模型偏差和方差
- 交叉驗證
- 正規化
- 正則化直覺
- L2 正則化:嶺回歸
- L1 正則化:LASSO 回歸
- 分類
- 概率回歸
- Logistic 模型
- Logistic 模型的損失函數
- 使用邏輯回歸
- 經驗概率分布的近似
- 擬合 Logistic 模型
- 評估 Logistic 模型
- 多類分類
- 統計推斷
- 假設檢驗和置信區間
- 置換檢驗
- 線性回歸的自舉(真系數的推斷)
- 學生化自舉
- P-HACKING
- 向量空間回顧
- 參考表
- Pandas
- Seaborn
- Matplotlib
- Scikit Learn