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

1. 建表

testA

CREATE TABLE testA (
	Id int ( 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, '小花' );
	

testB

create TABLE testB (
	Id int ( 10 ) PRIMARY KEY,
	age int ( 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 );

两表如下：

2. on…and

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

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

on 后边加 and a.id = 1

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

on 后边加 and b.id = 1

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

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

SELECT
	* 
FROM
	testA a
LEFT 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 a
LEFT JOIN 
	testB b 
ON a.id = b.id 
and b.id > 1

where b.id =2

以上sql分解一下：首先看

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

然后再加上where b.id =2

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