Summary

Dialects of SQL have evolved over the years and SQL Server understands different expressions and techniques that do the same thing. The ANSI-92 SQL standard helps to distill a variety of join techniques to a manageable number. This not only simplifies the choices, but also provides some guidance when maintaining code and scripts written by others. Follow the ANSI standard and use the techniques promoted by SQL Server. This will ensure that your queries will continue to work most efficiently going forward.

Joins provide a means to reassemble data back into meaningful information. The Inner Join matches rows between two tables whereas the Outer Joins select all rows from one side of the join and only matching rows from the other. Cross joins and Full joins, although less common in most applications, provide a means to match up combinations of rows from two tables that may not be related.

Union queries allow records from multiple tables to be combined, rather than joined, bringing rows into a single result set. This is an effective technique for partitioning and federating data in archive tables and databases in different geographic locations.

It's important to understand the impact of multi-table joins and unions. Query performance is significantly impacted by the use of indexes and data types. If done correctly, SQL Server can work very efficiently with a large volume of data. If not, complex queries can demand significant server resources and impede overall system performance and availability. Test your queries with real data, and analyze the execution plans to make sure your queries execute as you expect them to.