ORACLE FAQS

http://blog.sqlauthority.com/

http://www.coolinterview.com/type.asp?iType=8

http://www.orafaq.com/

http://www.techonthenet.com/oracle/index.php

http://www.vishalon.net/TechyStuffs/SQLFAQs/tabid/128/Default.aspx

http://www.orafaq.com/cgi-bin/sqlformat/pp/utilities/sqlformatter.tpl

Some info on outer joins
Query A: Oracle Outer Join Syntax

view plaincopy to clipboardprint?

SELECT d.dname, d.deptno, e.ename
FROM dept d, emp e
WHERE d.deptno = e.deptno(+) and
d.deptno in (10,40)

SELECT d.dname, d.deptno, e.ename
FROM dept d, emp e
WHERE d.deptno = e.deptno(+) and
d.deptno in (10,40)
Query B: ANSI Outer Join Syntax Version 1

view plaincopy to clipboardprint?

SELECT d.dname, d.deptno, e.ename
FROM dept d LEFT OUTER JOIN emp e
ON d.deptno = e.deptno
WHERE d.deptno in (10,40)

SELECT d.dname, d.deptno, e.ename
FROM dept d LEFT OUTER JOIN emp e
ON d.deptno = e.deptno
WHERE d.deptno in (10,40)
Query C: ANSI Outer Join Syntax Version 2

view plaincopy to clipboardprint?

SELECT d.dname, d.deptno, e.ename
FROM dept d LEFT OUTER JOIN emp e
ON d.deptno = e.deptno and
d.deptno in (10,40)

SELECT d.dname, d.deptno, e.ename
FROM dept d LEFT OUTER JOIN emp e
ON d.deptno = e.deptno and
d.deptno in (10,40)
Do note the slight difference between the two ANSI versions: Query B has the filter predicate in the WHERE clause, where Query C has the filter predicate in the ON clause.

Query Results
Query A

DNAME DEPTNO ENAME
-------------- ---------- ----------
ACCOUNTING 10 CLARK
ACCOUNTING 10 KING
ACCOUNTING 10 MILLER
OPERATIONS 40

4 rows selected.
Query B

DNAME DEPTNO ENAME
-------------- ---------- ----------
ACCOUNTING 10 CLARK
ACCOUNTING 10 KING
ACCOUNTING 10 MILLER
OPERATIONS 40

4 rows selected.
Query C

DNAME DEPTNO ENAME
-------------- ---------- ----------
ACCOUNTING 10 CLARK
ACCOUNTING 10 KING
ACCOUNTING 10 MILLER
RESEARCH 20
SALES 30
OPERATIONS 40

6 rows selected.