MySQL自连接查询：找出主语言ES存在但其他语言缺失的条目

首先，你的核心需求是找出在es语言中存在的alias，但在其他非es语言中缺失的（alias, lang）组合。你之前尝试的SQL逻辑存在偏差，B.alias NOT IN (A.alias)的关联条件会错误匹配不同alias的记录，完全偏离了目标需求。

正确实现步骤 #

我们可以通过以下逻辑来精准获取结果：

1. 提取所有在es语言中存在的alias集合
2. 提取所有非es的语言类型集合
3. 生成这两个集合的笛卡尔积（即每个es存在的alias与所有非es语言的组合）
4. 从笛卡尔积中排除表中已经存在的（alias, lang）组合，剩下的就是缺失的目标记录

兼容多数数据库的SQL语句 #

-- 生成所有应存在的组合，再排除已有的记录
SELECT 
    es_aliases.alias, 
    non_es_langs.lang
FROM 
    (SELECT DISTINCT alias FROM my_table WHERE lang = 'es') AS es_aliases
CROSS JOIN 
    (SELECT DISTINCT lang FROM my_table WHERE lang != 'es') AS non_es_langs
LEFT JOIN 
    my_table t ON es_aliases.alias = t.alias AND non_es_langs.lang = t.lang
WHERE 
    t.id IS NULL
ORDER BY 
    es_aliases.alias, non_es_langs.lang;

语句细节解释 #

• (SELECT DISTINCT alias FROM my_table WHERE lang = 'es') AS es_aliases：获取所有在es语言中存在的alias，比如你的数据里的word1、word2、word3
• (SELECT DISTINCT lang FROM my_table WHERE lang != 'es') AS non_es_langs：获取所有非es的语言类型，这里是en、fr
• CROSS JOIN：将上述两个集合做笛卡尔积，得到所有理论上应该存在的组合：(word1,en)、(word1,fr)、(word2,en)、(word2,fr)、(word3,en)、(word3,fr)
• LEFT JOIN ... WHERE t.id IS NULL：过滤掉表中已经存在的组合，剩下的就是缺失的记录，正好匹配你期望的结果

验证结果 #

执行上述SQL后，会输出你想要的结果：

alias lang
word1 fr
word2 en
word3 en
word3 fr

简洁写法（支持EXCEPT语法的数据库） #

如果你的数据库（如PostgreSQL、SQL Server）支持EXCEPT关键字，可以用更简洁的方式实现：

SELECT alias, lang
FROM (
    SELECT DISTINCT alias FROM my_table WHERE lang = 'es'
) AS es_aliases
CROSS JOIN (
    SELECT DISTINCT lang FROM my_table WHERE lang != 'es'
) AS non_es_langs
EXCEPT
SELECT alias, lang FROM my_table;

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