CODEWITHSUNDEEP
mysqlsql
silkfire
silkfire

Reputation: 25945

How to return NULL when result is empty?

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.

Upvotes: 28

Views: 60424

Answers (5)

GolezTrol
GolezTrol

Reputation: 116110

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 was mandatory until 23ai. 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.

Upvotes: 40

Griffin
Griffin

Reputation: 305

Late reply but I think this is the easiest method:

SELECT
   IFNULL((SELECT your query), NULL)

Upvotes: 8

Mehdi Yeganeh
Mehdi Yeganeh

Reputation: 2129

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

Upvotes: 0

Bohemian
Bohemian

Reputation: 424993

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>)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

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.

Upvotes: 19

Related Questions