Sriram Sriram - 1 month ago 5
MySQL Question

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?

Answer

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.

Comments