Postgres - Where EXISTS else use other where condition

Question

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.

klin · Accepted Answer · 2022-06-29 21:45:12Z

1

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.

edited Jun 29, 2022 at 21:45

answered Jun 29, 2022 at 20:27

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

turbofood Over a year ago

This query is returning more than 1 record with example records like (1, John, null) & (2, John, Frank). The problem is when we search for a record with first name & last name like (John, Jonas) then, it should be returning just the id of 1. However, in your query with coalesce your not actually comparing against the t.last_name column so, it ends up returning both records because it essentially just looks for records with t.first_name = :firstName which is not what I want because I also want it to look for t.last_name IS NULL

klin Over a year ago

See the updated answer for an optimized query.

Collectives™ on Stack Overflow

Postgres - Where EXISTS else use other where condition

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related