AP 13 - Avoid inner join after outer join in multi-join query

Severity

Type

Problem

If an outer join is followed by an inner join, all rows from the outer join are thrown away if the predicate in the inner join’s ON clause compares an attribute from the non-preserved side of the outer join and an attribute from a third table. Let’s go through an example

SELECT a.col1
      ,b.col2
  FROM Customer a
  LEFT
  JOIN orders b
    ON a.customer_id=b.customer_id
 INNER
  JOIN order_line c
    ON b.order_id=c.order_id

In this query, we left outer join the orders table to the customer table and then inner join the order_line table to the orders table. As a result, only preserved rows from both tables were retained in the result set. In other words we implicitly converted the outer join to an inner join.

The preceding query is equivalent to the following query

SELECT a.col1
      ,b.col2
  FROM Customer a
 INNER
  JOIN orders b
    ON a.customer_id=b.customer_id
 INNER
  JOIN order_line c
    ON b.order_id=c.order_id

Solution

Do not use an inner join against a non preserved table. Here is the correct query to preserve the rows of the order table.

SELECT a.col1 
      ,b.col2 
  FROM orders a 
 INNER 
  JOIN order_line b 
    ON a.order_id=b.order_id
 RIGHT 
  JOIN customer c 
    ON a.customer_id=c.customer_id

Legitimate use of the anti pattern

None