sql - 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?

Answer 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:

• ANSI/ISO plans for LIMIT standardization?