NOT IN/NOT EXISTS/EXCEPT 使用注意事項
#SQL
【SQL Tips】之【 NULL處理技巧，使用NOT IN /NOT EXISTS/EXCEPT】
許多時候，兩個資料表要找出差異值的時候，許多人第一直覺就是使用【NOT IN】，當然大部分時候都不會有狀況，但是碰到NOT IN的子查詢資料值，如果有NULL，就全盤皆輸，意思就是找不出任何差異。這樣在小量資料可以藉由眼力觀察的狀況下，還可以找出這樣寫法NOT IN的危險地方，但是碰到背景程式，或是資料量多的時候，幾乎無法觀察到這樣危險。所以，告訴自己不要再用【NOT IN】去找出兩邊資料差異。
反倒是要使用【NOT EXISTS】寫法，雖然是複雜一點，但是跨越SQL Server與Oracle兩種資料庫，都是可以正常找出兩邊資料差異值，不擔心NOT EXISTS的基礎資料表有NULL值狀況。另外值得一提就是，需多人會直接使用SQL Server的【EXCEPT】與Oracle的【MINUS】方式，要留意再留意，這樣的方式雖然可以找出差異值，但是針對回傳值，會自動進行重複資料列移除。
【SQL Server Code】
if object_id('x') is not null
drop table x
go
--建立比對基礎資料
create table x(a int)
go
insert into x values(1)
insert into x values(1) --注意重複
insert into x values(NULL) --注意NULL
go
if object_id('y') is not null
drop table y
go
--建立簡單比對來源資料
create table y(b int not null)
go
insert into y values(1)
insert into y values(1)
insert into y values(2)
insert into y values(2)
insert into y values(2) --注意三個2
go
--三種找出y資料表中(1,1,2,2,2) 然後不存在於 x資料表中的(1,1,null)
--【預期要回傳三個2】
--第一種 使用NOT IN 注意(子查詢有NULL值)
--無法處理對比資料表有NULL狀況
SELECT b
FROM y
WHERE b NOT IN(SELECT a from x)
GO
--第二種 使用NOT EXISTS 注意(SELECT 需要 JOIN)
--忠實回傳三個2
SELECT b
FROM y
WHERE NOT EXISTS (SELECT * FROM x WHERE x.a = y.b)
GO
--第三種 使用EXCEPT (僅回傳一個2)
SELECT b FROM y
EXCEPT
SELECT a FROM x
GO
【Oracle Code】
drop table x purge;
--建立比對基礎資料
create table x(a int);
insert into x values(1);
insert into x values(1); --注意重複
insert into x values(NULL); --注意NULL
drop table y purge;
--建立簡單比對來源資料
create table y(b int not null);
insert into y values(1);
insert into y values(1);
insert into y values(2);
insert into y values(2);
insert into y values(2); --注意三個2
--三種找出y資料表中(1,1,2,2,2) 然後不存在於 x資料表中的(1,1,null)
--第一種 使用NOT IN 注意(子查詢有NULL值)
--無法處理對比資料表有NULL狀況
SELECT b
FROM y
WHERE b NOT IN(SELECT a from x);
--第二種 使用NOT EXISTS 注意(SELECT 需要 JOIN)
--忠實回傳三個2
SELECT b
FROM y
WHERE NOT EXISTS (SELECT * FROM x WHERE x.a = y.b);
--第三種 使用EXCEPT (僅回傳一個2)
SELECT b FROM y
MINUS
SELECT a FROM x;
sql兩個資料表比對 在 [SQL ] 資料表欄位名稱比對欄位資料問題- 看板Database 的美食出口停車場
DBMS sql server express edition 2008
Language c#
______________________________________________________________________________
想請問，目前有兩個資料表如下
資料表(一) (原有產品)
項目 產品A 產品B 產品C 產品D
歡樂包 1 1 3 4
全家包 2 1 1 1
資料表(二) (附加產品)
項目(FK) 額外產品 數量
歡樂包 產品A 3
歡樂包 產品B 1
全家包 產品A 5
我希望計算出個別項目的某產品總和
以上面兩張表來看，我在歡樂包中的產品A總數為4
(資料表一中有1個，外加資料表二有3個)
歡樂包產品B總和為1+1=2
歡樂包產品C總和為3+0=3
以此類推，計算出所有的項目的各項產品
最後希望得到表格如下
資料表(輸出)
項目 產品A 產品B 產品C 產品D
歡樂包 4 2 3 4
全家包 7 1 1 1
資料表一紀錄原有的資料，資料表二紀錄附加的資料
這樣的資料表設計下有辦法單純用SQL得到輸出的資料表嗎
已經做過得嘗試
______________________________________________________________________________
google大神我拜過了，可是找不到類似的主題
因為我想做的事情是比對欄位名稱跟欄位內的資料
似乎沒辦法直接比對(也有可能是我搜尋的關鍵字不夠關鍵)
目前的作法是先把兩個表各自取出項目以及產品A~D
然後透過c#去做加總後產生新的表格
不過我希望能透過純sql來解決這個問題
感謝m(_ _)m
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 140.138.145.170
※ 編輯: DRLai 來自: 140.138.145.170 (07/27 00:07)
... <看更多>