摘自：http://cqujsjcyj.iteye.com/blog/952302

　　　http://jackleechina.iteye.com/blog/2087856

 

 

运行平台：PostgreSQL
目的：比较join......on 后面的and 和where的区别 

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

1）建tab1表

-- ----------------------------
-- Table structure for tab1
-- ----------------------------
DROP TABLE IF EXISTS "public"."tab1";
CREATE TABLE "public"."tab1" (
"id" int4 NOT NULL,
"size" int4
)
WITH (OIDS=FALSE)

;

 

　　添加数据

-- ----------------------------
-- Records of tab1
-- ----------------------------
INSERT INTO "public"."tab1" VALUES ('1', '10');
INSERT INTO "public"."tab1" VALUES ('2', '20');
INSERT INTO "public"."tab1" VALUES ('3', '30');

-- ----------------------------
-- Alter Sequences Owned By
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table tab1
-- ----------------------------
ALTER TABLE "public"."tab1" ADD PRIMARY KEY ("id");

 

2）建tab2表

 

-- ----------------------------
-- Table structure for tab2
-- ----------------------------
DROP TABLE IF EXISTS "public"."tab2";
CREATE TABLE "public"."tab2" (
"size" int4,
"name" varchar(32) COLLATE "default"
)
WITH (OIDS=FALSE)

;

 

　　添加数据

-- ----------------------------
-- Records of tab2
-- ----------------------------
INSERT INTO "public"."tab2" VALUES ('10', 'AAA');
INSERT INTO "public"."tab2" VALUES ('20', 'BBB');
INSERT INTO "public"."tab2" VALUES ('20', 'CCC');

 

3）比较下面的运行结果

两条SQL: 
1、select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA’ 
2、select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’) 

 

第一条SQL的过程： 

1、中间表 
on条件: 
tab1.size = tab2.size 

tab1.id    tab1.size    tab2.size     tab2.name 

1               10                   10               AAA 

2              20                     20             BBB 

2             20                      20               CCC 

3             30                    (null)              (null) 

2、再对中间表过滤 
where 条件： 
tab2.name=’AAA’ 

tab1.id       tab1.size        tab2.size     tab2.name 

1                  10                  10              AAA 


第二条SQL的过程： 

1、中间表 
on条件: 
tab1.size = tab2.size and tab2.name=’AAA’ 
(条件不为真也会返回左表中的记录) 

tab1.id      tab1.size         tab2.size       tab2.name 

1               10                     10                   AAA 

2               20                   (null)               (null) 

3               30                    (null)                 (null) 

 

 

 

4）结论 
4.1) where 是在两个表join完成后，再附上where条件。 


4.2) 
而 and 则是在表连接前过滤A表或B表里面哪些记录符合连接条件，同时会兼顾是left join还是right join。即 
假如是左连接的话①如果左边表的某条记录不符合连接条件，那么它不进行连接，但是仍然留在结果集中（此时右边部分的连接结果为NULL）。

②如果右边表的某条记录不符合连接条件，那么它不进行连接。

 


4.3)建议尽量用where来过滤条件，以避免复杂的逻辑考虑。（除非在某些情况下(后接其他sql语句)，用where会报错，才用and，但要考虑是否影响正确结果。）