I am not a big fan of ANSI style joins especially outer joins . For me , the SQL becomes large , applying hints is a bit problematic.
Some people would disagree with this . Their take is that is a standard ( I can't win on that ) and second , it is readable ( I have to say , this is subjective).For most of the queries written in ANSI way , Oracle translates itself in a legacy style ( like table1.column1 = table2.column1 (+) ... ) , if we look at the trace files.
Not all joins can be done in a legacy style . For example , FULL OUTER JOIN , PARTITION OUTER JOIN can be written only in ANSI style.
I try to use legacy joins , wherever possible :-) Here is an example .
SQL> create table zahir.emp as Select * from scott.emp;
Table created.
SQL> create table zahir.dept as Select * from scott.dept;
Table created.
Example of Equi Join or Inner Join
-----------------------------------
SQL> select d.deptno , d.dname , e.empno , e.ename from dept d , emp e where d.deptno = e.deptno;
DEPTNO DNAME EMPNO ENAME
---------- -------------- ---------- ----------
20 RESEARCH 7369 SMITH
30 SALES 7499 ALLEN
30 SALES 7521 WARD
20 RESEARCH 7566 JONES
30 SALES 7654 MARTIN
30 SALES 7698 BLAKE
10 ACCOUNTING 7782 CLARK
20 RESEARCH 7788 SCOTT
10 ACCOUNTING 7839 KING
30 SALES 7844 TURNER
20 RESEARCH 7876 ADAMS
30 SALES 7900 JAMES
20 RESEARCH 7902 FORD
10 ACCOUNTING 7934 MILLER
10 ACCOUNTING 7 James Bond
15 rows selected.
SQL> select d.deptno , d.dname , e.empno , e.ename from dept d inner join emp e on ( d.deptno = e.deptno);
DEPTNO DNAME EMPNO ENAME
---------- -------------- ---------- ----------
20 RESEARCH 7369 SMITH
30 SALES 7499 ALLEN
30 SALES 7521 WARD
20 RESEARCH 7566 JONES
30 SALES 7654 MARTIN
30 SALES 7698 BLAKE
10 ACCOUNTING 7782 CLARK
20 RESEARCH 7788 SCOTT
10 ACCOUNTING 7839 KING
30 SALES 7844 TURNER
20 RESEARCH 7876 ADAMS
30 SALES 7900 JAMES
20 RESEARCH 7902 FORD
10 ACCOUNTING 7934 MILLER
10 ACCOUNTING 7 James Bond
15 rows selected.
Example of Outer Join
---------------------
SQL> select d.deptno , d.dname , e.empno , e.ename from dept d , emp e where d.deptno = e.deptno (+);
DEPTNO DNAME EMPNO ENAME
---------- -------------- ---------- ----------
20 RESEARCH 7369 SMITH
30 SALES 7499 ALLEN
30 SALES 7521 WARD
20 RESEARCH 7566 JONES
30 SALES 7654 MARTIN
30 SALES 7698 BLAKE
10 ACCOUNTING 7782 CLARK
20 RESEARCH 7788 SCOTT
10 ACCOUNTING 7839 KING
30 SALES 7844 TURNER
20 RESEARCH 7876 ADAMS
30 SALES 7900 JAMES
20 RESEARCH 7902 FORD
10 ACCOUNTING 7934 MILLER
10 ACCOUNTING 7 James Bond
40 OPERATIONS
16 rows selected.
SQL> select d.deptno , d.dname , e.empno , e.ename from dept d left outer join emp e on ( d.deptno = e.deptno);
DEPTNO DNAME EMPNO ENAME
---------- -------------- ---------- ----------
20 RESEARCH 7369 SMITH
30 SALES 7499 ALLEN
30 SALES 7521 WARD
20 RESEARCH 7566 JONES
30 SALES 7654 MARTIN
30 SALES 7698 BLAKE
10 ACCOUNTING 7782 CLARK
20 RESEARCH 7788 SCOTT
10 ACCOUNTING 7839 KING
30 SALES 7844 TURNER
20 RESEARCH 7876 ADAMS
30 SALES 7900 JAMES
20 RESEARCH 7902 FORD
10 ACCOUNTING 7934 MILLER
10 ACCOUNTING 7 James Bond
40 OPERATIONS
16 rows selected.
Example of Full Join
---------------------
Here is where , leacgy style doesn't work.
SQL> select d.deptno , d.dname , e.empno , e.ename from dept d , emp e where d.deptno (+) = e.deptno (+);
select d.deptno , d.dname , e.empno , e.ename from dept d , emp e where d.deptno (+) = e.deptno (+)
*
ERROR at line 1:
ORA-01468: a predicate may reference only one outer-joined table
SQL> select d.deptno , d.dname , e.empno , e.ename from dept d full outer join emp e on ( d.deptno = e.deptno);
DEPTNO DNAME EMPNO ENAME
---------- -------------- ---------- ----------
20 RESEARCH 7369 SMITH
30 SALES 7499 ALLEN
30 SALES 7521 WARD
20 RESEARCH 7566 JONES
30 SALES 7654 MARTIN
30 SALES 7698 BLAKE
10 ACCOUNTING 7782 CLARK
20 RESEARCH 7788 SCOTT
10 ACCOUNTING 7839 KING
30 SALES 7844 TURNER
20 RESEARCH 7876 ADAMS
30 SALES 7900 JAMES
20 RESEARCH 7902 FORD
10 ACCOUNTING 7934 MILLER
10 ACCOUNTING 7 James Bond
40 OPERATIONS
Comments Welcome.
No comments:
Post a Comment