SQL查询：保留IN子句顺序并为不匹配项返回NULL

现有Person表，结构及数据如下：

需要根据一组(name, phone)配对查询匹配的id，要求：

• 严格保留查询条件中的顺序
• 不匹配的配对返回null或指定默认值

例如查询条件为('Name2', 2345), ('NonExistingName', 34543), ('Name1', 1234)时，期望结果为[2, null, 1]。

原使用IN子句的写法无法满足需求：

SELECT id
FROM Person
WHERE (name, phone) in (('Name2', 2345),
                        ('NonExistingName', 34543),
                        ('Name1', 1234));

该语句返回结果不保留输入顺序，且不会返回无匹配的项。

核心思路是将查询条件构造成带顺序标识的临时数据集，再与Person表做左连接，从而同时满足顺序保留与空值返回的要求。

1. MySQL 实现 #

通过UNION ALL构造带序号的临时表，再执行左连接：

SELECT p.id
FROM (
    SELECT 1 AS seq, 'Name2' AS name, '2345' AS phone
    UNION ALL
    SELECT 2 AS seq, 'NonExistingName' AS name, '34543' AS phone
    UNION ALL
    SELECT 3 AS seq, 'Name1' AS name, '1234' AS phone
) AS conditions
LEFT JOIN Person p ON conditions.name = p.name AND conditions.phone = p.phone
ORDER BY conditions.seq;

2. PostgreSQL 实现 #

直接使用VALUES子句构造带序号的数据集：

SELECT p.id
FROM (
    VALUES
        (1, 'Name2', '2345'),
        (2, 'NonExistingName', '34543'),
        (3, 'Name1', '1234')
) AS conditions(seq, name, phone)
LEFT JOIN Person p ON conditions.name = p.name AND conditions.phone = p.phone
ORDER BY conditions.seq;

3. SQL Server 实现 #

同样支持VALUES子句构造临时数据集：

SELECT p.id
FROM (
    VALUES
        (1, 'Name2', '2345'),
        (2, 'NonExistingName', '34543'),
        (3, 'Name1', '1234')
) AS conditions(seq, name, phone)
LEFT JOIN Person p ON conditions.name = p.name AND conditions.phone = p.phone
ORDER BY conditions.seq;

补充说明 #

• 临时数据集中的seq字段用于标记查询条件的顺序，最终通过ORDER BY seq保证结果顺序与输入一致
• 左连接（LEFT JOIN）确保所有条件项都会出现在结果中，无匹配项对应的p.id会返回null
• 若需将null替换为默认值（比如0），可使用COALESCE(p.id, 0)替代p.id

内容的提问来源于stack exchange，提问作者Karan Dhingra