left join 中的条件放在on中还是where中

1、放在on中

SELECT
    `u`.`id`,
    COALESCE ( `u`.`user_real_name`, `u`.`user_nickname` ) `name`,
    `credit`
FROM
    `user` AS `u`
    LEFT JOIN `subject` AS `s` ON `u`.`id` = `s`.`user_id` and `s`.`deleted_at` IS NULL
WHERE
    `u`.`deleted_at` IS NULL 
    and `u`.`id`=4***1
GROUP BY
    `u`.`id`,
    `name`,
`credit`

image.png

2、放在where中

SELECT
    `u`.`id`,
    COALESCE ( `u`.`user_real_name`, `u`.`user_nickname` ) `name`,
    `credit`
FROM
    `user` AS `u`
    LEFT JOIN `subject` AS `s` ON `u`.`id` = `s`.`user_id`
WHERE
    `u`.`deleted_at` IS NULL 
    and `u`.`id`=4***1
        and `s`.`deleted_at` IS NULL
GROUP BY
    `u`.`id`,
    `name`,
`credit`

image.png

3、总结

放在on中表示联表时的条件，是对关联的那个表的约束；
放在where中表示，联表后的条件，是对关联后的整张表进行筛选。