While ANSI-92 joins have been around for over 20 years and the pre-ANSI-92 join syntax has been deprecated 4 SQL Server versions ago, it is still in use today. This article provides guidance on how to rewrite those old queries into the “new” outer form.
SQL Server 2008 introduced a single statement insert-update-delete: MERGE. This article introduces this statement, explains how it works and shows that it is really a full outer join under the covers.
SQL Server cannot do an exhaustive search of all possible execution plans when compiling a query. On its quest to find the best plan, it sometimes misses the mark. See how join hints and query hints can help in a situation like that. But be aware, using join hints can backfire. Know the risks before you start using them.
When joining more than two tables together, one additional JOIN key word is needed for each new table. there are many ways to group the tables in a join query together effectively building nested joins. However, the order or even grouping of tables does not change the query. Indeed, the query optimizer often makes use of that and produces an execution plan in which the order of the tables does not match the order in which they are mentioned in the query.
A join that is using only equality comparisons is call “Equi-Join”. A join on the other hand that has at least one inequality comparison in the join condition is called “Nonequi-Join”. Find common use cases and limitations by reading on.