Sql在多張表中檢索資料的方法詳解

2023-09-07 10:08:42

1.內連線

各表分開存放是為了減少重複資訊和方便修改，需要時可以根據相互之間的關係連線成相應的合併詳情表以滿足相應的查詢。FROM JOIN ON 語句就是告訴sql：將哪幾張表以什麼基礎連線/合併起來。

select *
from order 
inner join customers
	on	order.id = customers.id;
-- inner可省略
-- 可起別名
select 
	order_id,
	customer_id,
	o.id,
	last_name
from order o
join customers c
	on	o.id = c.id;

2.跨資料庫連線

有時需要選取不同庫的表的列，其他都一樣，就只是WHERE JOIN裡對於非現在正在用的庫的表要加上庫名字首而已。依然可用別名來簡化

use sql_store;
select * from order_items oi
join sql_inventory.products p
    on oi.product_id = p.product_id
-- 或
use sql_inventory;
select * from sql_store.order_items oi
join products p
    on oi.product_id = p.product_id
-- 非當前使用的庫，才需要加庫字首

3.自連線

一個表和它自己合併。如員工的上級也是員工，所以也在員工表裡，所以想得到的有員工和他的上級資訊的合併表，就要員工表自己和自己合併，用兩個不同的表別名即可實現。這個例子中只有兩級，但也可用類似的方法構建多層級的組織結構。

USE sql_hr
select 
	e.employee_id,
	e.first_name,
	m.first_name as manger
from
	employees e
join employee m
	on e.reports_to = m.employee_id

4.多表連線

FROM 一個核心表A，用多個 JOIN …… ON …… 分別通過不同的連結關係連結不同的表B、C、D……，通常是讓表B、C、D……為表A提供更詳細的資訊從而合併為一張詳情合併版A表，即：

FROM A
JOIN B ON AB的關係
JOIN C ON AC的關係
JOIN D ON AD的關係
……

將得到一個合併了BCD……等表詳細資訊的詳情合併版A表

真實工作場景中有時甚至要合併十多張表

-- 訂單表同時連結顧客表和訂單狀態表，合併為有顧客和狀態資訊的詳細訂單表
USE sql_store;
SELECT 
	o.order_id,
	o.order_date,
	c.first_name,
	c.last_name,
	os.name AS status
FROM	order o
JOIN	customers c
	on	o.customers_id = c.customers_id
JOIN	order_statuses os
    ON o.status = os.order_status_id;

5.複合連線條件

像訂單專案（order_items）這種表，訂單id和產品id合在一起才能唯一表示一條記錄，這叫複合主鍵，設計模式下也可以看到兩個欄位都有PK標識，訂單專案備註表（order_item_notes）也是這兩個複合主鍵，因此他們兩合併時要用複合條件：FROM 表1 JOIN 表2 ON 條件1 【AND】 條件2

USE sql_store
SELECT *
FROM order_items oi
	JOIN order_item_notes oin
		ON oi.order_Id = oin.order_Id
    	AND oi.product_id = oin.product_id

6.隱式連線語法

就是用FROM WHERE取代FROM JOIN ON

儘量別用，因為若忘記WHERE條件篩選語句，不會報錯但會得到交叉合併（cross join）結果：即10條order會分別與10個customer結合，得到100條記錄。最好使用顯性合併語法，因為會強制要求你寫合併條件ON語句，不至於漏掉。

USE sql_store;
SELECT *
FROM orders o
JOIN customers c
	on o.id = c.id;
-- 可轉換為下面這個
SELECT *
FROM orders

7.外連線

(INNER) JOIN只包含兩個表的交集

LEFT/RIGHT (OUTER) JOIN 結果裡除了交集，還包含只出現在左/右表中的記錄

-- 合併顧客表和訂單表，用INNER JOIN
USE sql_store;
SELECT 
    c.customer_id,
    c.first_name,
    o.order_id
FROM customers c
JOIN orders o
    ON o.customer_id = c.customer_id
ORDER BY customer_id
-- 這樣是INNER JOIN，只展示有訂單的顧客（及其訂單），也就是兩張表的交集，但注意這裡因為一個顧客可能有多個訂單，所以INNER JOIN以後顧客資訊其實是是廣播了的，即一條顧客資訊被多條訂單記錄共用，當然 這叫廣播（broadcast）效應，是另一個問題，這裡關注的重點是 INNER JOIN 的結果確實是兩表的交集，是那些同時有顧客資訊和訂單資訊的記錄。
-- 若要展示全部顧客（及其訂單，如果有的話），要改用LEFT (OUTER) JOIN，結果相較於 INNER JOIN 多了沒有訂單的那些顧客，即只有顧客資訊沒有訂單資訊的記錄
-- 當然，也可以調換左右表的順序（即調換FROM和JOIN的物件）再 RIGHT JOIN，即：
FROM orders o
    RIGHT [OUTER] JOIN customers c
    -- 中括號 [] 表示是可選項、可省略 
    ON o.customer_id = c.customer_id
