如何從Snowflake的另一個表中選擇不在表中的所有內容？

所以我有兩張桌子：

表格1。

ID  value value2
001     A    blue 
002     V     red
003     A    blue
004     V   green
005     X    blue

表 2。

ID  value value2
001     A    blue 
004     V   green

我想做的就是使用 CTE（考慮到資料的性質，這很重要），回傳 table2 ID 沒有出現在表 1 中的實體。看起來像：

CTE結果

ID  value  value2
002     V     red
003     A    blue
005     X    blue

所以我期待類似的東西：

WITH t1 AS (
SELECT ID, value,value2
FROM table1
),
t2 AS (
SELECT ID, value,value2
FROM table2
)
SELECT 
??????

我認為這將是某種加入，但我正在努力以這種方式正確地做到這一點。

uj5u.com熱心網友回復：

減號方式：

好吧，根本不是 CTE，但是鑒于您的行“都一樣”，您可以執行MINUS：

SELECT id, value, value2 FROM table1
MINUS 
SELECT id, value, value2 FROM table2;

在使用 CTE 預取/調節資料的背景關系中，可以是：

WITH t1 AS (
  SELECT ID, value,value2
  FROM table1
), t2 AS (
  SELECT ID, value,value2
  FROM table2
)
SELECT id, value, value2 FROM table1
MINUS 
SELECT id, value, value2 FROM table2

現在，如果您的 CTE 將所有列對齊，則可以簡化為：

WITH t1 AS (
  SELECT ID, value,value2
  FROM table1
), t2 AS (
  SELECT ID, value,value2
  FROM table2
)
SELECT * FROM table1
MINUS 
SELECT * FROM table2

左連接方式：

這也可以通過添加 WHERE IS NULL 子句來使用 LEFT JOIN（稱為 LEFT OUTER JOIN）來完成：

WITH t1 AS (
  SELECT ID, value,value2
  FROM table1
), t2 AS (
  SELECT ID, value,value2
  FROM table2
)
SELECT t1.id, t1.value, t1.value2 
FROM table1 as t1
LEFT JOIN table2 as t2
    ON t1.id = t2.id /* plus other clauses if you want those as well */
WHERE t2.id IS NULL

你用文字提出的要求：

回傳 table2 ID 未出現在表 1 中的實體。

表示為 LEFT JOIN：

WITH t1 AS (
  SELECT ID, value,value2
  FROM table1
), t2 AS (
  SELECT ID, value,value2
  FROM table2
)
SELECT t2.id, t2.value, t2.value2 
FROM table2 as t2
LEFT JOIN table1 as t1
    ON t1.id = t2.id /* plus other clauses if you want those as well */
WHERE t1.id IS NULL

沒有結果：

because 001 & 004 are both in table1

this can also be expressed as a RIGHT JOIN:

WITH t1 AS (
  SELECT ID, value,value2
  FROM table1
), t2 AS (
  SELECT ID, value,value2
  FROM table2
)
SELECT t2.id, t2.value, t2.value2 
FROM table1 as t1
RIGHT JOIN table2 as t2
    ON t1.id = t2.id /* plus other clauses if you want those as well */
WHERE t1.id IS NULL

which still returns no results.

Funny to me aside on CTE's

I almost always use CTE's to input "fake data" for people's questions, thus to structure this question and "use your two CTE's" I had to nest my CTE's which is just really funny, but shows how to nest them, and how powerful they can be:

WITH table1(ID, value, value2) as (
    SELECT * FROM VALUES
        ('001', 'A', 'blue'),
        ('002', 'V', 'red'),
        ('003', 'A', 'blue'),
        ('004', 'V', 'green'),
        ('005', 'X', 'blue')
), table2(ID, value, value2) as (
    SELECT * FROM VALUES
        ('001', 'A', 'blue'),
        ('004', 'V', 'green')
), cte as(
  
    
WITH t1 AS (
  SELECT ID, value,value2
  FROM table1
), t2 AS (
  SELECT ID, value,value2
  FROM table2
)
SELECT t2.id, t2.value, t2.value2 
FROM table1 as t1
RIGHT JOIN table2 as t2
    ON t1.id = t2.id /* plus other clauses if you want those as well */
WHERE t1.id IS NULL
    
    
)
SELECT * FROM cte;

標籤：sql 加入 雪花云数据平台 公共表表达式

上一篇：Linux 0.11原始碼閱讀筆記-總覽

下一篇：如何連接具有多個ID的資料框？