Are you confused by the different join syntax in SQL Server? Microsoft is moving towards using ANSI syntax, which is becoming more and more prominent. While the old syntax still works, it is strongly recommended to learn the new syntax as Microsoft will not support the old syntax indefinitely.
The most common type of join is the inner join, which returns rows where data matching exists in the tables being joined. The transition from the old syntax to the new syntax for inner joins is fairly straightforward. In the old syntax, the join conditions are specified in the WHERE clause, while in the new syntax, they are defined in the FROM clause using the INNER JOIN keyword.
For example, the following old syntax:
SELECT o.name, i.name
FROM sysobjects o, sysindexes i
WHERE o.id = i.id
Is equivalent to the new syntax:
SELECT o.name, i.name
FROM sysobjects o
INNER JOIN sysindexes i ON o.id = i.id
It’s important to note that the WHERE clause is now used only for specifying selection criteria.
Update and delete statements also support the ANSI join syntax. For example:
UPDATE t1
SET t1.col = ...
FROM t1
INNER JOIN t2 ON t1.col = t2.col
DELETE t1
FROM t1
INNER JOIN t2 ON t1.col = t2.col
Outer joins are used when you want to include rows from one table even if there are no matching rows in the other table. There are two types of outer joins: left and right. Left outer joins return all rows from the table on the left side of the join, and right outer joins return all rows from the table on the right side of the join.
Here is an example of a left outer join:
SELECT *
FROM #left
LEFT OUTER JOIN #right ON leftI = rightI
Full outer joins combine the left and right outer joins, returning data if it matches in both tables or if it exists in either one.
Cross joins create a Cartesian product, which is useful in certain scenarios. One possible use for cross joins is orphan hunting, where you want to find records in one table that do not match a record in another. Prior to ANSI SQL, “NOT IN” or “NOT EXISTS” queries were recommended for this purpose, but now it is recommended to use outer joins instead.
It’s important to be aware of some differences and potential traps when using outer joins. For example, when using the old syntax, SQL Server will ignore selection criteria on the outer joined table if no data is found, while the new syntax will not return any rows. Care should be taken when translating queries to the new syntax to ensure the desired results are achieved.
Understanding the different join syntax options in SQL Server is essential for writing efficient and maintainable queries. By familiarizing yourself with the ANSI syntax and its various types of joins, you can ensure your queries are compatible with future versions of SQL Server and take advantage of the latest features and optimizations.
About the author: Neil Boyle is an independent SQL Server consultant based in London, England. He has extensive experience with SQL Server and offers a free SQL Server guide on his website at http://www.impetus-sql.co.uk.