Oracle join and where execution order

Oracle join and where join in execution order: select * from a inner join B on. aa = B. bb; equivalent to select * from a, B where. aa = B. bb; that is, the internal join is equivalent to the normal equivalent join. Outer join: www.2cto.com is divided into several types: left/right/full outer join. It seems that outer can be omitted, that is, left join, right join, full join, and so on. Left join: select * from a left join B on. aa = B. bb; equivalent to another statement: select * from a, B where. aa = B. bb (+); If table a on the left has 5, the result is 5. Right join: www.2cto.com select * from a right join B on. aa = B. bb; equivalent to another statement: select * from a, B where. aa (+) = B. bb; if Table a has five rows and table B has three, only three rows are displayed, the same as the number of Table B entries on the right. Full join: select * from a full join B on a. aa = B. bb; all columns a and B are equal, and all rows in a and B are equal. Self join: self join is not an SQL keyword. It is implemented in the form of select * from aa a and aa B. It is mainly used to join two columns in the same table. Example: select * from a1; AA BB1 1 12 2 23 3 34 4 45 5 5 select * From a2; CC DD1 2 22 3 33 5 54 9 9 9 join result: select * from a1 a, a2 B where. aa = B. cc; equivalent to: select * from a1 a inner join a2 B on. aa = B. cc; aa bb cc DD1 2 2 22 3 3 3 33 5 5 5 left Outer join result: select * from a1 a left join a2 B on. aa = B. cc; equivalent to: select * from a1 a, a2 B where. aa = B. cc (+); aa bb cc DD1 2 2 22 3 3 3 3 33 5 5 54 1 1 5 4 4 right join result: Select * from a1 a right join a2 B on. aa = B. cc; equivalent to: select * from a1 a, a2 B where. aa (+) = B. cc; aa bb cc DD1 2 2 22 3 3 3 33 5 5 5 54 9 9 full join result: select * from a1 a full join a2 B on. aa = B. cc; equivalent to the first left join and then right join: select * from a1 a, a2 B where. aa (+) = B. cc union select * from a1 a, a2 B where. aa = B. cc (+); aa bb cc DD1 1 1 2 2 2 2 2 23 3 3 3 34 4 4 5 5 5 56 9 9. Note that the (+) method is dedicated to oracle syntax and is not recommended. The reason is as follows: we recommend that you use the Outer Join syntax after the From statement instead of the Oracle Join operator (+ ). and (+) is restricted by the following rules, but the Outer Join syntax is not affected ~~ 1) You cannot use (+) in the query block. When it simultaneously contains the from Statement of join 2) (+), it is only in the where statement, and can only correspond to one table or View row Field 3) If A and B are joined with multiple conditions, then (+) must improve all matching conditions. If not, oracle will not warn you ~ Only the results are naturally different. 4) You cannot create a (+) Join Operation for querying in another table outside the table ~~ 5) You cannot use the (+) external connection to connect to yourself. Of course, Self Join is acceptable. 6) note after the Where clause containing (+) OR the IN Clause unavailable subquery unavailable remarks, the SQL statement execution sequence is similar to that of Machine Translation. It is unclear whether it is applicable to oracle, but the execution sequence of SQL 2008 in MSDN is similar. The most obvious characteristic of SQL speech that is not in line with other programming speech is the order of Punishment Code. In most of the database speeches, the code is punished in the encoding order. However, in an SQL statement, the first clause to be punished is FROM, rather than the first-rendered SELECT statement. SQL query processing penalty step number: (8) SELECT (9) DISTINCT (11) <TOP_specification> <_ list> (1) FROM <left_table> (3) <join_type> JOIN <right_table> (2) ON <join_condition> (4) WHERE <where_condition> (5) group by <group_by_list> (6) WITH {CUBE | ROLLUP} (7) HAVING