I was shown the following technique for comparing tables when I was a junior programmer many years ago.
WITH A as (SELECT something)
, B as (SELECT another_similar_thing)
SELECT * FROM A MINUS SELECT * FROM B
SELECT * FROM B MINUS SELECT * FROM A;
This technique is simple and powerful. I use it whenever I am tuning SQL statements. The old slow statement goes in one side, or into a table that is queried. The newly tuned statement goes in the other side.
This technique is powerful. I’ve taught it to many people.
So I was quite surprised to learn that it doesn’t quite work.
Read the rest on the Optimal BI blog.