Return a value if no record is found

Question

I have this simple statement that works:

SELECT idnumber FROM dbo.database WHERE number = '9823474'

If the number does not exist anywhere in the table, it fails. I would like to add something to this statement that says:
IF NO RECORD IS FOUND RETURN NULL INSTEAD OF NO ROW.

Any suggestions?

Bailee

Give it that value in what row? There are no rows in which to give it a value. – Eaglet

"It fails". What is "it fails"? It simply returns no rows. That's "not failing", in my opinion... – Democratic

Answer 1

Encapsulate the query in a sub-query to transform "no row" to a null value.

I tested and verified this with PostgreSQL, SQLite, SQL Server, and MySQL.

SELECT (SELECT id FROM tbl WHERE id = 9823474) AS id;

In Oracle you have to select from the dummy 1-row table DUAL:

SELECT (SELECT id FROM tbl WHERE id = 9823474) AS id FROM DUAL;

You can do the same in MySQL for compatibility reasons, but you don't have to.
Similar in Firebird:

SELECT (SELECT id FROM tbl WHERE id = 9823474) AS id FROM RDB$DATABASE;

This does it for DB2 (like Sean commented):

SELECT (SELECT id FROM tbl WHERE id = 9823474) AS id FROM SYSIBM.SYSDUMMY1;

Ectomere

Works well in Firebird. Thanks for doing the research. – Depurate

I do not understand why sub-query encapsulation works. – Gyrostat

@8.8.8.8: Because "no row" from a subquery is converted to a NULL value in the SELECT list. It's a logic necessity. – Ectomere

I'm surprised there isn't a function to do this. But this definitely works – Angelicaangelico

@chrismarx: If your query returns no row (nothing at all), there is no place for a function to catch that. So we need an outer SELECT - and then we don't even need a function. – Ectomere

This works in DB2 as well, provided you substitute DUAL with SYSIBM.SYSDUMMY1. – Numbskull

What if I want to return 0 instead of null when no row is found? – Akins

@MohitAtray: Use COALESCE. As in: SELECT COALESCE((SELECT ...), 0) AS id; And it's easy with the UNION variant. – Ectomere

Thanks for this answer!! :) Using Oracle I was looking to fetch the max number and always getting null instead of 0.00. SELECT NVL(( select max(Valor) AS VALOR from SCHEMA_NAME.TABLE_NAME where 1=1 AND acc_no=acc_no ), 0.00) INTO acc_bal FROM DUAL – Lorenlorena

Answer 2

To make it more simplier, this should work fine. If you assign this to a variable based on the datatype of your idnumber than you would be able to evaluate whether the value is null or the actual idnumber return.

SELECT ISNULL(
      (
         SELECT idnumber 
         FROM dbo.database 
         WHERE number = '9823474'
      ), NULL)

Kachine

PostgreSQL does not have the ISNULL function. – Reinke

Answer 3

Select isnull(sum(Amount),0) as Amt from BeginningBalance where CustomerID = @CustomerID
Union all
Select isnull(sum(Amount),0) as Amt from SalesOrders where CustomerID = @CustomerID
Union all
Select isnull(sum(Amount),0) as Amt from SalesInvoices where CustomerID = @CustomerID
//Data Row Result if no data is present at Beginning Balance Table
// example 

Amt
2000  // amount from sales orders
1000  // amount from sales invoices

---

// if the 1st select statement return no data use this
SELECT (select sum(Amount) from BeginningBalance 
        where CustomerID = @CustomerID) as Amt
Union all
Select sum(Amount) as Amt from SalesOrders where CustomerID = @CustomerID
Union all
Select sum(Amount) as Amt from SalesInvoices where CustomerID = @CustomerID

Result :

Amt
NULL  // amount from BeginningBalance
2000  // amount from sales orders
1000  // amount from sales invoices

Low

Answer 4

I use this for MySql

SELECT IFNULL(ColumnA,"1") AS ColumnA , COUNT(1) AS Total FROM table 
WHERE ID = 1 LIMIT 0, 1;

Indus

Answer 5

Simplest way for me was to use the good old IF THEN ELSE trick! Works with all SQL flavors.

IF EXISTS (SELECT * FROM dbItem WHERE price >= 10)
BEGIN
    SELECT * FROM dbItem WHERE price >= 10
END
ELSE
  SELECT 'No record'

Ruffian

Answer 6

Here is a standalone proof-of-concept in Oracle, which returns a real value instead of NULL

In Oracle, the "dual" table always has a column called "dummy" which contains 'X'. Therefore, the following statement will never return a row.

select * from dual where dummy='123';

So this statement will always return "NO RECORD FOUND" thanks to the nvl function

select nvl((select * from dual where dummy='123'),'NO RECORD FOUND')
value 
from dual;

but, if you really want NULL you can do this (as described above)

select (select * from dual where dummy='123') value from dual;

Of course, swap the above select statement with your own

Barnette

Answer 7

You can use COALESCE

COALESCE((SELECT idnumber FROM dbo.database WHERE number = '9823474'),0)

Replace 0 with any value that you would like to return if the select query returns no rows.

Agamic

COALESCE catches null values, but can't catch "no row". – Ectomere

@ErwinBrandstetter when dealing with single column, moving COALESCE outside the SELECT does the trick – Agamic