How to return NULL when result is empty?

Question

I have a simple query that selects one field and only one row, thus one value.

Is there any way to make it return NULL if the query results in an empty set? Instead of returning zero rows?

I think I need to use something with NOT EXISTS, THEN NULL but not certain about it.

Answer 1

select
  (Your entire current Select statement goes here) as Alias
from 
  dual

dual is a built in table with a single row that can be used for purposes like this. In Oracle this is mandatory. MySQL supports it, but you can also just select a single value without specifying a table, like so:

select
  (Your entire current Select statement goes here) as Alias

In either case you're selecting a single value. This means that:

• If your select returns one value, that value is returned.
• If your select statement returns one column, but no rows, NULL will be returned.
• If your select statement returns multiple columns and/or multiple rows, this won't work and the query fails.

Answer 2

An easy way to do this is with aggregation:

select max(col)
from t
where <your condition here>

This always returns one row. If there is no match, it returns NULL.