如何用SQL查询列表中存在及不存在于表中的邮箱？

嘿，这个需求我之前也碰到过！你原来用IN的查询只能拿到数据库里存在的邮箱记录，要把列表里不存在的也一起返回，核心就是得先把你要查的邮箱列表变成一个可查询的临时数据集，再和user_table做左连接（LEFT JOIN）——这样不管邮箱是否在表中，都会被保留下来，匹配到的会带出对应的姓名，没匹配到的姓名字段就是NULL。

下面分几种常见数据库给出具体实现：

1. PostgreSQL 或 SQL Server #

这两个数据库支持直接用VALUES子句或者数组构造临时数据集：

PostgreSQL 写法 #

WITH target_emails AS (
    -- 把目标邮箱放进数组，用unnest展开成行
    SELECT unnest(ARRAY['a@a.com', 'b@b.com', 'c@c.com']) AS email
)
SELECT 
    te.email,
    ut.first_name,
    ut.last_name
FROM target_emails te
LEFT JOIN user_table ut ON te.email = ut.email;

SQL Server 写法 #

WITH target_emails AS (
    -- 用VALUES直接构造多行数据集
    SELECT email FROM (VALUES ('a@a.com'), ('b@b.com'), ('c@c.com')) AS temp(email)
)
SELECT 
    te.email,
    ut.first_name,
    ut.last_name
FROM target_emails te
LEFT JOIN user_table ut ON te.email = ut.email;

2. MySQL #

如果是MySQL 8.0及以上版本，也可以用CTE；如果是低版本，用UNION ALL构造临时数据即可：

MySQL 通用写法（兼容所有版本） #

SELECT 
    te.email,
    ut.first_name,
    ut.last_name
FROM (
    -- 用UNION ALL拼接所有目标邮箱
    SELECT 'a@a.com' AS email UNION ALL
    SELECT 'b@b.com' AS email UNION ALL
    SELECT 'c@c.com' AS email
) te
LEFT JOIN user_table ut ON te.email = ut.email;

MySQL 8.0+ 写法（用CTE更简洁） #

WITH target_emails AS (
    SELECT 'a@a.com' AS email UNION ALL
    SELECT 'b@b.com' AS email UNION ALL
    SELECT 'c@c.com' AS email
)
SELECT 
    te.email,
    ut.first_name,
    ut.last_name
FROM target_emails te
LEFT JOIN user_table ut ON te.email = ut.email;

额外小技巧：标记邮箱状态 #

如果需要直观区分哪些邮箱存在、哪些不存在，可以加一个CASE判断字段：

-- 以PostgreSQL为例，其他数据库语法类似
WITH target_emails AS (
    SELECT unnest(ARRAY['a@a.com', 'b@b.com', 'c@c.com']) AS email
)
SELECT 
    te.email,
    ut.first_name,
    ut.last_name,
    CASE WHEN ut.email IS NOT NULL THEN '存在' ELSE '不存在' END AS email_status
FROM target_emails te
LEFT JOIN user_table ut ON te.email = ut.email;

内容的提问来源于stack exchange，提问作者Daniel Joseph Day