Differences between on and where in left join of Oracle

Differences between on and where in left join of Oracle databases when two or more tables are connected to return records, an intermediate temporary table is generated, then, return the temporary table to the user. When left jion is used, the on and where conditions differ as follows: www.2cto.com 1. The on condition is used when a temporary table is generated, regardless of whether the conditions on are true or not, all records in the left table are returned. 2. The where condition is used to filter the temporary table after the temporary table is generated. At this time, there is no meaning of left join (records in the left table must be returned). If the condition is not true, all records are filtered out. Suppose there are two tables: Table 1 tab1: id size 1 10 2 20 3 30 Table 2 tab2: size name 10 AAA 20 BBB 20 CCC two SQL statements: 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') www.2cto.com first SQL process: 1. Intermediate table on condition: 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. filter the where Condition for the intermediate table: tab2.name = 'aaa' tab1.id tab1.size tab2.size tab2.name 1 10 10 AAA www.2cto.com process of the second SQL statement: 1. Intermediate table on condition: tab1.size = tab2.size and tab2.name = 'aaa' (records in the left table will also be returned if the condition is not true) tab1.id tab1.size tab2.size tab2.name 1 10 AAA 2 20 (null) 3 30 (null) the key reason for the above results is the particularity of left join, right join, and full join, no matter whether the condition on is true or not, records in the left or right table are returned. full is the Union set with the left and right features. Inner jion does not have this particularity, so the condition is placed in the on and where, and the returned result set is the same.