CODEWITHSUNDEEP
postgresqljoinleft-join
Don
Don

Reputation: 4202

Join table only if record exists or return null?

I am querying records from multiple tables. Records exist for any id_value in schema.table_one but may or may not exist in schema.table_two (or additional tables joined in the query).

The following query format works fine when there is a record with the given id_val in all queried tables:

SELECT one.value_one, two.value_two
FROM schema.table_one one LEFT JOIN schema.table_two two 
    ON one.id_value = two.id_value 
    WHERE one.id_value = 'id_val' AND two.other_column = 'other_val';

If the record is removed from table_two or doesn't exist (and/or any additionally joined tables) then no results are returned.

Is there a way to either return null for two.value_two if there is no record for the given id_val in table_two or only join if the record exists?

Upvotes: 2

Views: 2876

Answers (1)

klin
klin

Reputation: 121889

Move conditions relating to the second table from WHERE to ON:

SELECT one.value_one, two.value_two
FROM schema.table_one one 
LEFT JOIN schema.table_two two 
    ON one.id_value = two.id_value 
    AND two.other_column = 'other_val'
WHERE one.id_value = 'id_val'

Upvotes: 2

Related Questions