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

Question

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

If you intend to do this in the FROM clause, how do you format it so that it is understandable and readable? (I'm talking about indentation, new characters, spaces in general.)

Are there any advantages / disadvantages for everyone?

Answer 1

I tend to use a sentence, FROMor rather a sentence JOIN, indentation 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

. , WHERE, , .

Answer 2

(ANSI-89 ANSI-92), , , ON, , JOIN . OUTER JOINed, WHERE, JOIN. .

, INNER JOIN , ON WHERE - . , , WHERE - , JOINed, ON. WHERE, ANSI-92 .

Answer 3

ANSI 92, , JOINING.

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

,

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

,

.

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

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

( , parens, , bar baz)

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

Answer 4

FROM, - ( ) . :

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

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

, , , , .

Answer 5

Join, From. , .

. , "", "", "", " ", "" "". . , On .

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 6

FROM, ANSI-92.

:

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

:

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

Answer 7

JOINS ( ) FROM.

:

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

, , , WHERE FROM, ( , MS SQL) , , ( , , , ).

, , t3.id = 3, , WHERE, :

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

, , , , , , YMMV.

, , .

Answer 8

ANSI . SQL, . , , ? , " ".

.

, SQL- , , , , . , , , , :

• / ?
• ? ?
• how many / columns returned on request

I like to write my queries so that 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