多表关联查询时，返回主表全部数据

1、这里主表有8条数据

image.png

2、错误写法
由于表连接后紧接where条件过滤，所以主表数据没有全部显示。

SELECT
    a.rule_id,
    a.rule_name,
    a.all_day_check,
    a.in_use,
    a.white_list_in_use,
    a.create_time,
    a.creator_id,
    a.creator_name,
    a.updater_id,
    a.updater_name,
    a.update_time,
    b.rule_detail_id,
    b.time,
    b.time_unit,
    b.role_id,
    b.role_name,
    b.staff_ids,
    c.control_time_id,
    c.monday,
    c.tuesday,
    c.wednesday,
    c.thursday,
    c.friday,
    c.saturday,
    c.sunday,
    c.control_start_time,
    c.control_end_time,
    d.white_list_id,
    d.white_list_name,
    e.relation_id,
    e.group_id
FROM
    group_chat_quanlity_control_rule a
left JOIN group_chat_quanlity_control_rule_detail b ON a.rule_id = b.rule_id
left JOIN group_chat_quanlity_control_time c ON a.rule_id = c.rule_id
left JOIN group_chat_quanlity_control_white_list d ON a.rule_id = d.rule_id
left JOIN group_chat_control_rule_relation e ON a.rule_id = e.rule_id
-- 这里使用where条件，主表数据不会全部显示，需要换成AND
where 
1=1
AND a.in_use = 0
AND a.is_deleted = 0
AND b.is_deleted = 0
AND c.is_deleted = 0
AND d.is_deleted = 0
AND e.is_deleted = 0；

结果：只查出3条主表数据

image.png

3、正确写法

SELECT
    a.rule_id,
    a.rule_name,
    a.all_day_check,
    a.in_use,
    a.white_list_in_use,
    a.create_time,
    a.creator_id,
    a.creator_name,
    a.updater_id,
    a.updater_name,
    a.update_time,
    b.rule_detail_id,
    b.time,
    b.time_unit,
    b.role_id,
    b.role_name,
    b.staff_ids,
    c.control_time_id,
    c.monday,
    c.tuesday,
    c.wednesday,
    c.thursday,
    c.friday,
    c.saturday,
    c.sunday,
    c.control_start_time,
    c.control_end_time,
    d.white_list_id,
    d.white_list_name,
    e.relation_id,
    e.group_id
FROM
    group_chat_quanlity_control_rule a
left JOIN group_chat_quanlity_control_rule_detail b ON a.rule_id = b.rule_id
left JOIN group_chat_quanlity_control_time c ON a.rule_id = c.rule_id
left JOIN group_chat_quanlity_control_white_list d ON a.rule_id = d.rule_id
left JOIN group_chat_control_rule_relation e ON a.rule_id = e.rule_id
AND a.is_deleted = 0
AND b.is_deleted = 0
AND c.is_deleted = 0
AND d.is_deleted = 0
AND e.is_deleted = 0
where 
1=1
 and a.in_use = 0;

结果：主表数据全部查出

image.png