Back

Explore Courses Blog Tutorials Interview Questions
0 votes
1 view
in SQL by (20.3k points)

Disregarding performance, will I get the same result from queries A and B below? How about C and D?

-- A

select *

from   a left join b

           on <blahblah>

       left join c

           on <blahblan>

-- B

select *

from   a left join c

           on <blahblah>

       left join b

           on <blahblan>  

-- C

select *

from   a join b

           on <blahblah>

       join c

           on <blahblan>

-- D

select *

from   a join c

           on <blahblah>

       join b

           on <blahblan>  

1 Answer

0 votes
by (40.4k points)

The order doesn't matter for INNER joins. As long as you change your selects from SELECT * to SELECT a.*, b.*, c.* the queries will return the same results. 

But the order matters for (LEFT, RIGHT or FULL) OUTER joins.

Outer joins are not commutative. 

Therefore, a LEFT JOIN b is not the same as b LEFT JOIN a. Outer joins are not associative either.

In your examples both (commutativity and associativity) properties are involved:

a LEFT JOIN b 

    ON b.ab_id = a.ab_id

  LEFT JOIN c

    ON c.ac_id = a.ac_id

The above query is equivalent to:

a LEFT JOIN c 

    ON c.ac_id = a.ac_id

  LEFT JOIN b

    ON b.ab_id = a.ab_id

but the below query is:

a LEFT JOIN b 

    ON  b.ab_id = a.ab_id

  LEFT JOIN c

    ON  c.ac_id = a.ac_id

    AND c.bc_id = b.bc_id

Not equivalent to this query:

a LEFT JOIN c 

    ON  c.ac_id = a.ac_id

  LEFT JOIN b

    ON  b.ab_id = a.ab_id

    AND b.bc_id = c.bc_id

Another associativity example is as follows. Assume this as (a LEFT JOIN b) LEFT JOIN c:

a LEFT JOIN b 

    ON b.ab_id = a.ab_id          -- AB condition

 LEFT JOIN c

    ON c.bc_id = b.bc_id          -- BC condition

Which is equivalent to a LEFT JOIN (b LEFT JOIN c):

a LEFT JOIN  

    b LEFT JOIN c

        ON c.bc_id = b.bc_id    -- BC condition

    ON b.ab_id = a.ab_id        -- AB condition

In avove code, both ON b.ab_id = a.ab_id and c.bc_id = b.bc_id are equality checks and do not involve NULL comparisons.

You can even have conditions with other operators or more complex ones like this: 

ON a.x <= b.x or ON a.x = 7 or ON a.x LIKE b.x or ON (a.x, a.y) = (b.x, b.y)

 and the two queries will still be equivalent.

Note: If any of these involved IS NULL or a function that is related to nulls like COALESCE(), for example if the condition was b.ab_id IS NULL, then the two queries will never be equivalent.

Enroll yourself in the best sql courses to learn in-depth about SQL statements, queries and become proficient in SQL.

Related questions

Welcome to Intellipaat Community. Get your technical queries answered by top developers!

28.4k questions

29.7k answers

500 comments

94k users

Browse Categories

...