Imagine you have two different tables/queries that are supposed to have/return identical data. You want to verify this. What's an easy way to show any unmatched rows from each table just like the example below, comparing every column? Assume there are 30 columns in the tables, many of which are NULLable.
When there is no PK or there could be duplicates per PK, joining on just PK columns isn't enough, and it would be a disaster to have to do a FULL JOIN with 30 join conditions that properly handle NULLs, plus a nasty WHERE condition to exclude the matched rows.
Usually it is when I'm writing a new query against unscrubbed or not-fully-understood data that the problem is worst and the likelihood of a PK being logically available is extremely low. I cook up two different ways to solve the problem and then compare their results, the differences highlighting special cases in the data that I was unaware of.
The result needs to look like this:
Which Col1 Col2 Col3 ... Col30 ------ ------ ------ ------ ------ TableA Cat 27 86 -- mismatch TableB Cat 27 105 -- mismatch TableB Cat 27 87 -- mismatch 2 TableA Cat 128 92 -- no corresponding row TableB Lizard 83 NULL -- no corresponding row
If [Col1, Col2] does happen to be a candidate primary key and we order by them in our final result, then we can easily see that A and B have one row different that should be the same, and each has one row that is not in the other.
In the above example, seeing the first row twice is not desirable.