如果找不到记录，则返回一个值 [英] Return a value if no record is found

问题描述

我有一个简单的声明有效:

I have this simple statement that works:

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

如果该号码在表中的任何地方都不存在，则它将失败.我想在此语句中添加一些内容:
如果找不到记录，则返回NULL表示没有行.

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.

有什么建议吗?

推荐答案

将查询封装在子查询中，以将无行"转换为NULL值.

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

我使用 PostgreSQL 对此进行了测试和验证， SQL Server 和 MySQL .也可以与 SQLite 一起使用.

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;

在 Oracle ，您必须像这样从虚拟1行表DUAL中进行选择:

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

出于兼容性原因，您可以在MySQL中执行相同的操作，但不必这样做.
在 Firebird :

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;

对于 > DB2 (例如肖恩评论):

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

用UNION ALL

替代

Alternative with UNION ALL

SELECT id FROM tbl WHERE id = 9823474
UNION  ALL
SELECT NULL -- FROM DUAL  -- for Oracle
FETCH FIRST 1 ROW ONLY;

标准SQL，但我仅使用Postgres对此进行了测试，其评估结果如下:
如果在第一个SELECT中找到一行，则将其返回.一旦发现LIMIT 1(FETCH FIRST 1 ROW ONLY)，Postgres就会停止寻找更多行.
仅当第一个SELECT不返回任何内容时，才执行第二个SELECT. NULL值的数据类型由tbl.id的数据类型自动确定.

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.

关于LIMIT子句:

• 用于LIMIT标准化的ANSI/ISO计划?

这篇关于如果找不到记录，则返回一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！