Should I use the sql JOIN keyword for complex join

Question

I've got the following request :

select * 
    from tbA A, tbB B, tbC C, tbD D
where 
    A.ID=B.ID and B.ID2= C.ID2 and A.ID=D.ID and C.ID3=D.ID3 and B.ID4=D.ID4
and
    A.Foo='Foo'

I've heard several times that this join syntax is depreciated, and that I should use the 'JOIN' keyword instead.

How do I do that in such a complicated join (multiple tables joined on multiple columns belonging to different tables)? Do you think this best practice still applies here ?

Answer 1

I find join syntax much easier to understand

select *
from tbA A
inner join tbB B on a.id = b.id
inner join tbC C on b.id2 = c.id2
inner join tbD D on a.id = d.id and c.id3 = d.id3 and b.id4 = d.id4
where A.Foo='Foo'

Now you can clearly see how data are joined together and that it is not a very complicated join altogether.

BTW, the database design in your example strongly smells of lacking normalization. Usually you should have either one table joining to many (a join b on a.bid = b.bid join c on a.cid= c.cid) or a chain (a join b on a.bid = b.bid join c on b.cid = c.cid).

EDIT. Added optional keyword INNER which does not change result, but makes it more clear.

Answer 2

SELECT * 
FROM tba AS a
    JOIN tbb AS b ON a.id = b.id
    JOIN tbc AS c ON b.id2 = c.id2
    JOIN tbd AS d ON a.id = d.id AND c.id3 = d.id3 AND b.id4 = d.id4
WHERE 
    a.foo = 'Foo'

Though I'm having a hard time imagining any need for that. Bare to give an example, or eh more descriptive table names?

Answer 3

JOIN syntax is more legible (though I personally prefer WHERE syntax in simple cases), and, which is more important, can handle INNER and OUTER joins in more clear way.

WHERE is not deprecated and will probably never be.

It's deprecated only in a sense that different OUTER JOIN workarounds (like (*) and (+)) are deprecated.

There is nothing you cannot do with JOIN that you can do with WHERE, but not vise versa.

Answer 4

It's a matter of taste, but I like the JOIN keyword better. It makes the logic clearer and is more consistent with the LEFT OUTER JOIN syntax that goes with it. Note that you can also use INNER JOIN which is synonymous with JOIN.

The syntax is

   a JOIN b
    ON expression relating b to all of the tables before

b can be a join itself. For inner joins it doesn't matter, but for outer you can control the order of the joins like this:

select * from
   a left join
      d join c
      on d.i = c.i
   on a.k = d.k 

Here a is left-joined to the inner join between d and c.

Here is your query:

select * 
    from tbA A
    join tbB B on A.ID = B.ID
    join tbC C on B.ID2 = C.ID2
    join tbD D on A.ID = D.ID and C.ID3 = D.ID3 and B.ID4 = D.ID4
where 
    A.Foo='Foo'