Return a value if no record is found

by

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:

More Related Contents:

  1. How do I UPDATE from a SELECT in SQL Server?
  2. Select rows which are not present in other table
  3. SQL is null and = null [duplicate]
  4. SQL join: selecting the last records in a one-to-many relationship
  5. Create unique constraint with null columns
  6. COUNT(*) vs. COUNT(1) vs. COUNT(pk): which is better? [duplicate]
  7. Best way to check for “empty or null value”
  8. What is the difference between using a cross join and putting a comma between the two tables?
  9. SQL QUERY replace NULL value in a row with a value from the previous known value
  10. why is null not equal to null false
  11. Sorting null values after all others, except special
  12. Select TOP X (or bottom) percent for numeric values in MySQL
  13. How to kill a running SELECT statement
  14. How do I select all the columns from a table, plus additional columns like ROWNUM?
  15. Sort NULL values to the end of a table
  16. DATEDIFF function in Oracle [duplicate]
  17. Why do NULL values come first when ordering DESC in a PostgreSQL query?
  18. Best way to do nested case statement logic in SQL Server
  19. How to select multiple rows filled with constants?
  20. How to select only the first rows for each unique value of a column?
  21. Why postgres returns unordered data in select query, after updation of row?
  22. SQL column reference “id” is ambiguous
  23. IN Clause with NULL or IS NULL
  24. How to select records without duplicate on just one field in SQL?
  25. how does SELECT TOP works when no order by is specified?
  26. SQL: Two select statements in one query
  27. Check if a parameter is null or empty in a stored procedure
  28. Check if two “select”s are equivalent
  29. SQL Inner Join On Null Values
  30. Select rows where column value has changed

Leave a Comment