iT邦幫忙

0

MySQL JOIN多欄位 取表X有 表Y沒有的

有兩張表單 表X、表Y (裡面有A、B、C三欄位)

根據A、B、C三欄位 我想取表X有但是不存在於表Y的資料

因為需要結合多欄位(A、B、C三欄位)來判斷 所以原本都是用CONCAT來結合字串

--- 原本的寫法
SELECT * FROM 表X x
WHERE CONCAT(x.A,x.B,x.C) 
NOT IN (SELECT CONCAT(y.A,y.B,y.C) FROM 表Y y)

但效率很差 想以LFET JOIN方式改寫

可是多條件方式我查不出來

--- 後來改寫 但結果不是我要的...
SELECT * FROM 表X x
LEFT JOIN 表Y y ON 
x.A=y.A AND x.B=y.B AND x.C=y.C
AND y.A IS NULL AND y.B IS NULL AND y.C IS NULL;

參考資料如下

--- 表X
A  B C
甲 1 001
乙 2 001
丙 3 001
丁 4 001
己 5 001
--- 表Y
A  B C
甲 1 002
乙 2 001
丙 3 001
丁 4 002
戊 5 001
--- 想要的結果
A  B C
甲 1 001
丁 4 001
己 5 001

再請各位指教

謝謝 !

0
rogeryao
iT邦高手 6 級 ‧ 2019-12-25 18:06:17
最佳解答
SELECT X.* 
FROM X
LEFT JOIN Y ON 
X.A=Y.A AND X.B=Y.B AND X.C=Y.C
WHERE 1=1
AND Y.A IS NULL AND Y.B IS NULL AND Y.C IS NULL;

DEMO

Neish iT邦研究生 1 級 ‧ 2019-12-26 08:35:07 檢舉

發問的SQL語法寫錯了...
AND y.A IS NULL AND y.B IS NULL AND y.C IS NULL;
實際是這樣=>WHERE y.A IS NULL AND y.B IS NULL AND y.C IS NULL;

發現你的寫法跟我的一樣 可是DEMO裡面跑得出來... 我的不行
進一步發現可能是受其他欄位的影響@@

其實我還有一欄是日期 A B C三欄在不同日期會有重複 應該是這樣造成我結果跑不出來

--- 原本LEFT JOIN寫法的完整樣貌 (結果不是我要的)
SELECT * FROM 表X x
LEFT JOIN 表Y y ON 
x.A=y.A AND x.B=y.B AND x.C=y.C
WHERE x.CREATEDATE='2019-12-24' AND y.FinishDate='2019-12-24' AND y.A IS NULL AND y.B IS NULL AND y.C IS NULL;
--- 重新改寫 結果成功了!
SELECT * FROM 表X x
LEFT JOIN (SELECT * FROM 表Y temp_y WHERE temp_y.FinishDate='2019-12-24') y ON 
x.A=y.A AND x.B=y.B AND x.C=y.C
WHERE x.CREATEDATE='2019-12-24' AND y.A IS NULL AND y.B IS NULL AND y.C IS NULL;
2
石頭
iT邦研究生 2 級 ‧ 2019-12-25 20:32:28

如果是使用OUTER JOIN又想要判斷哪些資料不存在(IS NULL)請把判斷寫在Where

或者你可以使用NOT EXISTS

SELECT * 
FROM  X
WHERE  NOT EXISTS  (
    SELECT 1 
    FROM Y 
    WHERE X.A=Y.A AND X.B=Y.B AND X.C=Y.C
)
Neish iT邦研究生 1 級 ‧ 2019-12-26 08:36:23 檢舉

感謝石頭提醒!
我其實是寫在WHERE裡面 問題描述的SQL寫錯了

1
小魚
iT邦大師 1 級 ‧ 2019-12-25 23:28:22
SELECT x.* FROM 表X x
LEFT JOIN 表Y y ON 
x.A=y.A AND x.B=y.B AND x.C=y.C
WHERE y.A IS NULL OR y.B IS NULL OR y.C IS NULL;

你要的是不是OR ?
還有最前面只取X表的就好(建議是把欄位都列出來)

小魚 iT邦大師 1 級 ‧ 2019-12-25 23:30:25 檢舉

後來想到,
因為JOIN的條件,
應該取其中一個NULL就可以.

Neish iT邦研究生 1 級 ‧ 2019-12-26 08:37:05 檢舉

感謝小魚回覆
後來重新調整就搞定了!

0
ckp6250
iT邦新手 2 級 ‧ 2019-12-26 05:03:19

我向來比較懶,不想寫那麼長的 sql 指令。
我會在 表X 和 表Y 裡,各添加一個虛擬欄位 d ,其值為 CONCAT(A,B,C),並加上索引
這時,查詢指令就很簡單啦。

--- 原本的寫法
SELECT * FROM 表X x
WHERE d NOT IN (SELECT d FROM 表Y y)
Neish iT邦研究生 1 級 ‧ 2019-12-26 08:37:36 檢舉

這方法也不錯
只是要加欄位
感謝分享!

ckp6250 iT邦新手 2 級 ‧ 2019-12-26 09:26:17 檢舉

  我個人覺得,以這個命題而言,加虛擬欄位才是最簡便的方法。

  目前 a、b、c 三個欄位要比對,如果將來要延伸為 a、b、c、d、e、f、g....

  只要修改虛擬欄位的公式即可,幾秒鐘就搞定了,程式碼完全不用動。

我要發表回答

立即登入回答