當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

SQLite | Join 语句

發布時間：2025/3/15 数据库 24 豆豆

生活随笔收集整理的這篇文章主要介紹了 SQLite | Join 语句小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

文章目錄

1. Join
- 1.1 表聯合
- 1.2 內聯合
- 1.3 左聯合
- 1.4 其他聯合類型
- 1.5 多表聯合
- 1.6 分組聯合
參考資料

1. Join

我們在上一篇中介紹了 Case 子句
，接下來我們將使用 join ，對表格進行合并。

使用Jupyter Notebook 運行 SQL 語句需安裝 ipython-sql
%sql 以及 %%sql 為在 Notebook 中運行 SQL 語句，在 SQLite 命令行或 SQLite Stiduo 中不需要 %sql 或 %%sql

載入 SQL 以及連接 SQLite：

%load_ext sql %sql sqlite:///DataBase/rexon_metals.db 'Connected: @DataBase/rexon_metals.db'

本文將使用 rexon_metals.db 數據庫，其中包含了 CUSTOMER、CUSTOMER_ORDER 和 PRODUCT 三張表。

1.1 表聯合

Joining 是 SQL 內置的一個子句，但卻和其他的子句不同。當我們討論一個關系數據庫時，
里面通常有著幾張表，表與表之間通過某一列數據相聯系。舉個例子，在這樣 CUSTOMER_ORDER
表中，有這 CUSTOMER_ID 這一列：

%%sql select * from customer_order limit 0,5; * sqlite:///DataBase/rexon_metals.db Done. ORDER_IDORDER_DATESHIP_DATECUSTOMER_IDPRODUCT_IDORDER_QTYSHIPPED

1	2015-05-15	2015-05-18	1	1	450	false
2	2015-05-18	2015-05-21	3	2	600	false
3	2015-05-20	2015-05-23	3	5	300	false
4	2015-05-18	2015-05-22	5	4	375	false
5	2015-05-17	2015-05-20	3	2	500	false

這一列是我們和 CUSTOMER 表聯系起來的鍵（key），因此你也能猜出，在
CUSTOMER 表中，也有 CUSTOMER_ID 這一列數據：

%%sql select * from customer limit 0,5; * sqlite:///DataBase/rexon_metals.db Done. CUSTOMER_IDNAMEREGIONSTREET_ADDRESSCITYSTATEZIP

1	LITE Industrial	Southwest	729 Ravine Way	Irving	TX	75014
2	Rex Tooling Inc	Southwest	6129 Collie Blvd	Dallas	TX	75201
3	Re-Barre Construction	Southwest	9043 Windy Dr	Irving	TX	75032
4	Prairie Construction	Southwest	264 Long Rd	Moore	OK	62104
5	Marsh Lane Metal Works	Southeast	9143 Marsh Ln	Avondale	LA	79782

我們可以按順序從這一個表中讀取客戶的信息，這與 Excel 中的 VLOOKUP 非常相似。

下面是 CUSTOMER_ORDER 表與 CUSTOMER 表之間關系的示意圖，我們可以說 CUSTOMER 表是 CUSTOMER_ORDER 表的父表，因為 CUSTOMER_ORDER 表依賴于 CUSOMER 表中的信息，因此它是一個子表。相反的，CUSTOMER 不可能是 CUSTOMER_ORDER 的子表，因為它沒有什么信息是依賴于
CUSTOMER_ORDER 表的。

圖1 表 CUSTOMER 與表 CUSTOMER_ORDER 關系圖

箭頭顯示了 CUSTOMER 表同過 CUSTOMER_ID 這一列數據與 CUSTOMER_ORDER 表聯系。

我們也可以從另一個方面來考慮兩個表之間的關系，那就是對于兩個表相同的列，子表中的數據通常是重復的，而父表中的數據通常是唯一的。還是以這兩張表為例，在下面這張圖中你可以看到
一對多的關系：一位 CUSTOMER_ID 為 3 的 Re-Barre Construction 的客戶對應著三張訂單。

圖2 表 CUSTOMER 與表 CUSTOMER_ORDER 關系圖2

一對多是最常見的數據關系，因為這符合大多數商業需要，如一個客戶對應著多張訂單。最不常見的關系就是一對一和多對多的關系。

