Comparing sets in SQL

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
UNION ALL
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.

Comments