Oracle differences between left join... ON... and left join... ON... WHERE...

Oracle left join... ON... AND... and left join... ON... WHERE... difference about left join... on... and... and left join... on... the difference between where is that many online statements are about left join... on... and ..., and conditions only apply to associated fields, such as select. * from tmp_table_a left join tmp_table_ B B on. col1 = B. col1 and B. col2 = xx, many people think Condition B. col2 = xx does not work. I personally disagree with this statement. At least, this is an irresponsible statement. Here are some examples of my tests. You can understand the differences between the two. Select count (*) from tb_bo_valusr_new where month = 201010 and brand = 3; -- 141858 select count (*) from tb_bo_valusr_new where month = 201010; --- 2281189 select count (distinct usr_nbr) from tb_bo_valusr_new where brand = 3; -- 152110 select count (distinct usr_nbr) from tb_bo_valusr_new where month = 201010; -- 2281189 select count (*) from tb_bo_valusr_new; -- 4602747 select count (*) from tmp_msy_bj_001; -- 986843 select cou Nt (*) from tmp_msy_bj_001 where if_wlg = 'yes'; -- 272623 -- Example 1 create table tmp_msy_bj_007as selecta. usr_nbr, B. cmcc_branch_cd fromtmp_msy_bj_001 aleft join tb_bo_valusr_new B on. usr_nbr = B. usr_nbr; select count (*) from tmp_msy_bj_007; -- 1957872 select count (distinct usr_nbr) from tmp_msy_bj_007; --- 986843 select count (*) from (select distinct * from tmp_msy_bj_007 ); -- 1024792 -- create table tmp_msy_bj_008as s Electa. usr_nbr, B. cmcc_branch_cd fromtmp_msy_bj_001 aleft join tb_bo_valusr_new B on. usr_nbr = B. usr_nbr and B. month = 201010; select count (*) from tmp_msy_bj_008; -- 986843 select count (distinct usr_nbr) from tmp_msy_bj_008; -- 986843 select count (*) from (select distinct * from tmp_msy_bj_008 ); -- 986843 -- Example 3 create table tmp_msy_bj_006as selecta. usr_nbr, B. cmcc_branch_cd fromtmp_msy_bj_001 aleft join tb_bo_va Lusr_new B on. usr_nbr = B. usr_nbr and B. month = 201010 where. if_wlg = 'yes'; select count (*) from tmp_msy_bj_006; -- 272623 select count (distinct usr_nbr) from tmp_msy_bj_006; -- 272623 select count (*) from (select distinct * from tmp_msy_bj_006); -- 272623 -- Example 4 create table tmp_msy_bj_005as selecta. usr_nbr, B. cmcc_branch_cd fromtmp_msy_bj_001 aleft join tb_bo_valusr_new B on. usr_nbr = B. usr_nbr and B. month = 201010 And B. brand = 3; select count (*) from tmp_msy_bj_005; -- 986843 select count (distinct usr_nbr) from tmp_msy_bj_005; -- 986843 select count (*) from (select distinct * from tmp_msy_bj_005 ); -- 986843 -- Example 5 create table tmp_msy_bj_003as selecta. usr_nbr, B. cmcc_branch_cd, B. brand fromtmp_msy_bj_001 aleft join tb_bo_valusr_new B on. usr_nbr = B. usr_nbr and B. brand = 3; select count (*) from tmp_msy_bj_003; -- 1062507 select c Ount (distinct usr_nbr) from tmp_msy_bj_003; -- 986843 select count (*) from (select distinct * from tmp_msy_bj_003); -- 991560 select count (*) from tmp_msy_bj_003 where brand = 3; -- 154124 select count (distinct usr_nbr) from tmp_msy_bj_003 where brand = 3; -- 78460 select count (*) from tmp_msy_bj_003 where brand is null; -- 908383 -- Example 6 create table tmp_msy_bj_002as selecta. usr_nbr, B. cmcc_branch_cd fromtmp_msy_bj_001 Aleft join tb_bo_valusr_new B on. usr_nbr = B. usr_nbr where B. brand = 3; select count (*) from tmp_msy_bj_002; --- 154124 select count (distinct usr_nbr) from tmp_msy_bj_002; -- 78460 select count (*) from (select distinct * from tmp_msy_bj_002 ); -- 83177 -- Example 7 create table tmp_msy_bj_011as selecta. usr_nbr, B. cmcc_branch_cd fromtmp_msy_bj_001 a, tb_bo_valusr_new B where. usr_nbr = B. usr_nbr and B. brand = 3; select count (*) From tmp_msy_bj_011; --- 154124 select count (distinct usr_nbr) from tmp_msy_bj_011; -- 78460 select count (*) from (select distinct * from tmp_msy_bj_011 ); -- 83177 -- Example 8 create table tmp_msy_bj_009as selecta. usr_nbr, B. cmcc_branch_cd fromtmp_msy_bj_001 aleft join tb_bo_valusr_new B on. usr_nbr = B. usr_nbr where B. brand = 3 and B. month = 201010; select count (*) from tmp_msy_bj_009; --- 78421 select count (distinct usr _ Nbr) from tmp_msy_bj_009; -- 78421 select count (*) from (select distinct * from tmp_msy_bj_009 ); -- 78421 -- the creation time of the above tables is within 20 seconds -- Example 9 create table tmp_msy_bj_004as selecta. usr_nbr, B. cmcc_branch_cd fromtmp_msy_bj_001 aleft join tb_bo_valusr_new B on. usr_nbr = B. usr_nbr and. if_wlg = 'yes'; -- the result of running for 25 minutes has not been returned. In this example, the tb_bo_valusr_new table in Table B contains the full monthly number of the current month, and the monthly number is not repeated, table tmp_msy_bj_001 does not have repeated records. In this example, if you use where to specify the constraints of the non-join field of B after left join, it is equivalent to equivalent join of A and B. Obviously, in this case, left join has no meaning. The original meaning of left join is to keep all records in the previous table, therefore, it is silly to put the restrictions on non-join fields in the last table after the where clause. From the preceding example, we can see that after left join, and B is specified. column = .... the effect is: connect the records that meet the B condition in a, and keep other non-conforming records in a once. Therefore, we can understand left join... on... and...: This ensures that all the records in the previous table are retained and the records that meet the specified conditions can be obtained in the following table. When the and... condition of the following table does not work, the record set of the next table, that is, table B, is a subset of Table. We can see from Example 9 that we absolutely do not place the constraints of a in left join on... and.... That is to find death.