sqlite - 返回一个空行，其中包含具有 NULL 值的必要列

Question

我需要将以下表达式转换为 SQLite，知道如何执行此操作吗？

IF EXISTS (SELECT [ID] FROM [USERS] WHERE [ID]=1)
SELECT [ID], [NAME], [CITY] FROM [USERS] WHERE [ID]=1
ELSE   
SELECT NULL AS ID, NULL AS NAME, NULL AS CITY

UNION ALL

IF EXISTS (SELECT [ID] FROM [USERS] WHERE [ID]=2)
SELECT [ID], [NAME], [CITY] FROM [USERS] WHERE [ID]=2
ELSE   
SELECT NULL AS ID, NULL AS NAME, NULL AS CITY

换句话说，当条件不满足条件时，我需要返回一个空行，其中包含具有 NULL 值的必要列。

Answer 1

您可以创建一个包含所有所需 ID 的虚拟表，然后使用左 outer join获取匹配的Users记录值或NULL:

SELECT Users.*
FROM (SELECT 1 AS ID
      UNION ALL
      SELECT 2) AS IDList
LEFT JOIN Users ON IDList.ID = Users.ID