oracle中full join中on和where的区别

Oracle的full join中on和where的区别 

看两张表：

SELECT * FROM tmp_trmnl_flag ;

此张表中有100条记录，bcd_code字段全部为空。

SELECT * FROM TD_SETT_BCD_PROD_TMP ;

此张表里面记录很多也有bcd_code字段，当然bcd_code和prod_id字段的意义跟上表中一样。

现在我想用full join查询两张表通过prod_id和bcd_code字段关联出来的所有联接查询记录，但是要过滤掉第一张表的bcd_code为空的记录。

大家知道，第一张表的所有记录都是bcd_code为空。

首先，我们把过滤第一张表bcd_code为空的条件放在on中：

select a.prod_id prod_id1,a.bcd_code bcd_code1 ,a.sett_flag,s.prod_id prod_id2 ,s.bcd_code bcd_code2 from tmp_trmnl_flag sfull join TD_SETT_BCD_PROD_TMP a on a.bcd_code = s.bcd_code and a.prod_id = s.prod_id AND s.bcd_code IS NOT null ;

因为是全联接，所以关联出来的应该为第一张表和第二张表的记录数之和（得出这个结论的原因是刚好两张表一条记录都没关联上，至于为什么一条记录都没关联上，原因就很简单啦）。

验证一下：

create TABLE aaa_bbb AS select a.prod_id prod_id1,a.bcd_code bcd_code1 ,a.sett_flag,s.prod_id prod_id2 ,s.bcd_code bcd_code2 from tmp_trmnl_flag sfull join TD_SETT_BCD_PROD_TMP a on a.bcd_code = s.bcd_code and a.prod_id = s.prod_id AND s.bcd_code IS NOT null ;

SELECT COUNT(*) FROM aaa_bbb ;--14246521

SELECT COUNT(*) FROM TD_SETT_BCD_PROD_TMP ;--14246421

下面我们在把过滤条件放到where中去：

select a.prod_id prod_id1,a.bcd_code bcd_code1 ,a.sett_flag,s.prod_id prod_id2 ,s.bcd_code bcd_code2 from tmp_trmnl_flag sfull join TD_SETT_BCD_PROD_TMP a on a.bcd_code = s.bcd_code and a.prod_id = s.prod_id WHERE s.bcd_code IS NOT null ;

一条记录都没有。

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

在使用jion 联接查询时，on和where条件的区别如下：

1、生成临时表时，无论on中的条件是否为真，这里都会返回全表联接查询的记录。对于left join就会返回左表的全部数据，对于right join就会返回右全表的记录。

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有full join（left、right）的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

总结：

其实以上结果的关键原因就是left join,right join,full join的特殊性，不管on上的条件是否为真都会返回left或right表中的记录，full则具有left和right的特性的并集。 而inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。