如何避免MariaDB查询中的子查询重复问题？

方法1：提前关联clients表复用匹配结果 #

先完成general与clients的关联，将买家ID的匹配逻辑只执行一次，后续关联widgets和doohickeys直接复用关联后的结果，彻底消除重复子查询：

SELECT g.*, w.*, d.*
FROM general g
JOIN clients c ON g.buyer_id = c.buyer_id
JOIN widgets w ON w.widget_buyer = c.buyer_id
JOIN doohickeys d ON d.doohickey_buyer = c.buyer_id
WHERE g.date_sold > '2023-03-01'

方法2：用派生表封装核心关联逻辑 #

通过派生表先筛选出符合日期条件的销售记录及其对应客户买家ID，再基于这个结果关联其他表，逻辑分层更清晰：

SELECT g.*, w.*, d.*
FROM (
    SELECT g.*, c.buyer_id AS client_buyer_id
    FROM general g
    JOIN clients c ON g.buyer_id = c.buyer_id
    WHERE g.date_sold > '2023-03-01'
) AS filtered_general
JOIN widgets w ON w.widget_buyer = filtered_general.client_buyer_id
JOIN doohickeys d ON d.doohickey_buyer = filtered_general.client_buyer_id

方法3：修正CTE写法（解决你之前的CTE失效问题） #

你之前使用CTE失效大概率是写法错误，MariaDB 10.6完全支持CTE，正确写法是先在CTE中完成general与clients的关联，再基于CTE关联其他表：

WITH filtered_sales AS (
    SELECT g.*, c.buyer_id AS client_buyer_id
    FROM general g
    JOIN clients c ON g.buyer_id = c.buyer_id
    WHERE g.date_sold > '2023-03-01'
)
SELECT fs.*, w.*, d.*
FROM filtered_sales fs
JOIN widgets w ON w.widget_buyer = fs.client_buyer_id
JOIN doohickeys d ON d.doohickey_buyer = fs.client_buyer_id

针对左连接空值问题的优化 #

如果需要保留general中无对应客户的记录但又要避免空值行，可以将JOIN改为LEFT JOIN并添加非空过滤：

SELECT g.*, w.*, d.*
FROM general g
LEFT JOIN clients c ON g.buyer_id = c.buyer_id
LEFT JOIN widgets w ON w.widget_buyer = c.buyer_id
LEFT JOIN doohickeys d ON d.doohickey_buyer = c.buyer_id
WHERE g.date_sold > '2023-03-01'
AND c.buyer_id IS NOT NULL
AND w.widget_id IS NOT NULL
AND d.doohickey_id IS NOT NULL

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