'Select only rows with COLUMN=<value> if matches exist, or COLUMN IS NULL otherwise

Here's a sample script to demonstrate the problem:

CREATE TABLE person (
  id NUMERIC PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  city VARCHAR(255)
);

INSERT INTO person(id, name, city) VALUES(1, 'John', 'New York');
INSERT INTO person(id, name, city) VALUES(2, 'Mike', 'Boston');
INSERT INTO person(id, name, city) VALUES(3, 'Ralph', NULL);

For city='Boston' I want only the 2nd row to be returned. For city='Chicago' I want the 3rd row to be returned.

sqloraclepostgresql


Solution 1:[1]

If you are looking for one row:

select p.*
from person p
where city is null or city = 'Boston'
order by (city = 'value') desc
fetch first 1 row only;

If you can have multiple matches, then I would suggest:

select p.*
from person p
where p.city = 'Boston'
union all
select p.*
from person p
where p.city is null and
      not exists (select 1 from person p2 where p2.city = 'Boston');

Or, using window functions:

select p.*
from (select p.*, count(*) filter (where p.city = 'Boston') as cnt
      from person p
     ) p
where (cnt > 0 and p.city = 'Boston') or
      (cnt = 0 and p.city is null);

Solution 2:[2]

try like below by using subquery

select * from person
where 
   city='chicago' or 
  ( city is null and 
    1!=( select count(*) from person where city='chicago'  )
  )

demo link

ID  NAME    CITY
3   Ralph   


select * from person
where 
   city='Boston' or 
  ( city is null and 
    1!=( select count(*) from person where city='Boston'  )
  )

result using boston

    ID  NAME    CITY
    2   Mike    Boston

Demo using boston

Solution 3:[3]

When you have multiple override columns then no good SQL solution will work. Under the assumption that you have only one column - like in the proposed example column city only - then you can try either of these queries:

SELECT
    IFNULL(person.id, person_override.id) AS id,
    IFNULL(person.name, person_override.name) AS name,
    IFNULL(person.city, person_override.city) AS city
FROM person AS person_override
LEFT JOIN person ON person.city = :city
WHERE person_override.city IS NULL;

Output for Boston is: | id | name | city | |----|------|--------| | 3 | Mike | Boston |

Output for Chicago is: | id | name | city | |----|-------|--------| | 3 | Ralph | |

Note that you won't be able to process multiple entries at the same time but it should satisfy the illustrated example.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Gordon Linoff
Solution 2 Zaynul Abadin Tuhin
Solution 3 Khoubeib Bouthour

Related Questions