Inside Microsoft® SQL Server® 2008: T-SQL Querying

Conclusion

I covered many aspects of joins and set operations and demonstrated new querying techniques that you might find handy.

Remember that the comma-based syntax for cross and inner joins is part of standard SQL and is fully supported by SQL Server. However, when you intend to write an inner join but you forget to specify the join predicate in the WHERE clause, you get a Cartesian product. For this reason and for consistency’s sake, I recommended that you stick to the ANSI SQL-92 join syntax with the JOIN keyword.

SQL Server has native operators for the UNION, UNION ALL, EXCEPT, and INTERSECT set operations. It also provides other tools that allow simple solutions for achieving EXCEPT ALL and INTERSECT ALL.