0 votes
1 view
in SQL by (22.4k 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.3k 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.

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


Categories

...