Postgres - Where EXISTS else use other where condition-CodePudding

I am trying to write a single query that simply looks for a record based on 2 values. However, if the record doesn't exist I want to search again where 1 of the values (last name) is null. I'm trying to figure out if this is possible outside of PL/SQL through some use of EXISTS or IN keywords.

SELECT t.id 
FROM table t 
WHERE t.first_name = :firstName AND 
EXISTS (SELECT t.id FROM table t WHERE t.first_name = :firstName AND t.last_name = :lastName)
ELSE t.last_name IS NULL;

EDIT:

I have 2 records:

(1, John, null) & (2, John, Frank)

If we search for John Jonas then, we expect 1 to be returned. Alternatively, if we search for John Frank we expect 2 to be returned.

CodePudding user response：

You might use COALESCE:

select t.id 
from my_table t 
where t.first_name = :firstName 
and coalesce(t.last_name, :lastName) = :lastName;

The above query returns all the rows where first_name is equal to :firstName and last_name is null or equal to :lastName. The logic you want (conditional querying) is much more complex:

with condition(do_exist) as (
    select exists(
        select from my_table
        where first_name = :firstName
        and last_name = :lastName)
)

select id
from my_table 
cross join condition
where first_name = :firstName
and case when do_exist then last_name = :lastName else last_name is null end;

Test it in Db<>fiddle.