1.2 內聯合

理解了表的關系，我們來考慮下將兩張表聯合到一起，這樣我們可以在同一張表內即看到 CUSTOMER 的數據，又看到 CUSTOMER_ORDER 的數據。

INNER JOIN 讓你能夠將兩張表聯合到一起，但如果我們要聯合表，就需要定義一個或多個兩張表都有的列作為鍵。如果我們要查詢 CUSTOMER_ORDER 并用 CUSTOMER 表來補充客戶的信息，我們可以將共同的 CUSTOMER_ID 作為鍵：

%%sql SELECT order_id, -- CUSTOMER_ORDER customer.customer_id, -- CUSTOMER order_date, -- CUSTOMER_ORDER ship_date, -- CUSTOMER_ORDER name, -- CUSTOMER street_address, -- CUSTOMER city, -- CUSTOMER state, -- CUSTOMER zip, -- CUSTOMER product_id, -- CUSTOMER_ORDER order_qty -- CUSTOMER_ORDERFROM customer INNER JOIN customer_order ON customer.customer_id = customer_order.customer_id LIMIT 0,5 * sqlite:///DataBase/rexon_metals.db Done. ORDER_IDCUSTOMER_IDORDER_DATESHIP_DATENAMESTREET_ADDRESSCITYSTATEZIPPRODUCT_IDORDER_QTY

1	1	2015-05-15	2015-05-18	LITE Industrial	729 Ravine Way	Irving	TX	75014	1	450
2	3	2015-05-18	2015-05-21	Re-Barre Construction	9043 Windy Dr	Irving	TX	75032	2	600
3	3	2015-05-20	2015-05-23	Re-Barre Construction	9043 Windy Dr	Irving	TX	75032	5	300
4	5	2015-05-18	2015-05-22	Marsh Lane Metal Works	9143 Marsh Ln	Avondale	LA	79782	4	375
5	3	2015-05-17	2015-05-20	Re-Barre Construction	9043 Windy Dr	Irving	TX	75032	2	500

你可能注意到了我們可以同時從 CUSTOMER 和 CUSTOMER_ORDER 兩張表中提取列，并將它們合并。

讓我們看看剛才的查詢是怎么完成的，首先我們從兩張表中提取出我們想要的數據：

SELECT order_id, -- CUSTOMER_ORDER customer.customer_id, -- CUSTOMER order_date, -- CUSTOMER_ORDER ship_date, -- CUSTOMER_ORDER name, -- CUSTOMER street_address, -- CUSTOMER city, -- CUSTOMER state, -- CUSTOMER zip, -- CUSTOMER product_id, -- CUSTOMER_ORDER order_qty -- CUSTOMER_ORDER

這樣我們可以在每一個訂單中看見客戶的地址信息。同時也要注意到：由于兩張表都具有 ORDER_ID ，所以需要指定使用某一章表的數據。在這個例子中，我們選擇了 COSTOMER 表中的 CUSTOMER_ID 數據：

customer.customer_id

最后我們用 select … inner join … 語句臨時將兩張表合并為一張表。指定了從 CUSTOMER 表中提取數據并加入到 CUSTOMER_ORDER 表中，而它們共同的列就是 CUSTOMER_ID：

FROM customer INNER JOIN customer_order ON customer.customer_id = customer_order.customer_id

正因為我們是將根據 CUSTOMER_ORDER 上的 CUSTOMER_ID 添加 CUSTOMER 的信息，因此并不是CUSTOMER 上所有的信息都會出現在 CUSTOMER_ORDER 上，以下圖為例，當我們合并兩張表時，合并的結果中只出現了三個顧客的名字，而由于 CUSTOMER_ORDER 中并沒有 Rex Tooling Inc 和 Prairie Construction，因此合并表中也就不會出現這兩家公司了。

圖3 inner join 示意圖

1.3 左聯合

考慮下我們剛才說到的問題，在內聯合中，Rex Tooling Inc 和 Prairie Construction 兩家公司由于沒有訂單而沒有呈現在最后的合并結果中。但如果我就是想要在合并結果中保留它們呢（盡管沒有數據）？

