mysqljoinleft-join

Order of keys in the LEFT JOIN condition

What happens when we reverse the order of keys in the LEFT JOIN condition?

Ideal SQL left join syntax for joining two tables is 

select fields 
from tableA 
LEFT JOIN tableB 
ON tableA.key = tableB.key

Flipped order of join condition 

select fields 
from tableA 
LEFT JOIN tableB 
ON tableB.key = tableA.key  -- (order flipped)

Will these queries produce different set of results?

Solution

  • LEFT JOIN is not commutative, you can't switch arguments: tableA LEFT JOIN tableB is equivalent to tableB RIGHT JOIN tableA, and different from tableB LEFT JOIN tableA.

    == is commutative, you can switch arguments: tableA.key = tableB.key is equivalent to tableB.key = tableA.key.