JOIN on column if NOT NULL
I am rewriting an old SQL query and I mean problems. It contains several conditions like
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 (+)
Is it possible to rewrite this query using the correct JOIN syntax? Is it equivalent to the following, or will it lead to 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 rows long and spans 5 tables as well as multiple joins over "virtual tables" (that is, where the form conditions are x.z_id = y.z_id
), making it difficult to break down into more manageable bits or debug.
source to share
if you want to get the same result as in the first query, you have to make a left join with table a only, for example:
SELECT ...
FROM b, c
LEFT JOIN a
ON b.id = a.b_id and b.id = a.b_id
WHERE
c.id = ... b.c_id
or if you want to have the same style with all tables, you can use an inner join with table b, for example:
SELECT ...
FROM c
INNER JOIN b
on b.c_id = c.id
LEFT JOIN a
ON b.id = a.b_id
WHERE
c.id = ...
in my both queries we are fetching data from table b where the column is not null
source to share