Top 60 Oracle Blogs

Recent comments

ANSI Join Syntax in SQL Server

Another in a series of posts in response to Tim Ford's #EntryLevel Challenge.

Anyone new to SQL Server will sooner or later, and probably sooner, encounter exhortations to write joins in "ANSI join syntax". While the term is misleading and in fact incorrect, the practice of using the so-called "ANSI join syntax" contributes toward queries that are easier to understand and maintain. 

Clear Intentions

Following are two queries that produce the same result -- all possible combinations of product and subcategory names from the two tables listed in the FROM clause. (Such a result is termed a Cartesian product). Notice how I've written the joins in the respective FROM clauses.

--Query 1: Comma-delimited syntax
SELECT psc.Name, p.Name
FROM Production.ProductSubCategory AS psc, Production.Product AS p

--Query 2: Explicit Join Syntax
SELECT psc.Name, p.Name
FROM Production.ProductSubCategory AS psc CROSS JOIN Production.Product AS p

A developer looking at Query 1 might wonder whether I had forgotten to write my WHERE clause. The bizarre result of a Cartesian product combined with the ambiguity of the syntax induces doubt as to my true intention when writing the query. 

Query 2 leaves no doubt as to my intention. Bizarre as it might seem to want a Cartesian product in this instance, the syntax makes clear that I did not generate such a result by accident. The advantage of the explicit syntax in Query 2 is that my intention is clear.

Readability and Reduced Error

Explicit join syntax improves readability and helps toward reducing programmer error. Consider the following business requirements:

  • List products and their subcategories
  • Do that for caps, gloves, and socks
  • Also do it for the accessories in Category 4

Queries 3 and 4 show two attempts to generate these business results. If you're on your game, you'll spot right away that one query gives a wrong result.

--Query 3: Comma-delimited syntax
SELECT psc.Name, p.Name, psc.ProductSubcategoryID, p.ProductSubcategoryID, psc.ProductCategoryID
FROM Production.ProductSubCategory AS psc, Production.Product AS p
        OR psc.Name='Gloves'
        OR psc.Name='Socks'
      AND psc.ProductSubcategoryID = p.ProductSubcategoryID
      OR psc.ProductCategoryID = 4;

--Query 4: Explicit Join Syntax
SELECT psc.Name, p.Name
FROM Production.ProductSubCategory AS psc INNER JOIN Production.Product AS p
  ON psc.ProductSubcategoryID = p.ProductSubcategoryID
WHERE psc.Name='Caps' 
   OR psc.Name='Gloves'
   OR psc.Name='Socks'
   OR psc.ProductCategoryID = 4;

Query 3's WHERE clause is a badly-ordered jumble to show how a join condition can get "lost" among other conditions that are unrelated to the join. I missed a beat while writing the query, and my example in Query 3 actually generates a wrong result.

Query 4 isolates the join condition in an ON clause. There's no ambiguity. It's clear at a glance how the two tables are being joined. And the WHERE clause becomes much simpler to write.

My first attempt at Query 4 gave the correct result, whereas it took me three tries to get to a correctly-working version of Query 3. (You are seeing Try #2 above). Explicit join syntax not only helps toward making your intentions explicit, it reduces your workload and helps you toward correct results more quickly than might otherwise be the case.


What about terminology? I commented earlier on the phrasing "ANSI join syntax". Here are my reasons for not liking that particular phrasing:

  • Reference to ANSI slights the greater portion of the planet
  • The comma-delimited syntax is ANSI syntax as well

I used to speak of "ANSI join syntax" myself. Then I progressed to saying "ISO join syntax" in recognition that the planet did not end at my particular country's borders. Then a friend pointed out that comma-delimited join syntax is fully-supported in the ANSI and ISO SQL standards.

I've come to prefer phrasing that does not convey the incorrect impression that the comma-delimited syntax is somehow non-standard. It may be non-preferred, but it is standard. Hence my use of "explicit join syntax" to describe the practice of specifying join types and conditions in the FROM clause of a query.

My Exhortation

Banish the comma-delimited join syntax from your repertoire. Write joins using explicit syntax so that others who come after you won't have doubts over your intentions. Writing with explicit syntax keeps your intentions clear, it keeps your join conditions separate from the melee of other conditions in the WHERE clause, and you'll benefit from being able to think more clearly about a query and get to correct results faster and with greater confidence.