如果你對我們剛才所介紹 inner join 能接受的話，那么 left 或 out join 也就依葫蘆畫瓢了。你可以將前面代碼中的 inner 換成 left ，你會發現左表（CUSTOMER）中所有信息都被保留下來了：

%%sql SELECT order_id, -- CUSTOMER_ORDER customer.customer_id, -- CUSTOMER order_date, -- CUSTOMER_ORDER ship_date, -- CUSTOMER_ORDER name, -- CUSTOMER street_address, -- CUSTOMER city, -- CUSTOMER state, -- CUSTOMER zip, -- CUSTOMER product_id, -- CUSTOMER_ORDER order_qty -- CUSTOMER_ORDERFROM customer LEFT JOIN customer_order ON customer.customer_id = customer_order.customer_id LIMIT 0,5 * sqlite:///DataBase/rexon_metals.db Done. ORDER_IDCUSTOMER_IDORDER_DATESHIP_DATENAMESTREET_ADDRESSCITYSTATEZIPPRODUCT_IDORDER_QTY

1	1	2015-05-15	2015-05-18	LITE Industrial	729 Ravine Way	Irving	TX	75014	1	450
None	2	None	None	Rex Tooling Inc	6129 Collie Blvd	Dallas	TX	75201	None	None
5	3	2015-05-17	2015-05-20	Re-Barre Construction	9043 Windy Dr	Irving	TX	75032	2	500
2	3	2015-05-18	2015-05-21	Re-Barre Construction	9043 Windy Dr	Irving	TX	75032	2	600
3	3	2015-05-20	2015-05-23	Re-Barre Construction	9043 Windy Dr	Irving	TX	75032	5	300

可以看到雖然 Rex Tooling Inc 并沒有訂單數據，但會以缺失值的顯示出現在結果中，如下圖過程所示：

圖4 left join 示意圖

left join 也經常被用于檢查是否有 “孤兒（orphaned）”數據，即子數據無父數據，或相反的父數據無子數據。如用于查找是否有客戶沒有訂單的或訂單丟失客戶的：

%%sql SELECT customer.customer_id, name AS customer_nameFROM customer LEFT JOIN customer_order ON customer.customer_id == customer_order.customer_idWHERE order_id IS null * sqlite:///DataBase/rexon_metals.db Done. CUSTOMER_IDcustomer_name

2	Rex Tooling Inc
4	Prairie Construction

1.4 其他聯合類型

我們已經介紹了內聯合于左聯合，SQL 中還包含了其他聯合方法，如 right join、out join 等方法。

圖5 SQL 表聯合方法

right join 與 left join 幾乎相同，不過是換了個方向：將右表的所有數據都包含了進來。然而 right join 很少使用，而且你也得避免使用它。要習慣于將保留所有數據的表作為左表！

SQLite 并不支持 right join 和 outer join，但大多數的數據庫都支持

1.5 多表聯合

由于表與表之間存在著關系，因此關系數據庫可以變得異常的復雜。一張子表可能存在著多張父表，同時是其他表的子表（貴圈也挺亂的hhhh），所以它們之間是如何協作的呢？

%%sql select * from product limit 0,5 * sqlite:///DataBase/rexon_metals.db Done. PRODUCT_IDDESCRIPTIONPRICE

1	Copper	7.51
2	Aluminum	2.58
3	Silver	15
4	Steel	12.31
5	Bronze	4

我們已經探索了 CUSTOMER 和 CUSTOMER_ID 兩張表了，但在這個數據庫中還有一張 PRODUCT 表我們沒用到。在 CUSTOMER_ORDER 和 PRODUCT 兩張表中都存在著 PRODUCT_ID 這一列數據，因此我們不僅可以為 CUSTOMER_ORDER 表添加顧客信息，還能添加產品信息。

圖6 多表聯合

我們可以利用這兩個關系將顧客信息和產品信息同時添加到訂單中：

