Different WHERE clause depending on subquery result

Question

I would like to SELECT WHERE column IS NULL or =value depending on result of subquery.

Here is an example incorrect solution that demonstrates the problem:

SELECT *
FROM table
WHERE column=(
  SELECT (CASE WHEN COUNT(*) = COUNT(COLUMN) THEN MIN(column) END)
  FROM table
)

When the subquery returns NULL the other query will return nothing because column=NULL is never true. How do I fix this?

http://sqlfiddle.com/#!9/a3968c/1 , I expect get row with name `a` and if that row wasn't present, row with name `c`. As you wrote, adding `OR column IS NULL` solves my problem. — user7810882, Jul 14 '18 at 18:51

score 1 · Accepted Answer · answered Jul 14 '18 at 18:36

From your question. just add OR column IS NULL in where clause.

You will get the subquery condition or column IS NULL data.

SELECT *
FROM table
WHERE column= (
  SELECT (CASE WHEN COUNT(*) = COUNT(COLUMN) THEN MIN(column) END)
  FROM table
) OR column IS NULL

score 1 · Answer 2 · answered Jul 14 '18 at 22:12

1

If you are only looking for one row, I would suggest:

select t.*
from table t
order by column nulls first
fetch first 1 row only;

answered Jul 14 '18 at 22:12

Gordon Linoff

2 Answers2