Does Join Order Matter in SQL Queries, Especially with Outer Joins?

SQL JOIN order: does it matter?

In SQL, the order of JOIN operations will affect the query results, especially when using outer joins.

INNER JOIN

For inner joins, a row in table A matches a row in table B exactly based on a certain condition, and the order of the joins does not affect the results. This is because inner joins are both commutative and associative. Therefore, queries A and B shown below will produce the same results:

<code class="language-sql">-- A (内连接)
SELECT *
FROM a INNER JOIN b ON <条件>
INNER JOIN c ON <条件>;

-- B (内连接)
SELECT *
FROM a INNER JOIN c ON <条件>
INNER JOIN b ON <条件>;</code>

OUTER JOIN

For outer joins (LEFT, RIGHT or FULL), the order of the joins is important. Unlike inner joins, outer joins are neither interchangeable nor combinable.

Commutative Law

Commutativity means that a LEFT JOIN b is the same as b LEFT JOIN a. However, this is not true for outer joins. For example, the following query:

<code class="language-sql">a LEFT JOIN b ON b.ab_id = a.ab_id</code>

Returns all rows in table a and the corresponding rows in table b, or NULL if there is no match in table b. Opposite query:

<code class="language-sql">b LEFT JOIN a ON a.ab_id = b.ab_id</code>

Returns all rows in table b and the corresponding rows in table a, or NULL if there is no match in table a. If table a and table b have different numbers of rows, the two queries will produce different results.

Associative Law

The associative law of

means that (a LEFT JOIN b) LEFT JOIN c is equivalent to a LEFT JOIN (b LEFT JOIN c). However, this is not true for outer joins either. Consider the following equivalent query:

<code class="language-sql">a LEFT JOIN b ON b.ab_id = a.ab_id
LEFT JOIN c ON c.ac_id = a.ac_id</code>

If the condition c.ac_id = a.ac_id contains a NULL comparison, the associativity law may not hold. In other words, if NULL values ​​are involved in the join condition, the order of the joins will affect the result.

The above is the detailed content of Does Join Order Matter in SQL Queries, Especially with Outer Joins?. For more information, please follow other related articles on the PHP Chinese website!