%%sql SELECT order_id, -- CUSTOMER_ORDER customer.customer_id, -- CUSTOMER name AS customer_name, -- CUSTOMER street_address, -- CUSTOMER city, -- CUSTOMER state, -- CUSTOMER zip, -- CUSTOMER order_date, -- CUSTOMER_ORDER product.product_id, -- PRODUCT description, -- PRODUCT order_qty, -- CUSTOMER_ORDER order_qty * price as revenue -- CUSTOMER_ORDER, PRODUCTFROM customer LEFT JOIN customer_order ON customer.customer_id = customer_order.customer_idLEFT JOIN product ON customer_order.product_id = product.product_idLIMIT 0,5 * sqlite:///DataBase/rexon_metals.db Done. ORDER_IDCUSTOMER_IDcustomer_nameSTREET_ADDRESSCITYSTATEZIPORDER_DATEPRODUCT_IDDESCRIPTIONORDER_QTYrevenue

1	1	LITE Industrial	729 Ravine Way	Irving	TX	75014	2015-05-15	1	Copper	450	3379.5
None	2	Rex Tooling Inc	6129 Collie Blvd	Dallas	TX	75201	None	None	None	None	None
5	3	Re-Barre Construction	9043 Windy Dr	Irving	TX	75032	2015-05-17	2	Aluminum	500	1290.0
2	3	Re-Barre Construction	9043 Windy Dr	Irving	TX	75032	2015-05-18	2	Aluminum	600	1548.0
3	3	Re-Barre Construction	9043 Windy Dr	Irving	TX	75032	2015-05-20	5	Bronze	300	1200

1.6 分組聯合

還是以上面的查詢為例，加入我們想要計算每一個顧客的總收入，我們可以在之前查詢的基礎上使用 group by 子句。為了簡便，我們省去了其他的列：

%%sql select customer.customer_id, name as customer_name, sum(order_qty * price) as total_revenuefrom customer inner join customer_order on customer.customer_id = customer_order.customer_idinner join product on customer_order.product_id = product.product_idgroup by 1,2 * sqlite:///DataBase/rexon_metals.db Done. CUSTOMER_IDcustomer_nametotal_revenue

1	LITE Industrial	3379.5
3	Re-Barre Construction	4038.0
5	Marsh Lane Metal Works	4616.25

注意到上面的結果中并沒有 Rex Tooling 和 Prairie Construction 兩家公司的數據，這是因為它們并沒有訂單。如果你想要保留著部分缺失值，可以使用 left join：

%%sql select customer.customer_id, name as customer_name, sum(order_qty * price) as total_revenuefrom customer left join customer_order on customer.customer_id = customer_order.customer_idleft join product on customer_order.product_id = product.product_idgroup by 1,2 * sqlite:///DataBase/rexon_metals.db Done. CUSTOMER_IDcustomer_nametotal_revenue

1	LITE Industrial	3379.5
2	Rex Tooling Inc	None
3	Re-Barre Construction	4038.0
4	Prairie Construction	None
5	Marsh Lane Metal Works	4616.25

我們對兩次聯合都使用了 LEFT JOIN 而不是 LEFT JOIN 與 INNER JOIN 混合使用，這是因為 INNER JOIN 會過濾缺失值，而 LEFT JOIN 可以保留缺失值。當我們先使用 LEFT JOIN 并產生缺失值時，如果我們再使用 INNER JOIN，會導致部分缺失值丟失

我們在之前的文章中提過，sum 函數會對非缺失值的數據進行加總，因此如果你想要在查詢結果中保留該公司的總收入為 0 的話，可以使用 coalesce 函數將缺失值替換為 0：

%%sql select customer.customer_id, name as customer_name, coalesce(sum(order_qty * price), 0) as total_revenuefrom customer left join customer_order on customer.customer_id = customer_order.customer_idleft join product on customer_order.product_id = product.product_idgroup by 1,2 * sqlite:///DataBase/rexon_metals.db Done. CUSTOMER_IDcustomer_nametotal_revenue

1	LITE Industrial	3379.5
2	Rex Tooling Inc	0
3	Re-Barre Construction	4038.0
4	Prairie Construction	0
5	Marsh Lane Metal Works	4616.25

參考資料

[1] Thomas Nield.Getting Started with SQL[M].US: O’Reilly, 2016: 53-66

相關文章：

總結

以上是生活随笔為你收集整理的SQLite | Join 语句的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇：重磅！『2021科技研究前沿』发布，重点
下一篇： Linux配置启动挂载：fstab文件详