sql where 后面加判断_sql 表连接on后面加where和and的区别

create table testA(       Id number(10) primary key,       Name varchar(10))insert into testA values(1,'小黄');insert into testA values(2,'小绿');insert into testA values(3,'小白');insert into testA values(4,'小黑');insert into testA values(5,'小花');commitcreate table testB(       Id number(10) primary key,       age number(10))insert into testB values(1,10);insert into testB values(2,11);insert into testB values(3,12);insert into testB values(4,13);commit

on后面加and

select * from testA a left join testB b  on a.id = b.id and b.age=10

on后面加where

select * from testA a left join testB b  on a.id = b.id where b.age=10

on 是用于消除笛卡儿积的，表连接时不加on会报错，left join语句会从左表那里返回所有的行，即使在右表中没有匹配的行，on后面接and也会兼顾左连接，不管and 后面接什么内容，左表数据都会全部展示下图语句由于a.name =10在testA没有匹配数据，所以与其关联的testB也匹配不到数据。

select * from testA a left join testB b   on a.id = b.id and a.name='10'

使用where就是对连接后的结果集进行条件筛选

select * from testA a left join testB b   on a.id = b.id where a.name='10'