SQL style question: INNER JOIN in FROM clause or WHERE clause?

Question

If you are going to join multiple tables in a SQL query, where do you think is a better place to put the join statement: in the FROM clause or the WHERE clause?

If you are going to do it in the FROM clause, how do you format it so that it is clear and readable? (I'm talking about indents, newlines, whitespace in general.)

Are there any advantages/disadvantages to each?

Answer 1

ANSI joins. I omit any optional keywords from the SQL as they only add noise to the equation. There's no such thing as a left inner join, is there? And by default, a simple join is an inner join, so there's no particular point to saying 'inner join'.

Then I column align things as much as possible.

The point being that a large complex SQL query can be very difficult to comprehend, so the more order that is imposed on it to make it more readable, the better. Any body looking at the query to fix, modify or tune it, needs to be able to answer a few things off right off the bat:

• what tables/views are involved in the query?
• what are the criteria for each join? What's the cardinality of each join?
• what/how many columns are returned by the query

I like to write my queries so they look something like this:

select PatientID              = rpt.ipatientid ,
       EventDate              = d.dEvent       ,
       Side                   = d.cSide        ,
       OutsideHistoryDate     = convert(nchar, d.devent,112) ,
       Outcome                = p.cOvrClass    ,
       ProcedureType          = cat.ctype      ,
       ProcedureCategoryMajor = cat.cmajor     ,
       ProcedureCategoryMinor = cat.cminor
from      dbo.procrpt rpt
join      dbo.procd   d   on d.iprocrptid   = rpt.iprocrptid
join      dbo.proclu  lu  on lu.iprocluid   = d.iprocluid
join      dbo.pathlgy p   on p.iProcID      = d.iprocid
left join dbo.proccat cat on cat.iproccatid = lu.iproccatid
where procrpt.ipatientid = @iPatientID

Answer 2

I almost always use the ANSI 92 joins because it makes it clear that these conditions are for JOINING.

Typically I write it this way

FROM
    foo f
    INNER JOIN bar b
    ON f.id = b.id

sometimes I write it this way when it trivial

FROM
    foo f
    INNER JOIN bar b  ON f.id = b.id
    INNER JOIN baz b2  ON b.id = b2.id

When its not trivial I do the first way

e.g.

FROM 
   foo f
   INNER JOIN bar b 
   ON f.id = b.id  
      and b.type = 1

or

FROM 
   foo f
   INNER JOIN (
          SELECT max(date) date, id
          FROM foo 
          GROUP BY
             id) lastF
  ON f.id = lastF.id
      and f.date = lastF.Date

Or really the weird (not sure if I got the parens correctly but its supposed to be an LEFT join to table bar but bar needs an inner join to baz)

FROM 
     foo f
     LEFT JOIN (bar b
          INNER JOIN baz b2 
           ON b.id = b2.id
      )ON f.id = b.id

Answer 3

I definitely always do my JOINS (of whatever type) in my FROM clause.

The way I indent them is this:

SELECT fields
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.t1_id
INNER JOIN table3 t3 ON t1.id = t3.t1_id
    AND
    t2.id = t3.t2_id

In fact, I'll generally go a step farther and move as much of my constraining logic from the WHERE clause to the FROM clause, because this (at least in MS SQL) front-loads the constraint, meaning that it reduces the size of the recordset sooner in the query construction (I've seen documentation that contradicts this, but my execution plans are invariably more efficient when I do it this way).

For example, if I wanted to only select things in the above query where t3.id = 3, you could but that in the WHERE clause, or you could do it this way:

SELECT fields
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.t1_id
INNER JOIN table3 t3 ON t1.id = t3.t1_id
    AND
    t2.id = t3.t2_id
    AND
    t3.id = 3

I personally find queries laid out in this way to be very readable and maintainable, but this is certainly a matter of personal preference, so YMMV.

Regardless, I hope this helps.

Answer 4

I prefer the FROM clause if for no other reason that it distinguishes between filtering results (from a Cartesian product) merely between foreign key relationships and between a logical restriction. For example:

SELECT * FROM Products P JOIN ProductPricing PP ON P.Id = PP.ProductId
WHERE PP.Price > 10

As opposed to

SELECT * FROM Products P, ProductPricing PP 
WHERE P.Id = PP.ProductID AND Price > 10

I can look at the first one and instantly know that the only logical restriction I'm placing is the price, as opposed to the implicit machinery of joining tables together on the relationship key.

Answer 5

Use the FROM clause to be compliant with ANSI-92 standards.

This:

select *
    from a
        inner join b
            on a.id = b.id
    where a.SomeColumn = 'x'

Not this:

select *
    from a, b
    where a.id = b.id
        and a.SomeColumn = 'x'

Answer 6

When making OUTER JOINs (ANSI-89 or ANSI-92), filtration location matters because criteria specified in the ON clause is applied before the JOIN is made. Criteria against an OUTER JOINed table provided in the WHERE clause is applied after the JOIN is made. This can produce very different result sets.

In comparison, it doesn't matter for INNER JOINs if the criteria is provided in the ON or WHERE clauses -- the result will be the same. That said, I strive to keep the WHERE clause clean -- anything related to JOINed tables will be in their respective ON clause. Saves hunting through the WHERE clause, which is why ANSI-92 syntax is more readable.

Answer 7

You should put joins in Join clauses which means the From clause. A different question could be had about where to put filtering statements.

With respect to indenting, there are many styles. My preference is to indent related joins and keep main clauses like Select, From, Where, Group By, Having and Order By indented at the same level. In addition, I put each of these main attributes and the first line of an On clause on its own line.

Select ..
From Table1
    Join Table2
        On Table2.FK = Table1.PK
           And Table2.OtherCol = '12345'
           And Table2.OtherCol2 = 9876
    Left Join (Table3
        Join Table4
            On Table4.FK = Table3.PK)
        On Table3.FK = Table2.PK
Where ...
Group By ...
Having ...
Order By ...

Answer 8

I tend to use the FROM clause, or rather the JOIN clause itself, indenting like this (and using aliases):

SELECT t1.field1, t2.field2, t3.field3
FROM table1 t1
  INNER JOIN table2 t2
    ON t1.id1 = t2.id1
  INNER JOIN table3 t3
    ON t1.id1 = t3.id3

This keeps the join condition close to where the join is made. I find it easier to understand this way then trying to look through the WHERE clause to figure out what exactly is joined how.