Oracle SQL通过子查询实现同列双值匹配的多表关联查询

你的查询没有返回正确结果，核心是两个逻辑错误：

1. 最后的EXISTS子查询没有和当前查询的鸡尾酒做关联，它只判断整张Ingredients表中是否存在产地为Cuba的原料——只要表中有任意一条Cuba产地的原料，这个条件对所有鸡尾酒都成立，完全起不到过滤作用。
2. 你写的IN条件只判断了当前关联到的原料是西班牙产地，没有保证「同一款鸡尾酒同时用到两种产地原料」这个要求，只要酒用到了西班牙原料就会被返回，自然会把Mai Tai、Brooklyn Lamp这些只用到西班牙产柠檬汁/西柚、没用到古巴朗姆的酒也查出来。

另外补充：没有通配符的场景不要用LIKE做精确匹配，直接用=即可，性能更好。

下面给三种常用的实现方式，都能得到你预期的结果RID=1, Cocktail=Daiquiri：

写法1：双表关联（最适合新手理解） #

分别给西班牙产地、古巴产地的原料各做一次关联，只有两种原料都能关联到的鸡尾酒才会被返回，逻辑最直观：

SELECT r.RID, r.Cocktail
FROM Recipe r
-- 关联西班牙产地原料
JOIN Mix m_es ON r.RID = m_es.RID
JOIN Ingredients i_es ON m_es.Ing_ID = i_es.Ing_ID 
  AND i_es.From_Where = 'Spain'
-- 关联古巴产地原料
JOIN Mix m_cu ON r.RID = m_cu.RID
JOIN Ingredients i_cu ON m_cu.Ing_ID = i_cu.Ing_ID 
  AND i_cu.From_Where = 'Cuba'
WHERE r.Made_by = 'Otto';

这种写法不需要加DISTINCT，关联逻辑天然会过滤重复行。

写法2：分组聚合（通用性最强） #

先筛出Otto制作的酒用到的西班牙、古巴原料，按鸡尾酒分组后，统计组内不同产地的数量，数量为2就说明两种产地的原料都存在：

SELECT r.RID, r.Cocktail
FROM Recipe r
JOIN Mix m ON r.RID = m.RID
JOIN Ingredients i ON m.Ing_ID = i.Ing_ID
WHERE r.Made_by = 'Otto'
  AND i.From_Where IN ('Spain', 'Cuba')
GROUP BY r.RID, r.Cocktail
HAVING COUNT(DISTINCT i.From_Where) = 2;

如果后续需要同时匹配3种、4种更多产地的原料，只要修改IN里的取值、把HAVING后的数字改成对应数量即可，扩展非常方便。

写法3：修正你原来的EXISTS逻辑 #

如果想沿用你原来写的EXISTS思路，只要给两个子查询都加上和当前鸡尾酒RID的关联即可：

SELECT DISTINCT r.RID, r.Cocktail
FROM Recipe r
WHERE r.Made_by = 'Otto'
  AND EXISTS (
    SELECT 1
    FROM Mix m
    JOIN Ingredients i ON m.Ing_ID = i.Ing_ID
    WHERE m.RID = r.RID AND i.From_Where = 'Spain'
  )
  AND EXISTS (
    SELECT 1
    FROM Mix m
    JOIN Ingredients i ON m.Ing_ID = i.Ing_ID
    WHERE m.RID = r.RID AND i.From_Where = 'Cuba'
  );

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