优化SQL查询：获取用户未投票类别对应的候选人数据

方案一：使用NOT EXISTS替代NOT IN，移除无用关联 #

$nominees = $this->model->get("
    SELECT
        nc.name AS category,
        nc.id AS categoryId,
        n.name,
        n.surname,
        n.dealership,
        n.description,
        n.id
    FROM nominee_categories nc
    JOIN nominees n ON nc.id = n.nominee_category_id
    WHERE NOT EXISTS (
        SELECT 1
        FROM votes v
        JOIN nominees n_v ON v.nominee_id = n_v.id
        WHERE n_v.nominee_category_id = nc.id
        AND v.user_id = ?
    )
", [$this->userID]);

方案二：先获取用户已投票分类集合，再筛选目标候选人 #

$nominees = $this->model->get("
    SELECT
        nc.name AS category,
        nc.id AS categoryId,
        n.name,
        n.surname,
        n.dealership,
        n.description,
        n.id
    FROM nominee_categories nc
    JOIN nominees n ON nc.id = n.nominee_category_id
    LEFT JOIN (
        SELECT DISTINCT nc.id AS voted_category_id
        FROM votes v
        JOIN nominees n_v ON v.nominee_id = n_v.id
        JOIN nominee_categories nc ON n_v.nominee_category_id = nc.id
        WHERE v.user_id = ?
    ) voted_cats ON nc.id = voted_cats.voted_category_id
    WHERE voted_cats.voted_category_id IS NULL
", [$this->userID]);

核心优化点 #

• 移除无用的users表关联：votes表已存储user_id，无需额外关联users表即可完成用户投票判断
• 删除子查询中无意义的WHERE nc.id=nc.id条件
• 将原SQL的LEFT JOIN改为JOIN：需求是获取有候选人的分类，内连nominees可直接过滤无候选人的分类，减少无效数据返回
• 方案一用NOT EXISTS替代NOT IN：性能更稳定，避免子查询结果出现NULL时NOT IN导致的无结果问题
• 方案二采用LEFT JOIN + IS NULL逻辑：筛选未投票分类的方式更直观，降低SQL理解成本

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