Snowflake - Except operator

Question

I'm new to SQL - my company is using Snowflake and I try to write a query using EXCEPT operator. According to the documentation, I should be able to use it - but it doesn't seem to work (UNION for example, works fine). Any suggestions what I'm doing wrong?

Answer 1

SELECT column1 FROM VALUES (1),(2)
EXCEPT
SELECT column1 FROM VALUES (2);

yields just the value 1

COLUMN1
1

MINUS is the same concept, just different name.

SELECT column1 FROM VALUES (2)
MINUS 
SELECT column1 FROM VALUES (1),(2)

however returns no rows, because the single row 2 is negated by the 2 in the second set.

Both are a form of a LEFT OUTER JOIN, which is done via a LEFT JOIN and a NULL check in the WHERE.

so the ((2)) minus ((1),(2)) is the same as:

WITH data1(col1) as (
    SELECT column1 FROM VALUES (2)
), data2(col1) as (
    SELECT column1 FROM VALUES (1),(2)
)
SELECT d1.col1
FROM data1 AS d1
LEFT JOIN data2 AS d2
    ON d1.col1 = d2.col1
WHERE d2.col1 IS NULL;

which returns zero rows, and they are all present in the seconds table.