-- 若要展示全部訂單（及其顧客），就應該是 orders RIGHT JOIN customers，結果相較於 INNER JOIN 多了沒有顧客的那些訂單，即只有訂單資訊沒有顧客資訊的記錄。（注：因為這裡所有訂單都有顧客，所以這裡 RIGHT JOIN 結果和 INNER JOIN 一樣）

8.多表外連線

與內連線類似，我們可以對多個表（3個及以上）進行外連線，最好只用 JOIN 和 LEFT JOIN

SELECT 
    c.customer_id,
    c.first_name,
    o.order_id,
    sh.name AS shipper
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
LEFT JOIN shippers sh
    ON o.shipper_id = sh.shipper_id
ORDER BY customer_id
-- 雖然可以調換順序並用 RIGHT JOIN，但作為最佳實踐，最好調整順序並統一隻用 [INNER] JOIN 和 LEFT [OUTER] JOIN（總是左表全包含），這樣，當要合併的表比較多時才方便書寫和理解而不易混亂

9.自外部連線

就用前面那個員工表的例子來說，就是用LEFT JOIN讓得到的員工-上級合併表也包括老闆本人（老闆沒有上級，即 reports_to 欄位為空，如果用 JOIN 會被篩掉，用 LEFT JOIN 才能保留）

USE sql_hr;
SELECT 
    e.employee_id,
    e.first_name,
    m.first_name AS manager
FROM employees e
LEFT JOIN employees m  -- 包含所有僱員（包括沒有report_to的老闆本人）
    ON e.reports_to = m.employee_id

10.USING子句

當作為合併條件（join condition）的列在兩個表中有相同的列名時，可用 USING (……, ……) 取代 ON …… AND …… 予以簡化，內/外連線均可如此簡化。

一定注意 USING 後接的是括號，特容易搞忘

SELECT
    o.order_id,
    c.first_name,
    sh.name AS shipper
FROM orders o
JOIN customers c
    USING (customer_id)
LEFT JOIN shippers sh
    USING (shipper_id)
ORDER BY order_id
-- 複合主鍵表間複合連線條件的合併也可用 USING，中間逗號隔開就行：
SELECT *
FROM order_items oi
JOIN order_item_notes oin
ON oi.order_id = oin.order_Id AND
    oi.product_id = oin.product_id
/USING (order_id, product_id)
-- USING對複合主鍵的簡化效果更加明顯
-- 列名不同就必須用 ON …… 了
-- 實際中同一個欄位在不同表列名不同的情況也很常見，不能想當然的用USING

11.自然連線

NATURAL JOIN 就是讓MySQL自動檢索同名列作為合併條件。

最好別用，因為不確定合併條件是否找對了，有時會造成無法預料的問題，程式設計時保持對結果的控制是非常重要的

但也要知道有這個東西，混個臉熟，不要別人用了看不懂。

USE sql_store;
SELECT 
    o.order_id,
    c.first_name
FROM orders o
NATURAL JOIN customers c

12.交叉連線

得到名字和產品的所有組合，因此不需要合併條件。實際運用如：要得到尺寸和顏色的全部組合

-- 得到顧客和產品的全部組合（毫無意義，純粹為了展示交叉連線）
USE sql_store;
SELECT 
    c.first_name AS customer,
    p.name AS product
FROM customers c
CROSS JOIN products p
ORDER BY c.first_name
-- 上面是顯性語法，還有隱式語法，之前講過，其實就是隱式內合併忽略WHERE子句（即合併條件）的情況，也就是把 CROSS JOIN 改為逗號，即 FROM A CROSS JOIN B 等效於 FROM A, B，Mosh更推薦顯式語法，因為更清晰
USE sql_store;
SELECT 
    c.first_name,
    p.name
FROM customers c, products p
ORDER BY c.first_name

13.聯合

FROM …… JOIN …… 可對多張表進行橫向列合併，而 …… UNION …… 可用來按行縱向合併多個查詢結果，這些查詢結果可能來自相同或不同的表

同一張表可通過UNION新增新的分類欄位，即先通過分類查詢並新增新的分類欄位再UNION合併為帶分類欄位的新表。
不同表通過UNION合併的情況如：將一張18年的訂單表和19年的訂單表縱向合併起來在一張表裡展示

注意

合併的查詢結果必須列數相等，否則會報錯
合併表裡的列名由排在 UNION 前面的決定

-- 給訂單表增加一個新欄位——status，用以區分今年的訂單和今年以前的訂單
USE sql_store;
    SELECT 
        order_id,
        order_date,
        'Active' AS status
    FROM orders
    WHERE order_date >= '2019-01-01'
UNION
    SELECT 
        order_id,
        order_date,
        'Archived' AS status  -- Archived 歸檔
    FROM orders
    WHERE order_date < '2019-01-01';

到此這篇關於Sql在多張表中檢索資料的方法詳解的文章就介紹到這了,更多相關Sql檢索資料內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！