Oracle 9i New SQL Features
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 50
14 rows selected.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50
5 rows selected.
NATURAL JOIN
SQL> select empno, deptno, dname from emp natural join dept;
vs.
select emp.empno, emp.deptno, dept.dname
from emp, dept where emp.deptno = dept.deptno;
EMPNO DEPTNO DNAME
---------- ---------- --------------
7369 20 RESEARCH
7499 30 SALES
7521 30 SALES
7566 20 RESEARCH
7654 30 SALES
7698 30 SALES
7782 10 ACCOUNTING
7788 20 RESEARCH
7839 10 ACCOUNTING
7844 30 SALES
7876 20 RESEARCH
7900 30 SALES
7902 20 RESEARCH
7934 50
14 rows selected.
RIGHT OUTER JOIN
SQL> select emp.empno, emp.deptno, dept.dname from emp
right outer join dept on (emp.deptno=dept.deptno);
vs.
select emp.empno, emp.deptno, dept.dname from emp, dept
where emp.deptno (+) = dept.deptno
EMPNO DEPTNO DNAME
---------- ---------- --------------
7369 20 RESEARCH
7499 30 SALES
7521 30 SALES
7566 20 RESEARCH
7654 30 SALES
7698 30 SALES
7782 10 ACCOUNTING
7788 20 RESEARCH
7839 10 ACCOUNTING
7844 30 SALES
7876 20 RESEARCH
7900 30 SALES
7902 20 RESEARCH
7934 50
OPERATIONS
15 rows selected.
LEFT OUTER JOIN
SQL> select emp.empno, emp.deptno, dept.dname from emp
left outer join dept on (emp.deptno=dept.deptno);
vs.
select emp.empno, emp.deptno, dept.dname from emp, dept
where emp.deptno = dept.deptno (+)
EMPNO DEPTNO DNAME
---------- ---------- --------------
7839 10 ACCOUNTING
7782 10 ACCOUNTING
7902 20 RESEARCH
7876 20 RESEARCH
7788 20 RESEARCH
7566 20 RESEARCH
7369 20 RESEARCH
7900 30 SALES
7844 30 SALES
7698 30 SALES
7654 30 SALES
7521 30 SALES
7499 30 SALES
7934 50
14 rows selected.
FULL OUTER JOIN
SQL> select emp.empno, emp.deptno, dept.dname from emp
full outer join dept on (emp.deptno=dept.deptno);
vs.
select emp.empno, emp.deptno, dept.dname from emp, dept
where emp.deptno (+) = dept.deptno
union
select emp.empno, emp.deptno, dept.dname from emp, dept
where emp.deptno = dept.deptno (+)
EMPNO DEPTNO DNAME
---------- ---------- --------------
7839 10 ACCOUNTING
7782 10 ACCOUNTING
7902 20 RESEARCH
7876 20 RESEARCH
7788 20 RESEARCH
7566 20 RESEARCH
7369 20 RESEARCH
7900 30 SALES
7844 30 SALES
7698 30 SALES
7654 30 SALES
7521 30 SALES
7499 30 SALES
7934 50
OPERATIONS
15 rows selected.
USING
SQL> select empno, dname from emp join dept using (deptno);
vs.
select empno, dname from emp, dept where emp.deptno = dept.deptno;
EMPNO DNAME
---------- --------------
7369 RESEARCH
7499 SALES
7521 SALES
7566 RESEARCH
7654 SALES
7698 SALES
7782 ACCOUNTING
7788 RESEARCH
7839 ACCOUNTING
7844 SALES
7876 RESEARCH
7900 SALES
7902 RESEARCH
7934 ACCOUNTING
14 rows selected.
ON
SQL> select empno, dname from emp join dept
on (emp.deptno = dept.deptno and emp.empno < 7500)
vs.
select empno, dname
from emp, dept
where emp.deptno = dept.deptno and
emp.empno < 7500
EMPNO DNAME
---------- --------------
7369 RESEARCH
7499 SALES
CROSS JOIN
SQL> select emp.empno, dept.deptno from emp cross join dept;
vs.
select emp.empno, dept.deptno from emp, dept;
EMPNO DEPTNO
---------- ----------
7369 10
7499 10
7521 10
7566 10
7654 10
7698 10
7782 10
7788 10
7839 10
7844 10
7876 10
7900 10
7902 10
7934 10
7369 20
7499 20
7521 20
7566 20
7654 20
7698 20
7782 20
7788 20
7839 20
7844 20
7876 20
7900 20
7902 20
7934 20
7369 30
7499 30
7521 30
7566 30
7654 30
7698 30
7782 30
7788 30
7839 30
7844 30
7876 30
7900 30
7902 30
7934 30
7369 40
7499 40
7521 40
7566 40
7654 40
7698 40
7782 40
7788 40
7839 40
7844 40
7876 40
7900 40
7902 40
7934 40
7369 50
7499 50
7521 50
7566 50
7654 50
7698 50
7782 50
7788 50
7839 50
7844 50
7876 50
7900 50
7902 50
7934 50
70 rows selected.