Return NULL value when MySQL returned an empty result set

Question

I have a query as shown below.

 select a.ab as new from tab a inner join tab b on a.ab=b.ab

When i try to run its showing.

  MySQL returned an empty result set (i.e. zero rows).

because there are no rows for the selected condition.

Actual output i need is.

 new
 ----
 NULL

So i tried to add this query below.

 select COALESCE(a.ab,'') as new from tab a inner join tab b on a.ab=b.ab

Still its giving

 MySQL returned an empty result set (i.e. zero rows).

So how it is possible to display NULL value when MYSQL returns empty set.Any help appreciated.

Retuning 0 rows meaning there is no matching record with inner join. If you still want some data use left join instead. — Abhik Chakraborty
– Abhik Chakraborty, Commented Mar 5, 2015 at 7:43
This is a suspicious requirement. Why do you think you need to do this? It points to a logical flaw in your program. You're fundamentally changing the meaning of "number of rows" in your query for a subset of scenarios. — Lightness Races in Orbit
– Lightness Races in Orbit, Commented Mar 5, 2015 at 10:30

Mahesh · Accepted Answer · 2015-03-05 07:49:46Z

8

You can just use the Outer select. Encapsulate the query with the Outer select, if you want actual NULL value from the database in case of 0 rows,

SELECT 
    (
      SELECT a.ab 
      FROM 
          tab a INNER JOIN tab b 
           ON a.ab=b.ab
    ) As New;

You can use the alternative with UNION ALL and LIMIT as,

      SELECT a.ab 
      FROM 
          tab a INNER JOIN tab b 
           ON a.ab=b.ab
      UNION ALL 
      SELECT NULL
      LIMIT 1

edited Mar 5, 2015 at 7:49

answered Mar 5, 2015 at 7:43

Mahesh

8,9422 gold badges37 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Galma88 · Accepted Answer · 2015-03-05 10:04:36Z

1

If you want that the result is null when you have 0 rows try this statement:

IF EXISTS (SELECT 1 FROM TAB AS A INNER JOIN TAB AS B ON A.AB = B.AB )
BEGIN
     SELECT A.AB AS NEW FROM TAB AS A INNER JOIN TAB AS B ON A.AB = B.AB 
END
ELSE
BEGIN
    SELECT NULL AS NEW
END

answered Mar 5, 2015 at 10:04

Galma88

2,5566 gold badges31 silver badges50 bronze badges

Comments

apadana · Accepted Answer · 2019-04-13 17:53:39Z

0

In order to return null instead of empty result, try this:

select (select a.ab as new from tab a inner join tab b on a.ab=b.ab);

If you need the result column to be named new, add a new at the end:

select (your entire mysql query) new;

Explanation: Select without from is not standard conforming SQL. Nevertheless it works in many databases. Some databases like oracle require from dual. MySQL does not require FROM DUAL if no tables are referenced (reference). When there is no rows, select without from clause will return null.

answered Apr 13, 2019 at 17:53

apadana

14.9k16 gold badges91 silver badges104 bronze badges

Collectives™ on Stack Overflow

Return NULL value when MySQL returned an empty result set

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related