How to select everything NOT in a table from another table in Snowflake?-CodePudding

So I have two tables :

Table 1.

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

Table 2.

ID  value value2
001     A    blue 
004     V   green

All I would like to do is using a CTE (thats important given the nature of the data) , return the instances where the table2 ID's do not appear in table 1. Looking like:

CTE Result

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

So I'm expecting something like:

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

I thought it would be some sort of join but I am struggling to do it this way properly.

CodePudding user response：

The MINUS way:

Well not a CTE at all, but given your rows are "all the same" you could do a MINUS:

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

ID	VALUE	VALUE2
002	V	red
003	A	blue
005	X	blue

which in the context of using CTE's to prefetch/condition the data can be:

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

now if your CTE's align the columns all the same that can be simplified to:

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

The LEFT JOIN way:

This can also be done with a LEFT JOIN (called a LEFT OUTER JOIN) by adding a WHERE IS NULL clause:

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

ID	VALUE	VALUE2
002	V	red
003	A	blue
005	X	blue

What you asked for with words:

return the instances where the table2 ID's do not appear in table 1.

expressed as a 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

gives no results:

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;