Return a value if no rows are found SQL

What actually happens when you run a SQL query that doesn’t return any rows? For example, think of a WHERE clause with the condition 1=2, which can never be fulfilled and therefore rows can never be returned.

First, let’s look at a few examples that should show you what you can expect as a return value for different scenarios.

SELECT *
FROM DUAL
WHERE 1=1
-- Return: X

SELECT *
FROM DUAL
WHERE 1=2
-- Return: - (nicht NULL!)

SELECT NVL(DUMMY,1)
FROM DUAL
WHERE 1=2
-- Return: - (nicht NULL!)

SELECT COUNT(*)
FROM DUAL
WHERE 1=2
-- Return: 0

SELECT MAX(dummy)
FROM DUAL
WHERE 1=2
-- Return: NULL

NVL and NVL2

As you saw in the above example, you cannot use NVL and NVL2 because no rows are returned. The use of NVL and NVL2 only makes sense if rows are returned where not every column value has content. This can then be modified or changed using these functions. For our problem, however, NVL and NLV2 can safely be ruled out here. More information about the correct use as well as many tricks and tips can be found in the Article about NVL and NVL2.

MAX/MIN in conjunction with NVL or COALESCE

One trick to get a row returned is to use the MAX or MIN function in conjunction with MAX or MIN. The MAX or MIN functions always return a row value, provided this is also NULL. With MAX or MIN, at least one line is always returned. Now you can change this value with the functions NVL, NVL2 or COALESCE. This is a quick process to respond to queries that would not return any rows.

Below are two examples to help you understand how the concept works. As you can see, at least one value is now returned.

SELECT NVL(MAX(dummy), 'Z') 
FROM DUAL
WHERE 1=2
-- Return: Z

SELECT COALESCE(MAX(dummy), 'Z') 
FROM DUAL
WHERE 1=2
-- Return: Z

CASE and EXISTS

Another possibility is the query with an IF clause. To do this, you use the EXISTS function and check whether there is even a line that would come back. The first step is to check whether a row exists in your query. If this is the case, you can output the lines. However, if there is no line, you can output an alternative via the ELSE branch and thus always get a line displayed.

SELECT CASE
    WHEN EXISTS 
        (SELECT 1 
         FROM DUAL
         WHERE 1=2)
    THEN 
        (SELECT * 
         FROM DUAL
         WHERE 1=2)
    ELSE 'Z'
    END
FROM DUAL
-- Return: Z

CASE and COUNT

Another possibility is the CASE statement in conjunction with the COUNT function. As you have already seen above, COUNT gives you the number of rows returned and can therefore even give you the number 0 (if no row is returned). If COUNT is a positive number, the values of the query are returned. If the number is 0, on the other hand, the alternative path should be selected, which means that one line is returned as the return value.

SELECT CASE
    WHEN 
        (SELECT COUNT(*)
         FROM DUAL
         WHERE 1=2) <> 0
    THEN 
        (SELECT * 
         FROM DUAL
         WHERE 1=2)
    ELSE 'Z'
    END
FROM DUAL
-- Return: Z

You can find more tips and tricks for SQL Developer as well as for SQL and databases on the overview page.