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.