CODEWITHSUNDEEP
sqlselectnullrecord
user1042304
user1042304

Reputation: 649

Return a value if no record is found

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?

Upvotes: 60

Views: 134945

Answers (7)

DAB
DAB

Reputation: 1873

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

Upvotes: 0

Aparna
Aparna

Reputation: 612

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.

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656716

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;

Upvotes: 96

Fandango68
Fandango68

Reputation: 4868

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'

Upvotes: 1

Irfan Ashraf
Irfan Ashraf

Reputation: 2460

I use this for MySql

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

Upvotes: 1

Sam Kort
Sam Kort

Reputation: 133

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)

Upvotes: 10

Wilson
Wilson

Reputation: 51

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

Upvotes: 5

Related Questions