目錄

• 1.內(nèi)連接
• 2.跨數(shù)據(jù)庫(kù)連接
• 3.自連接
• 4.多表連接
• 5.復(fù)合連接條件
• 6.隱式連接語(yǔ)法
• 7.外連接
• 8.多表外連接
• 9.自外部連接
• 10.USING子句
• 11.自然連接
• 12.交叉連接
• 13.聯(lián)合

1.內(nèi)連接

各表分開存放是為了減少重復(fù)信息和方便修改，需要時(shí)可以根據(jù)相互之間的關(guān)系連接成相應(yīng)的合并詳情表以滿足相應(yīng)的查詢。FROM JOIN ON 語(yǔ)句就是告訴sql： 將哪幾張表以什么基礎(chǔ)連接/合并起來。

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.跨數(shù)據(jù)庫(kù)連接

有時(shí)需要選取不同庫(kù)的表的列，其他都一樣，就只是WHERE JOIN里對(duì)于非現(xiàn)在正在用的庫(kù)的表要加上庫(kù)名前綴而已。依然可用別名來簡(jiǎn)化

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
-- 非當(dāng)前使用的庫(kù)，才需要加庫(kù)前綴

3.自連接

一個(gè)表和它自己合并。如員工的上級(jí)也是員工，所以也在員工表里，所以想得到的有員工和他的上級(jí)信息的合并表，就要員工表自己和自己合并，用兩個(gè)不同的表別名即可實(shí)現(xiàn)。這個(gè)例子中只有兩級(jí)，但也可用類似的方法構(gòu)建多層級(jí)的組織結(jié)構(gòu)。

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 一個(gè)核心表A，用多個(gè) JOIN …… ON …… 分別通過不同的鏈接關(guān)系鏈接不同的表B、C、D……，通常是讓表B、C、D……為表A提供更詳細(xì)的信息從而合并為一張?jiān)斍楹喜鍭表，即：

FROM  A 
    JOIN B ON AB的關(guān)系 
    JOIN C ON AC的關(guān)系 
    JOIN D ON AD的關(guān)系 
    ……

將得到一個(gè)合并了BCD……等表詳細(xì)信息的詳情合并版A表

真實(shí)工作場(chǎng)景中有時(shí)甚至要合并十多張表

-- 訂單表同時(shí)鏈接顧客表和訂單狀態(tài)表，合并為有顧客和狀態(tài)信息的詳細(xì)訂單表
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.復(fù)合連接條件

像訂單項(xiàng)目（order_items）這種表，訂單id和產(chǎn)品id合在一起才能唯一表示一條記錄，這叫復(fù)合主鍵，設(shè)計(jì)模式下也可以看到兩個(gè)字段都有PK標(biāo)識(shí)，訂單項(xiàng)目備注表（order_item_notes）也是這兩個(gè)復(fù)合主鍵，因此他們兩合并時(shí)要用復(fù)合條件：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.隱式連接語(yǔ)法

就是用FROM WHERE取代FROM JOIN ON

盡量別用，因?yàn)槿敉沇HERE條件篩選語(yǔ)句，不會(huì)報(bào)錯(cuò)但會(huì)得到交叉合并（cross join）結(jié)果：即10條order會(huì)分別與10個(gè)customer結(jié)合，得到100條記錄。最好使用顯性合并語(yǔ)法，因?yàn)闀?huì)強(qiáng)制要求你寫合并條件ON語(yǔ)句，不至于漏掉。

USE sql_store;
SELECT *
FROM orders o
JOIN customers c
	on o.id = c.id;
-- 可轉(zhuǎn)換為下面這個(gè)
SELECT *
FROM orders 

7.外連接

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

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

-- 合并顧客表和訂單表，用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，只展示有訂單的顧客（及其訂單），也就是兩張表的交集，但注意這里因?yàn)橐粋€(gè)顧客可能有多個(gè)訂單，所以INNER JOIN以后顧客信息其實(shí)是是廣播了的，即一條顧客信息被多條訂單記錄共用，當(dāng)然 這叫廣播（broadcast）效應(yīng)，是另一個(gè)問題，這里關(guān)注的重點(diǎn)是 INNER JOIN 的結(jié)果確實(shí)是兩表的交集，是那些同時(shí)有顧客信息和訂單信息的記錄。
-- 若要展示全部顧客（及其訂單，如果有的話），要改用LEFT (OUTER) JOIN，結(jié)果相較于 INNER JOIN 多了沒有訂單的那些顧客，即只有顧客信息沒有訂單信息的記錄
-- 當(dāng)然，也可以調(diào)換左右表的順序（即調(diào)換FROM和JOIN的對(duì)象）再 RIGHT JOIN，即：
FROM orders o
    RIGHT [OUTER] JOIN customers c
    -- 中括號(hào) [] 表示是可選項(xiàng)、可省略 
    ON o.customer_id = c.customer_id
