left join后边跟on...and 和where...and的区别

left join后边跟on...and 和where...and的区别

文章目录

​​1. 建表​​​​2. on...and​​​​3. where...and​​

1. 建表

testA

CREATE TABLE testA ( Id int ( 10 ) PRIMARY KEY, NAME VARCHAR ( 10 ))

INSERT INTO testAVALUES ( 1, '小黄' );INSERT INTO testAVALUES ( 2, '小绿' );INSERT INTO testAVALUES ( 3, '小白' );INSERT INTO testAVALUES ( 4, '小黑' );INSERT INTO testAVALUES ( 5, '小花' );

testB

create TABLE testB ( Id int ( 10 ) PRIMARY KEY, age int ( 10 ))

INSERT INTO testBVALUES ( 1, 10 );INSERT INTO testBVALUES ( 2, 11 );INSERT INTO testBVALUES ( 3, 12 );INSERT INTO testBVALUES ( 4, 13 );

两表如下：

2. on…and

先来一个常用的​​left join...on​​

SELECT * FROM testA aLEFT JOIN testB b ON a.id = b.id

​​on​​ 后边加 ​​and a.id = 1​​

SELECT * FROM testA aLEFT JOIN testB b ON a.id = b.id and a.id = 1 # 连接限制条件 a.id = 1

​​on​​ 后边加 ​​and b.id = 1​​

SELECT * FROM testA aLEFT JOIN testB b ON a.id = b.id and b.id = 1

​​on​​ 后边加 ​​and b.age = a.id​​

SELECT * FROM testA aLEFT JOIN testB b ON a.id = b.id and b.age = a.id

on 后的 and 有两表关联的字段： b.age 和 a.id，由于b.age 没有与 a.id相等的数据

所以​​a​​表全部显示，​​b​​表内容为空

3. where…and

SELECT * FROM testA aLEFT JOIN testB b ON a.id = b.id and b.id > 1where b.id =2

以上sql分解一下：首先看

SELECT * FROM testA aLEFT JOIN testB b ON a.id = b.id and b.id > 1

然后再加上​​where b.id =2​​

由此可见，on…and是连接。where…and 是过滤！

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。