Return a value if no record is found
Solution 1
Encapsulate the query in a sub-query to transform "no row" to a NULL value.
I tested and verified this with PostgreSQL, SQL Server and MySQL. Also works with SQLite.
SELECT (SELECT id FROM tbl WHERE id = 9823474) AS id;
In Oracle you have to select from the dummy 1-row table DUAL
like this:
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;
Alternative with UNION ALL
SELECT id FROM tbl WHERE id = 9823474
UNION ALL
SELECT NULL -- FROM DUAL -- for Oracle
FETCH FIRST 1 ROW ONLY;
Standard SQL, but I only tested this with Postgres, which evaluates like this:
If a row is found in the first SELECT
, it is returned. Postgres stops looking for more rows, as soon as the first is found due to LIMIT 1
(FETCH FIRST 1 ROW ONLY
).
The second SELECT
is only even executed if the first returns nothing. The data type of the NULL value is determined by the data type of tbl.id
automatically.
About the LIMIT
clause:
Solution 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)
Solution 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
Solution 4
I use this for MySql
SELECT IFNULL(ColumnA,"1") AS ColumnA , COUNT(1) AS Total FROM table
WHERE ID = 1 LIMIT 0, 1;
Solution 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'
Related videos on Youtube
user1042304
Updated on July 09, 2022Comments
-
user1042304 almost 2 years
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?
-
Lightness Races in Orbit over 12 yearsGive it that value in what row? There are no rows in which to give it a value.
-
-
Valentin Despa about 11 yearsWorks well in Firebird. Thanks for doing the research.
-
8.8.8.8 about 9 yearsI do not understand why sub-query encapsulation works.
-
Erwin Brandstetter about 9 years@8.8.8.8: Because "no row" from a subquery is converted to a
NULL
value in theSELECT
list. It's a logic necessity. -
chrismarx almost 8 yearsI'm surprised there isn't a function to do this. But this definitely works
-
Erwin Brandstetter almost 8 years@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. -
Sean Branchaw almost 8 yearsThis works in DB2 as well, provided you substitute
DUAL
withSYSIBM.SYSDUMMY1
. -
Mohit Atray almost 5 yearsWhat if I want to return 0 instead of null when no row is found?
-
Erwin Brandstetter almost 5 years@MohitAtray: Use
COALESCE
. As in:SELECT COALESCE((SELECT ...), 0) AS id;
And it's easy with theUNION
variant. -
Max Hodges about 4 yearsPostgreSQL does not have the ISNULL function.