Below is the statement given in MicroSoft SQL Server migration documentation :
"When a view is defined with an outer join and is queried with a qualification on a column from the inner table of the outer join, the results from SQL Server and Oracle can differ. In most cases, Oracle views are easily translated into SQL Server views"
Please can anyone explain the above with some examples. I don't find such a 'CREATE VIEW' statement existsing in two DBs, which results in different result set. Am I wrong ?
Oracle 7 and 8 performed several flavors of JOINs using a "tournament" model, mostly to match the behavior of older utilities like SyncSort.
The tournament JOIN is more tolerant of missing stage 1 predicates because the actual JOIN operation hasn't taken place yet. This implies that some stage 2 predicates will fire (be evaluated) that wouldn't ever be evaluated in a relational model, because the candidate row would never have existed. The net result is that in some cases, Oracle would return rows that a relational database wouldn't, and even when rows weren't returned some side effects from the stage 2 predicates would exist.
The only answer that I ever found for exactly reproducing the behavior of Oracle's tournament joins was to recode the tournament in Transact-SQL using temp tables.
Oracle 8i and later removed the tournament join in favor of more relational behavior, and better conformance to the SQL standards.