I can answer one of these. What I officially know about Oracle you can fit in a thimble and still pour a shot into.
todinhkhoi (6/13/2011)
1. Can you tell me why I would get an INNER JOIN if I filtered on columns from the OUTER table in the WHERE clause?
It's the operational order.
First, think about the join itself: (tbl1: 1/2/3/4, tbl2: 1/2)
1 1
2 2
3 NULL
4 NULL
Now, if you where clause BEFORE you join (thus, in the on clause) and remove line 2 via some tbl2.attribute = 'x':
1 1
2 NULL
3 NULL
4 NULL
If you do it in the WHERE clause:
1 1
The where clause happens (logically, not necessarily physically) after the joins complete. The ON clause is a direct descriptor as to what to take from a table during the join mechanic. The OUTER JOIN needed to have the NULL side restricted without restricting the full query result.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA