SQL Server JOIN Syntax - Explicit vs Implicit
In the world of SQL Server coding (T-SQL) there are two forms of syntax used when writing a join: Explicit (good) and Implicit (evil). Please, I implore you, do yourself (and those who read your code) a favor and use Explicit joins. OK I can hear everyone in the Implicit camp starting to cry. What's the big deal you ask?
Implicit JOINs are distinguishable by their (lazy) comma-delimited syntax. Explicit joins use SQL Server's (best practices) JOIN and ON keywords. The implicit syntax is naughty depreciated, difficult to understand and more prone to errors. Consider the follow examples.
-- Explicit JOIN. SQL-92 ANSI. Groovy.
SELECT h.HorseId, HorseName, ColorName
FROM Horses h
INNER JOIN Colors c ON h.ColorId = c.ColorId
-- Implicit JOIN.
SELECT h.HorseId, HorseName, ColorName
FROM Horses h, Colors c
WHERE h.ColorId = c.ColorId
Explicit is SQL 92 ANSI compliant, easy to read and follow SQL Server best practices.
The above examples both produce the same output. However, the first example -- the explicit join -- is easier to read. It is not necessary to look into the WHERE clause in order to determine the criteria of the join. This is a big deal in the real world where we are often joining multiple tables. Then it not so easy to determine what is or is not join criteria.
The good news is it's easy to code well. By simply adding 2 words (JOIN and ON) to a select statement it is immediately obvious that the query joins on the Colors table. I know what some of you old-school Oracle coders are thinking. The first example is verbose. So what? This method is a Microsoft SQL Server best practice. And consider this. Many real-world queries are much larger, much more detailed.
Explicit promotes consistent code.
Many of us have seen T-SQL that has its inner joines written implicitly and its outer joins done explicity. This breaks with our goal of making code consistent and easy to read. Know what I mean? Let's summarize shall we...
Explicit (good doggy!)
- SQL 92 ANSI compliant
- Easier to read
- Less prone to errors
- Keeps code consistent
- Performance benefits (results may vary)
Implicit (bad dog)
- Outdated format
- Difficult to read
- Promotes errors
- Does not follow SQL Server best practices and naming conventions.