SQL - A bit about Nested Joins

posted by Mark @ 4:44pm, Sunday 11 October 2009.

I have maintained/re-written legacy code in every stage of my career to date and I cant see that stopping in the future.

Quite a few times I have found stored procedures and views, with badly formatted nested joins and absolutly no comments.
If you have never come across nested joins before, keep reading and I will try to cover a basic example below and how to de-mystify it.

Lets look at nicely formatted and VERY basic example of a nested join (real examples can be alot more complicated):

SELECT
   *
FROM
   tb1 
   LEFT JOIN tb2
        ON tb1.c1 = tb2.c1 
   RIGHT JOIN tb3 
        LEFT JOIN tb4 
                ON tb3.c1 = tb4.c1 
        ON tb1.c1 = tb3.c1

The first thing I do when I see a nested join is add parenthesis, which makes the above look like this:

SELECT
   *
FROM
   tb1 
   LEFT JOIN tb2
        ON tb1.c1 = tb2.c1 
   RIGHT JOIN (tb3 
        LEFT JOIN tb4 
                ON tb3.c1 = tb4.c1) AS tb3 
        ON tb1.c1 = tb3.c1

From my point of view, the whole thing makes a lot more sense now, although to be honest the nested join just looks like a derrived table now with a missing 'select *'... which would look like:

SELECT
   *
FROM
   tb1 
   LEFT JOIN tb2
        ON tb1.c1 = tb2.c1 
   RIGHT JOIN (
        SELECT *
        FROM tb3
        LEFT JOIN tb4 
                ON tb3.c1 = tb4.c1) AS tb3 
        ON tb1.c1 = tb3.c1

It might just be me, but the above looks infinitely more readable than the first example, even with the extra code?
Having said that, without a WHERE clause the derived table isnt really doing much. Lets get rid of the nested join/derived table and the extra code and see what it looks like:

SELECT
   *
FROM
   tb1 
   LEFT JOIN tb2
        ON tb1.c1 = tb2.c1 
   RIGHT JOIN tb3
        ON tb1.c1 = tb3.c1
   LEFT JOIN tb4 
        ON tb3.c1 = tb4.c1

Much better! Now the final step is to remove that silly RIGHT JOIN (Silly? read my take RIGHT JOINS):

SELECT
   *
FROM
   tb3 
   LEFT JOIN tb4
        ON tb3.c1 = tb4.c1 
   LEFT JOIN tb1
        ON tb3.c1 = tb1.c1
   LEFT JOIN tb2 
        ON tb1.c1 = tb2.c1

Perfect! (even if I do say so myself) The final result is a nicely formatted sql select, that most sql programmers should be able to quickly read and understand.

As this example shows, theres really no such thing as a nested join, either your using a derived table (which is pretty pointless without a where clause) or you are just writing your SQL in a different way.

I hope this helps a little, however as always get in contact if you have any questions/comments.

UPDATE: I have added a more detailed example sql script on the Downloads page.