difference between on condition and where condition in Oracle left join __oracle

difference between on condition and where condition in Oracle left join

The on condition in the join is the same as the where condition, while the left join is not the same

Sql> CREATE TABLE T1 as select * from Scott.emp;

Table has been created.

Sql> CREATE TABLE t2 as select * from Scott.dept;

Table has been created.

sql> Delete T2 where deptno=30;

1 rows have been deleted.

The following are query results and execution plans using where

The following are query results and execution plans using the on condition

Oracle has different parsing of predicates and t1.job= ' clerk ', where t1.job= ' clerk '.

Use where t1.job= ' clerk ':

1-access ("T1".) DEPTNO "=" T2 "." DEPTNO "(+))
2-filter ("T1".) JOB "= ' clerk ')

Oracle first based on "T1". JOB "= ' Clerk" filters the T1 table and then makes a left outer join with the T2 table

Oracle-resolved predicates and t1.job= ' clerk ' (on) are:

1-access ("T1".) DEPTNO "=" T2 "." DEPTNO "(+) and" T1 ". JOB "=case when
("T2".) DEPTNO "(+) is not NULL) THEN ' clerk ' ELSE ' clerk ' end)

What does it mean?

Oracle scans the T1,T2 for a full table, followed by a left outer join (which may also be connected during the scan), and t1.job= ' clerk ' has no effect on the total number of records after the connection, but on the non-compliant and t1.job= ' The department name in the record for clerk ' is empty

Condition restrictions in on

Sql> select * from Tab1; ID SIZE1--------------------1 2 3 sql> Select * FR

     Om tab2;  SIZE1 NAME------------------------------AAA sql> Select tab1.*,tab2.* from

        Tab1 left Join TaB2 on (tab1.size1= tab2.size1);
         ID SIZE1 SIZE1 NAME--------------------------------------------------1 AAA 2 BBB 2 CCC 3 sql> Select TAB1.*,TAB2

        . * from Tab1 LEFT join TaB2 on (tab1.size1= tab2.size1 and tab1.id=2);         ID SIZE1 SIZE1 NAME--------------------------------------------------1 10 2 BBB 2 CCC 3 sql> Select tab1.*,tab2.* from Tab1 le

        FT join TaB2 on (tab1.size1= tab2.size1 and Tab2.name= ' AAA ');  Id    SIZE1 SIZE1 NAME--------------------------------------------------1 AAA 3 2 sql> Select tab1.*,tab2.* from tab1 left join TaB2 on (tab1.size1= tab2.size1 A

        nd tab2.name= ' BBB ');
         ID SIZE1 SIZE1 NAME--------------------------------------------------2 BBB 3 1 sql> Select tab1.*,tab2.* from tab1 left join TaB2 on (tab1.size1= tab2.siz

        E1 and Tab2.name= ' CCC ');
         ID SIZE1 SIZE1 NAME--------------------------------------------------2 CCC

3 1 sql> Update tab2 set name= ' DDD ' where size1=20;

2 rows have been updated.
Submit completed.

Sql> commit 2;

Submit completed.

        Sql> Select tab1.*,tab2.* from tab1 left join TaB2 on (tab1.size1= tab2.size1 and Tab2.name= ' DDD '); ID SIZE1 SIZE1 NAME--------------------------------------------------2 DDD 2 ddd 3 30 1

        Sql> Select tab1.*,tab2.* from tab1 left join TaB2 on (tab1.size1= tab2.size1 and tab2.name= ' xxx ');         ID SIZE1 SIZE1 NAME--------------------------------------------------3 30 2 20 1 10

Note that when on the left table field limit in on, it is not the same as the non-join field limit for the right table.

When on the non-join field limit on the right table in on (tab1.size1= tab2.size1 and Tab2.name= ' AAA '), the right table gets the result based on the connectionless field restrictions, and then the left table is associated with it.
Select tab1.*,tab2.* from tab1 left join TaB2 on (tab1.size1= tab2.size1 and Tab2.name= ' AAA ');
Equivalent
Select tab1.*,t.* from tab1 LEFT join (SELECT * from tab2 where tab2.name= ' AAA ') t on (tab1.size1= t.size1);