如果值不存在，请选择空行

Question

我有下表：

消息

id | key   | msg
-------------------
1  | ABC   | text a 
2  | CDE   | text c
3  | CDE   | text d
4  | null  | text x

如果我有key = X，我想从表中选择所有值，或者选择所有带有 null 的值(带key = null(。像这样：

A( 消息(select ... where key = CDE，密钥 CDE 存在(

id | key   | msg
-------------------
2  | CDE   | text c
3  | CDE   | text d

B( 消息(select ... where key = XYZ，密钥 XYZ 不存在(

id | key   | msg
-------------------
4  | null  | text x

Answer 1

您可以使用or：

select t.*
from t
where key = ? or
(key is null and
not exists (select 1 from t t2 where t2.key = ?)
);

Answer 2

查询 #1

SELECT * FROM Tbl WHERE Key = 'CDE' OR Key IS NULL

查询 #2

SELECT * FROM Tbl WHERE Key = 'CDE' 
UNION ALL
SELECT * FROM Tbl WHERE Key IS NULL