Nice, easy one to start the day off on, thanks, Frans
Using a DISTINCT with a UNION add about as much value as using it in a subquery (especially a correlated subquery)
e.g. SELECT Col1, Col2 FROM dbo.TheTable WHERE Col3 IN (SELECT Col1, Col2 FROM dbo.TheTable WHERE Col3 IN (SELECT DISTINCT Col2 FROM dbo.Table2 WHERE ...) Col2 FROM dbo.Table2 WHERE ...)