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')
Advertisement
Answer
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
.