The except and intercept clauses are great if you want to compare 2 data sets but for whatever reason cannot use the primary keys. Or you need to compare 2 tables with many columns to try and find the difference between them when that difference could be in any column.

You could build a query with joins and then a where clause which compares all the columns. Or you can use the EXCEPT keyword and simply select from both tables.

For a real world example, imagine you have an ETL procedure. A large part of this is contained within some stored procedures which populate a pre-extraction table which being a flattened version of a normalised database has many many columns.

You've made a change to the scripts as some of the values weren't being transformed correctly.

You now need to check that a) the script has made the changes you wanted it to and b) the script isn't inadvertantly making any other changes.

You can copy the pre-extraction table, make the changes and then run the scripts. You now have two copies of the data, one pre change, the other post change.

To write the sql query, you can use something like the following to get the column names (saves typing)

select column_name + ','

from information_schema.columns

where table_name = ' '

order by ordinal_position

remove any columns you don't want to compare and then build the query:

select col1, col2, col3...col99

from postchange_table

EXCEPT

select col1, col2, col3...col99

from prechange_table

the return should only give the rows you expect to have changed. Any more and you've changed more than you expected, any less or zero and nothing has happened