oracle sql self-join filter in the join clause vs where clause
In the case of outer join, ON clause will have no effect on outer table. This means that all rows from outer table will be returned and ON clause just determine which row of a joined table joins outer table. And those rows of outer table that do not meet condition(s) in the ON clause simply will be extended with null values for columns of the table that is being joined. WHERE clause filters the final result set.
In the first query, it will first fetch the result set of on
a1.id = a2.id and a1.date_id < a2.date_id and then it will apply the `where clause` in the result set and so you get '20120103'.In the second query you are taking all the records that satisfy
on a1.id = a2.id and a1.date_id < a2.date_id and a1.date_id >=20120103and thats why you get those many rows. Hope this helps you.