The advantages of this syntax are that it lets you compare two output streams to identify rows that differ, and that it treats NULLs as being equivalent. EXCEPT and INTERSECT are really useful when you need NULL to equal NULL, and as Paul White has discussed, they use a special SQL Plan Operator to perform better than alternative approaches.
For comparing query output, before and after snapshots of tables, etc., I typically use the following snippet:
DSO AS (
DSN AS (
SELECT 'Old' AS Tbl, * FROM (SELECT * FROM DSO EXCEPT SELECT * FROM DSN) AS U1
SELECT 'New' AS Tbl, * FROM (SELECT * FROM DSN EXCEPT SELECT * FROM DSO) AS U2
ORDER BY Tbl DESC;
The reason I use the DSO and DSN CTEs is to isolate the "diff" portion of the query from the portion that identifies the data sets being diffed. This way I only need to replace #Old and #New in one place each. It also lets me replace the * in the DSO and DSN CTEs with an explicit column list if I want to exclude specific columns from the comparison. I can further customize it by modifying the ORDER BY clause at the end - I will frequently stick a candidate key at the beginning. This can be useful for regression testing - dump the output from two different versions of a query into temp tables, then compare the output.