SQL如何生成store、gender、age_group三表ID的全量组合数据

要得到store、gender、age_group三张表ID字段的所有排列组合，标准CROSS JOIN（交叉连接）是语义最清晰、性能最优的通用实现方案，交叉连接的作用就是返回多张表的所有行组合，也就是数学上的笛卡尔积，完全匹配你的需求。

基础实现代码 #

直接对三张表做交叉连接即可，不需要写任何关联条件：

SELECT
  s.store_id,
  g.gender_id,
  a.age_group_id
FROM store s
CROSS JOIN gender g
CROSS JOIN age_group a;

按照你给出的示例ID取值（三张表各有3个不同ID），这段代码会返回333=27行结果，刚好覆盖所有ID组合，和你给出的示例表结构完全一致。

优化写法（避免冗余数据） #

如果原表中存在ID重复的脏数据，可以先对每张表的ID做去重，避免结果出现重复行：

SELECT
  s.store_id,
  g.gender_id,
  a.age_group_id
FROM (SELECT DISTINCT store_id FROM store) s
CROSS JOIN (SELECT DISTINCT gender_id FROM gender) g
CROSS JOIN (SELECT DISTINCT age_group_id FROM age_group) a;

方案优势 #

• 语义清晰：CROSS JOIN是SQL标准中专门用于生成全组合的语法，后续维护代码的开发人员可以直接识别逻辑，不会和普通关联查询混淆
• 性能优秀：主流数据库对标准交叉连接都有专门的执行计划优化，只要ID字段建了主键或索引，查询速度极快
• 兼容性强：写法符合ANSI SQL标准，支持MySQL、PostgreSQL、SQL Server、Hive、Spark SQL等几乎所有SQL引擎

注意：不推荐使用FROM store, gender, age_group这种省略JOIN关键字的老旧写法，这种写法语义模糊，很容易因为漏写WHERE关联条件意外生成笛卡尔积，提高维护成本和出错概率。

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