文章目录
1. 建表2. on...and3. where...and1. 建表
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* FROMtestA aLEFT JOIN testB b ON a.id = b.id
on
后边加and a.id = 1
SELECT* FROMtestA aLEFT JOIN testB b ON a.id = b.id and a.id = 1 # 连接限制条件 a.id = 1
on
后边加and b.id = 1
SELECT* FROMtestA aLEFT JOIN testB b ON a.id = b.id and b.id = 1
on
后边加and b.age = a.id
SELECT* FROMtestA 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* FROMtestA aLEFT JOIN testB b ON a.id = b.id and b.id > 1where b.id =2
以上sql分解一下:首先看
SELECT* FROMtestA aLEFT JOIN testB b ON a.id = b.id and b.id > 1
然后再加上where b.id =2
由此可见,on…and是连接。where…and 是过滤!