JOIN on column only if NOT NULL

I’m in the process of re-writing an old SQL query and have troubles making sense out of it. It contains several conditions of the form

SELECT ...
FROM a, b, c
WHERE
c.id = ...
AND (
  a.x_id IS NULL
  OR a.x_id = c.x_id
)
AND b.id = a.b_id (+)

Can this query be rewritten using proper JOIN syntax? Is it equivalent to the following or will it produce different results under certain circumstances?

SELECT ...
FROM b
LEFT JOIN a
ON b.id = a.b_id
LEFT JOIN c
ON a.x_id = c.x_id
WHERE c.id = ...

The original query is 100 lines long and spans 5 tables, plus several joins over “virtual tables” (i.e. where conditions of the form x.z_id = y.z_id), which makes it hard to break down into more manageable bits or debug.


Source: oracle

Leave a Reply