-- 若要展示全部訂單（及其顧客），就應(yīng)該是 orders RIGHT JOIN customers，結(jié)果相較于 INNER JOIN 多了沒有顧客的那些訂單，即只有訂單信息沒有顧客信息的記錄。（注：因?yàn)檫@里所有訂單都有顧客，所以這里 RIGHT JOIN 結(jié)果和 INNER JOIN 一樣）

8.多表外連接

與內(nèi)連接類似，我們可以對(duì)多個(gè)表（3個(gè)及以上）進(jìn)行外連接，最好只用 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
-- 雖然可以調(diào)換順序并用 RIGHT JOIN，但作為最佳實(shí)踐，最好調(diào)整順序并統(tǒng)一只用 [INNER] JOIN 和 LEFT [OUTER] JOIN（總是左表全包含），這樣，當(dāng)要合并的表比較多時(shí)才方便書寫和理解而不易混亂

9.自外部連接

就用前面那個(gè)員工表的例子來說，就是用LEFT JOIN讓得到的 員工-上級(jí) 合并表也包括老板本人（老板沒有上級(jí)，即 reports_to 字段為空，如果用 JOIN 會(huì)被篩掉，用 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子句

當(dāng)作為合并條件（join condition）的列在兩個(gè)表中有相同的列名時(shí)，可用 USING (……, ……) 取代 ON …… AND …… 予以簡(jiǎn)化，內(nèi)/外鏈接均可如此簡(jiǎn)化。

一定注意 USING 后接的是括號(hào)，特容易搞忘

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
-- 復(fù)合主鍵表間復(fù)合連接條件的合并也可用 USING，中間逗號(hào)隔開就行：
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對(duì)復(fù)合主鍵的簡(jiǎn)化效果更加明顯
-- 列名不同就必須用 ON …… 了
-- 實(shí)際中同一個(gè)字段在不同表列名不同的情況也很常見，不能想當(dāng)然的用USING

11.自然連接

NATURAL JOIN 就是讓MySQL自動(dòng)檢索同名列作為合并條件。

最好別用，因?yàn)椴淮_定合并條件是否找對(duì)了，有時(shí)會(huì)造成無法預(yù)料的問題，編程時(shí)保持對(duì)結(jié)果的控制是非常重要的

但也要知道有這個(gè)東西，混個(gè)臉熟，不要?jiǎng)e人用了看不懂。

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

12.交叉連接

得到名字和產(chǎn)品的所有組合，因此不需要合并條件。 實(shí)際運(yùn)用如：要得到尺寸和顏色的全部組合

-- 得到顧客和產(chǎn)品的全部組合（毫無意義，純粹為了展示交叉連接）
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
-- 上面是顯性語(yǔ)法，還有隱式語(yǔ)法，之前講過，其實(shí)就是隱式內(nèi)合并忽略WHERE子句（即合并條件）的情況，也就是把 CROSS JOIN 改為逗號(hào)，即 FROM A CROSS JOIN B 等效于 FROM A, B，Mosh更推薦顯式語(yǔ)法，因?yàn)楦逦?USE sql_store;
SELECT 
    c.first_name,
    p.name
FROM customers c, products p
ORDER BY c.first_name

13.聯(lián)合

FROM …… JOIN …… 可對(duì)多張表進(jìn)行橫向列合并，而 …… UNION …… 可用來按行縱向合并多個(gè)查詢結(jié)果，這些查詢結(jié)果可能來自相同或不同的表

• 同一張表可通過UNION添加新的分類字段，即先通過分類查詢并添加新的分類字段再UNION合并為帶分類字段的新表。
• 不同表通過UNION合并的情況如：將一張18年的訂單表和19年的訂單表縱向合并起來在一張表里展示

注意

• 合并的查詢結(jié)果必須列數(shù)相等，否則會(huì)報(bào)錯(cuò)
• 合并表里的列名由排在 UNION 前面的決定

-- 給訂單表增加一個(gè)新字段——status，用以區(qū)分今年的訂單和今年以前的訂單
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";

到此這篇關(guān)于Sql在多張表中檢索數(shù)據(jù)的方法詳解的文章就介紹到這了,更多相關(guān)Sql檢索數(shù)據(jù)內(nèi)容請(qǐng)搜索以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持！