Find the difference between 1 column depending on date-CodePudding

When I run this:

SELECT NAME FROM T1
WHERE _LOAD_DATETIME::date = '2022-01-31'

I see 62 rows

but when I do

SELECT NAME FROM T1
WHERE _LOAD_DATETIME::date = '2022-02-01'

I see 59

I want to see what NAME's are missing when it ran for _LOAD_DATETIME::date = '2022-02-01'

I thought this would work but it doesn't:

SELECT NAME FROM table
WHERE _LOAD_DATETIME::date = '2022-02-01' 
AND NOT EXISTS (
SELECT NAME FROM 
table
WHERE _LOAD_DATETIME::date = '2022-01-31')

CodePudding user response：

You have to use MINUS for your purposes:

SELECT NAME FROM T1
WHERE _LOAD_DATETIME::date = '2022-01-31'
MINUS
SELECT NAME FROM T1
WHERE _LOAD_DATETIME::date = '2022-02-01'

If we are talking about PostgreSQL, you have to use EXCEPT instead of MINUS.

CodePudding user response：

There are two set operators MINUS or EXCEPT you can use (they are aliases for each other)

SELECT column1 FROM values (1),(2),(3),(4) MINUS SELECT column1 FROM values (2),(3),(4),(5);

gives 1 if you want to see 5 you need to flip the order of SELECTs.