Hi I am running a query to check for any changes in a table between two dates….
SELECT * FROM TABLE_A where run_time = current_date() MINUS SELECT * FROM TABLE_A where run_time = current_date()-1
The first select statement (where run_time = current_date() return 3,357,210 records. The second select statement (where run_time = current_date()-1 returns 0 records.
Using the MINUS operator, I was expecting to see 3,357,210 records (3,357,210 – 0) but instead I get 2,026,434
Any thoughts on why? Thanks
Advertisement
Answer
https://docs.snowflake.com/en/sql-reference/operators-query.html#minus-except
Removes rows from one query’s result set which appear in another query’s result set, with duplicate elimination.
Thus, you only have 2,026,434 unique values in your first query. The missing million-and-a-bit are the duplicates, which have been eliminated.