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.

Answer 1

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

Answer 2

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