SQL “EXCEPT” vs “WHERE NOT IN”

I would expect the following two queries to yield identical results; however, I have several instances where the first is empty while the second returns several hundred rows. How is this possible? Working in Azure Synapse using T-SQL

SELECT [value] FROM table_a
WHERE [value] NOT IN (SELECT [value] FROM table_b)

SELECT [value] FROM table_a
EXCEPT (SELECT [value] FROM table_b)

You need to sign in to view this answers