[SQL][Quiz]多資料表的關聯處理

內部訓練題目

在之前的訓練課程中,特別是再教 SQL 的關聯處理的時候,都會遇到不少人都是用硬背的方式,其實 SQL 是蠻容易入門的,不需要印去背那些指令,那樣學起來是很辛苦的,如果您能熟悉這些集合運算的概念,應該是可以很輕鬆地來找出所想到的結果。因此我就簡單的設計一個題目,假設我們有以下兩個資料表,第一個是我們有哪些商品的資料,第二個是我們的銷售資料。那我要怎麼從這兩個表中去找出來,有哪些商品代號是還沒有銷售過的呢 ?

資料可以透過以下這個語法來產生範例資料

SELECT * INTO #Items FROM ( VALUES ('A01'),('A02'),('B01'),('B02')) Items(ID)

SELECT * INTO #Orders 
	FROM ( VALUES ( 1,'張先生', 'A01'),( 2,'李先生', 'B01'),( 3,'黃小姐', 'B02'),( 4,'宋先生', 'A01')) Orders(ID,Custom,ItemID)
GO


SELECT * FROM #Items;
SELECT * FROM #Orders;
GO

 

1. ANSI-92 的 LEFT JOIN 寫法:基本上這個應該算是最容易被想到的答案之一

SELECT #Items.ID 
	FROM #Items
	LEFT JOIN #Orders ON #Items.ID = #Orders.ItemID
	WHERE #Orders.ID IS NULL

 

2.這個是採用 FULL OUTER  JOIN:基本上跟前面的 LEFT JOIN 都是相同的,但直覺上還是會儘量採用前者的 LEFT JOIN 或 RIGHT JOIN 會比較合適一點

SELECT #Items.ID 
	FROM #Items
	FULL JOIN #Orders ON #Items.ID = #Orders.ItemID
	WHERE #Orders.ID IS NULL

 

3.使用 Except : 這個是標準 SQL 集合運算的指令,不少的初學者會容易忽略掉這個指令的使用,其實搭配的合宜,使用起來也是蠻不錯的

SELECT #Items.ID FROM #Items
EXCEPT
SELECT #Orders.ItemID FROM #Orders 

 

4. 搭配 NOT IN 的指令:這個也是大家蠻常用的寫法,同樣的也很適合在這樣的例子當中來使用

SELECT #Items.ID FROM #Items
WHERE #Items.ID NOT IN ( SELECT #Orders.ItemID FROM #Orders )

 

5. 搭配 EXISTS 的指令

SELECT #Items.ID FROM #Items
WHERE NOT EXISTS ( SELECT 1 FROM #Orders WHERE #Items.ID = #Orders.ItemID  )

 

6. OUTER JOIN 的變形應用:基本上這個的想法跟 1 的概念是雷同的,但只是改用 GROUP BY 和 HAVING 的方式來做過濾

SELECT #Items.ID 
	FROM #Items
	LEFT JOIN #Orders ON #Items.ID = #Orders.ItemID
	GROUP BY #Items.ID HAVING COUNT(#Orders.ID)=0

 

7. OUTER APPLY : 基本上也還是跟 1 的概念相同,但這個是利用 APPLY 的方式,去關聯一個子查詢

SELECT #Items.ID 
	FROM #Items
	OUTER APPLY ( SELECT TOP 1 #Orders.ID FROM #Orders WHERE #Items.ID = #Orders.ItemID ) Orders
	WHERE Orders.ID IS NULL

 

8. SubQuery : 這也是一種變形的方式,只是採用子查詢的概念去思考

SELECT #Items.ID 
	FROM #Items
	WHERE ( SELECT TOP 1 #Orders.ID FROM #Orders WHERE #Items.ID = #Orders.ItemID ) IS NULL

 

如果還有人有想到其他的方式,也歡迎提供給我參考看看囉。