CODEWITHSUNDEEP
sqloracle-databasejoinnull
knittl
knittl

Reputation: 265211

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.

Upvotes: 4

Views: 15404

Answers (1)

EntGriff
EntGriff

Reputation: 875

if you want same result as you have in first query - you must make left join only with table a, like this : 

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 the same style with all tables, you can use inner join with table b, like this : 

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 query we select data from table b where column is not null

Upvotes: 4

Related Questions