本文转载请联系大数据和云端公众号。SQL优化过程中，发现开发人员在写多表关联查询时，把谓词过滤条件写的很随意，写在on和where之后，可能导致理解不了真正的含义。无法获得想要的结果。多表关联的连接方式有四种：innerjoin、leftjoin、rightjoin、fulljoin。下面的实验将说明on和where之后不同连接方法谓词的作用和影响。初始化测试数据createtablet1(idnumber(10),namevarchar2(30),statusvarchar2(2));createtablet2(idnumber(10),mobilevarchar2(30));insertintot1values(1,'a','1');insertintot1values(2,'b','1');insertintot1values(3,'c','1');insertintot1values(4,'d','1');insertintot1values(5,'e','1');insertintot1values(6,'f','0');insertintot1values(7,'g','0');insertintot1values(8,'h','0');insertintot1values(9,'i','0');insertintot1values(10,'j','0');insertintot2values(1,'12345');insertintot2values(2,'23456');insertintot2values(3,'34567');insertintot2values(6,'67890');insertintot2values(7,'78901');1.InnerjoinSQL>select*fromt1innerjoint2ont1.id=t2.idandt1.status='1';IDNAMESTIDMOBILE1a11123452b12234563c1334567SQL>select*fromt1innerjoint2ont1。id=t2.idwheret1.status='1';IDNAMESTIDMOBILE1a11123452b12234563c1334567我们发现放在on后面的谓词t1.status='1'和where后面是一样的，它们的执行计划是一样的，说明CBO做了对这两个案例同样的处理。执行计划如下图所示：无论Innerjoin时predicate放在什么位置，CBO都是先过滤t1表，再关联t2表。2.leftjoin(1)左右表谓词过滤器放在on之后：SQL>select*fromt1leftjoint2ont1.id=t2.idandt1.status='1'andt2.id<3;IDNAMESTIDMOBILE1a11123452b12234563c18h05e19i010j07g06f04d1执行计划如下：从执行计划可以看出t1.status='1'放在on之后，t1表不过滤谓词状态，结果集显示t1的全表数据.这是leftjoin的特性决定的，lefttable会显示所有的数据。t2.id<3是连接前过滤t2表，t1.status='1'作为连接条件存在，对连接时产生的笛卡尔积数据进行连接过滤。(2)左右表谓词过滤器放在where之后：SQL>select*fromt1leftjoint2ont1.id=t2.idwheret1.status='1'andt2.id<3;从执行计划中可以得到IDNAMESTIDMOBILE1a11123452b1223456可以看出谓词放在where之后先过滤表，再连接过滤后的数据。并且我们发现t1表自动添加了id<3的过滤条件。这是因为存在t1.id=t2.id的等价连接。如果t1表上的id列有索引，可以看出性能差异。注意join方式已经改为hashjoin。这是因为右表的谓词过滤条件写在where之后，CBO会把左连接等同于内连接。(3)右表的谓词写在on之后，左表的谓词写在where之后：SQL>select*fromt1leftjoint2ont1.id=t2.idandt2.id<3wheret1.status='1';2IDNAMESTIDMOBILE1a11123452b12234565e14d13c1在on之后右表写过滤器，先过滤两个表，然后进行leftjoin，结果集和where后面写的不一样，连接方式还是leftouterjoin，过滤后显示t1的所有数据。(4)右表的谓词写在where之后，左表的谓词写在on之后：SQL>select*fromt1leftjoint2ont1.id=t2.idandt1.status='1'wheret2.id<7;IDNAMESTIDMOBILE1a11123452b12234563c1334567从执行计划来看，左连接转换为内连接，左表的谓词条件写在相同位置。并且由于t2表过滤后的数据少于t1表，所以CBO将t2表作为驱动表。接下来再看另外一条语句：SQL>select*fromt1leftjoint2ont1.id=t2.idandt1.status='1'wheret1.status='0';IDNAMESTIDMOBILE8h06f09i010j07g0从执行中可以看出计划虽然t2表返回0行。第3步的过滤条件肯定不成立，但是有逻辑读消耗，所以推断还是进行了全表扫描，所以这个语句扫描t2表是一种资源浪费，没有意义。也许你会想，写这种SQL谁会这么无聊，但是在开发过程中，SQL语句往往是通过组合各种过滤条件拼接的，因为返回的结果是正确的，而自己并没有意识到会出现这种问题。这里解释这种情况主要是为了说明一件事：不要总想着用一条语句来解决所有的功能需求。适当的拆分对于提高性能是必要的。3、右连接和左连接类似，只是右表显示所有数据，on后面写的谓词过滤器对右表不起作用，这里就不举例了。4.fulljoin在应用中好像很少遇到，但是存在也是合理的，只是我自己没有遇到过。(1)两张表的谓词都放在on之后：这种情况下两张表不会先过滤，而是作为连接条件进行过滤。满足连接则匹配，不匹配则使用左右表的数据。显示，而另一个表的字段显示为空。(2)两张表的谓词放在where之后：此时CBO将其转化为内联，先过滤再关联。(3)左表谓词放在on之后，右表放在where之后：这种情况转化为右外连接，只是两表先过滤再关联。(4)左表谓词放在where之后，右表放在on之后：此时转为左外连接，两表先过滤再关联。总结1、对于innerjoin，两张表的谓词条件放在on和where之后。2.对于leftjoin：左表predicate放在where后不会过滤左表数据，仍然显示左表的所有数据，where放在后会过滤左表。右表谓词放在on或where之后，会先过滤右表再join，但是leftjoin放在where后会转为innerjoin。3、外连接，谓词条件的位置不同，结果集也不同。您可以根据自己的需要使用它。作者简介于志军，云与恩墨技术顾问，Oracle12cOCM。拥有OCM和OBCA证书。曾在大型国企工作多年，从事数据库运维工作。目前就职于银行，专攻SQL性能优化。热衷于学习运维故障排除、备份恢复、升级迁移、性能优化等。与某人分享。
