CODEWITHSUNDEEP
postgresqlselectjoin
Don
Don

Reputation: 4202

Select from table where value exists?

If a value, value_two does not exist (or null) in table_two I want to default to the value, value_two in table_one.

I have the following query format:

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';

I know one option is to select all of the values and handle it on the client side after returning the following:

SELECT one.value_one, one.value_two, two.value_two as 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';

Is there a 'clean' way check if two.value_two exists and default to select one.value_two if it doesn't?

Upvotes: 0

Views: 280

Answers (2)

Aurélien Gasser
Aurélien Gasser

Reputation: 3120

You can use the COALESCE function.

COALESCE returns the first of its arguments that is non null.

Use it like this:

SELECT COALESCE(two.value_two, one.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';

Note that COALESCE is an ANSI SQL function and as such is available in all implementations of SQL (MySQL, MS SQL Server, ...)

Upvotes: 2

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125254

select one.value_one, coalesce(two.value_two, one.value_two)

https://www.postgresql.org/docs/current/static/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL

Upvotes: 1

Related Questions