sql join中on条件后接and和where

目录

场景1：left join + on a.xx = b.xx and a.xx2 = 'aa'

场景2：left join + on a.xx = b.xx and b.xx2 = 'aa'

场景3：left join + on a.xx = b.xx where b.xx2 = 'aa'

场景4：inner join + on a.xx = b.xx where a.xx2 = 'aa'

场景5：inner join + on a.xx = b.xx and  a.xx2 = 'aa'

场景6：inner join + on a.xx = b.xx  and  b.xx2 = 'aa'

总结

场景1：left join + on a.xx = b.xx and a.xx2 = 'aa'

场景2：left join + on a.xx = b.xx and b.xx2 = 'aa'

场景3：left join + on a.xx = b.xx where b.xx2 = 'aa'

场景4：inner join + on a.xx = b.xx where a.xx2 = 'aa'

场景5：inner join + on a.xx = b.xx and  a.xx2 = 'aa'

场景6：inner join + on a.xx = b.xx  and  b.xx2 = 'aa'

总结

1、where 条件可以理解为，关联后，数据作为一个整体的过滤条件

2、on 后面的and 条件为关联条件，如果是left join，则不对主表记录行数产生影响；如果是inner join则，效果等同于where条件