如何避免重复执行子查询，标记SQL查询的首次匹配类型

方法一：用CTE预缓存子查询结果 #

先通过公共表表达式（CTE）把需要用到的子查询结果提前计算并缓存，后续主查询的WHERE和CASE都直接引用这些预存结果，确保子查询只执行一次：

WITH t2_types AS (SELECT DISTINCT type FROM t2),
     t3_names AS (SELECT DISTINCT name FROM t3 WHERE ...),
     t4_ages AS (SELECT DISTINCT age FROM t4 WHERE ...)
SELECT 
  CASE
    WHEN t1.color LIKE 'color%' THEN 'match_by_color'
    WHEN EXISTS (SELECT 1 FROM t2_types WHERE type = t1.type) THEN 'match_by_type'
    WHEN EXISTS (SELECT 1 FROM t3_names WHERE name = t1.name) THEN 'match_by_name'
    WHEN EXISTS (SELECT 1 FROM t4_ages WHERE age = t1.age) THEN 'match_by_age'
    ELSE 'no_match'
  END AS match_type,
  t1.*
FROM t1
WHERE 
  t1.color LIKE 'color%'
  OR EXISTS (SELECT 1 FROM t2_types WHERE type = t1.type)
  OR EXISTS (SELECT 1 FROM t3_names WHERE name = t1.name)
  OR EXISTS (SELECT 1 FROM t4_ages WHERE age = t1.age);

这里用EXISTS替代IN，因为EXISTS找到匹配项就会停止检索，性能比IN更优；DISTINCT用来避免子查询结果里的重复值，减少后续匹配的工作量。

方法二：通过LEFT JOIN关联预查询结果 #

把每个子查询的结果作为临时表，用LEFT JOIN关联到t1，然后通过判断关联字段是否非空来确定匹配类型，同样只执行一次子查询：

SELECT 
  CASE
    WHEN t1.color LIKE 'color%' THEN 'match_by_color'
    WHEN t2.type IS NOT NULL THEN 'match_by_type'
    WHEN t3.name IS NOT NULL THEN 'match_by_name'
    WHEN t4.age IS NOT NULL THEN 'match_by_age'
    ELSE 'no_match'
  END AS match_type,
  t1.*
FROM t1
LEFT JOIN (SELECT DISTINCT type FROM t2) t2 ON t1.type = t2.type
LEFT JOIN (SELECT DISTINCT name FROM t3 WHERE ...) t3 ON t1.name = t3.name
LEFT JOIN (SELECT DISTINCT age FROM t4 WHERE ...) t4 ON t1.age = t4.age
WHERE 
  t1.color LIKE 'color%'
  OR t2.type IS NOT NULL
  OR t3.name IS NOT NULL
  OR t4.age IS NOT NULL;

注意必须加DISTINCT，否则如果t2里有多个相同的type值，会导致t1的同一条记录被多次关联输出，影响结果正确性。

内容的提问来源于stack exchange，提问作者Russell Van Curen