为何两段仅连接顺序/起始表不同的SQL内连接脚本结果不同？

两段SQL仅调整了连接顺序和起始表，且都使用内连接，但执行后UNION的结果行数不同，原以为结果应一致。

两段SQL脚本如下：

SELECT
    ing.ingredientname,
    me.measurementdescription,
    'default' AS measurement_type
FROM
    recipesexample.ingredients ing
    JOIN recipesexample.measurements me ON me.measureamountid = ing.measureamountid
UNION
SELECT
    ing.ingredientname,
    me.measurementdescription,
    'recipe' AS measurement_type
FROM
    recipesexample.ingredients ing
    JOIN recipesexample.measurements me ON me.measureamountid = ing.measureamountid
    JOIN recipesexample.recipe_ingredients ri ON ri.ingredientid = ing.ingredientid;

SELECT
    ing.ingredientname,
    me.measurementdescription,
    'default' AS measurement_type
FROM recipesexample.measurements me
    JOIN recipesexample.ingredients ing ON me.measureamountid = ing.measureamountid
UNION
SELECT
    ing.ingredientname,
    me.measurementdescription,
    'recipe' AS measurement_type
FROM recipesexample.measurements me
    JOIN recipesexample.recipe_ingredients ri ON me.measureamountid = ri.measureamountid
    JOIN recipesexample.ingredients ing ON ing.ingredientid = ri.ingredientid;

核心差异出现在两段SQL中带'recipe'的第二个SELECT子查询，连接逻辑的本质不同导致结果集不一样：

• 第一个查询的第二个子查询：
  先通过measureamountid关联ingredients和measurements，再通过ingredientid关联recipe_ingredients。这意味着只要某个食材在recipe_ingredients中有记录（不管该记录的measureamountid是否和measurements匹配），且该食材本身和measurements的measureamountid匹配，就会被纳入结果。

• 第二个查询的第二个子查询：
  先通过measureamountid关联measurements和recipe_ingredients，再通过ingredientid关联ingredients。这要求recipe_ingredients的measureamountid必须在measurements中有对应记录，同时该食材存在于ingredients中才会被保留。

举个实际场景例子：
假设存在食材ing1，其measureamountid为1（对应measurements中的me1）；同时recipe_ingredients中有一条记录，ingredientid是ing1，但measureamountid是2（measurements中无此ID）。

• 第一个查询的第二个子查询会包含这条记录（因为ing1和me1匹配，且ri和ing1匹配）。
• 第二个查询的第二个子查询会过滤掉这条记录（因为me找不到measureamountid=2的记录，内连接直接排除）。

加上UNION的去重特性，两个子查询结果集的差异最终导致整体行数不同。

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