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.

Answer 3

Late reply but I think this is the easiest method:

SELECT
   IFNULL((SELECT your query), NULL)

Answer 4

Use a UNION with a NOT EXISTS(original where clause)

select col1
from mytable
where <some condition>
union
select null
where not exists (
    select *  from mytable
    where <some condition>)

Answer 5

You can use COALESCE for example:

SELECT COALESCE(Field1,NULL) AS Field1 FROM Table1

Edit 1: sorry i mistake with return field as null not result set,for result set return as null use Union and Exist Function like this:

SELECT NULL AS Field1 FROM Table1 WHERE not EXISTS(SELECT Field1 FROM Table1 WHERE Field2>0)
UNION
SELECT Field1 FROM Table1 WHERE Field2>0