join: join is a temparary relationship which is used to retriew the data from more than one table
catisian join?(cross join)
natural join
equi join(by using = operator)
non eqi-join
self join(joining a table by itself)
inner join
outer join---left outer join,right outer join,full outer join
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1021 122
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 10
15 rows selected.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from emp,dept;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- -------------
1021 122 10 ACCOUNTING NEW YORK
7369 SMITH CLERK 7902 17-DEC-80 800 20 10 ACCOUNTING NEW YORK
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 10 ACCOUNTING NEW YORK
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 10 ACCOUNTING NEW YORK
7566 JONES MANAGER 7839 02-APR-81 2975 20 10 ACCOUNTING NEW YORK
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 10 ACCOUNTING NEW YORK
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 10 ACCOUNTING NEW YORK
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 10 ACCOUNTING NEW YORK
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 10 ACCOUNTING NEW YORK
7900 JAMES CLERK 7698 03-DEC-81 950 30 10 ACCOUNTING NEW YORK
7902 FORD ANALYST 7566 03-DEC-81 3000 20 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK
1021 122 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 20 RESEARCH DALLAS
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 20 RESEARCH DALLAS
7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 20 RESEARCH DALLAS
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 20 RESEARCH DALLAS
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 17-NOV-81 5000 10 20 RESEARCH DALLAS
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-DEC-81 950 30 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 23-JAN-82 1300 10 20 RESEARCH DALLAS
1021 122 30 SALES CHICAGO
7369 SMITH CLERK 7902 17-DEC-80 800 20 30 SALES CHICAGO
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 02-APR-81 2975 20 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 30 SALES CHICAGO
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 30 SALES CHICAGO
7839 KING PRESIDENT 17-NOV-81 5000 10 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 30 SALES CHICAGO
7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 03-DEC-81 3000 20 30 SALES CHICAGO
7934 MILLER CLERK 7782 23-JAN-82 1300 10 30 SALES CHICAGO
1021 122 40 OPERATIONS BOSTON
7369 SMITH CLERK 7902 17-DEC-80 800 20 40 OPERATIONS BOSTON
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 40 OPERATIONS BOSTON
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 40 OPERATIONS BOSTON
7566 JONES MANAGER 7839 02-APR-81 2975 20 40 OPERATIONS BOSTON
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 40 OPERATIONS BOSTON
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 40 OPERATIONS BOSTON
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 40 OPERATIONS BOSTON
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 40 OPERATIONS BOSTON
7839 KING PRESIDENT 17-NOV-81 5000 10 40 OPERATIONS BOSTON
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 40 OPERATIONS BOSTON
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 40 OPERATIONS BOSTON
7900 JAMES CLERK 7698 03-DEC-81 950 30 40 OPERATIONS BOSTON
7902 FORD ANALYST 7566 03-DEC-81 3000 20 40 OPERATIONS BOSTON
7934 MILLER CLERK 7782 23-JAN-82 1300 10 40 OPERATIONS BOSTON
60 rows selected.
SQL>
SQL> ed
Wrote file afiedt.buf
1* select * from emp natural join dept
SQL> /
DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DNAME LOC
---------- ---------- ---------- --------- ---------- --------- ---------- ---------- -------------- -------------
20 7369 SMITH CLERK 7902 17-DEC-80 800 RESEARCH DALLAS
30 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 SALES CHICAGO
30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 SALES CHICAGO
20 7566 JONES MANAGER 7839 02-APR-81 2975 RESEARCH DALLAS
30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 SALES CHICAGO
30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 SALES CHICAGO
10 7782 CLARK MANAGER 7839 09-JUN-81 2450 ACCOUNTING NEW YORK
20 7788 SCOTT ANALYST 7566 19-APR-87 3000 RESEARCH DALLAS
10 7839 KING PRESIDENT 17-NOV-81 5000 ACCOUNTING NEW YORK
30 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 SALES CHICAGO
20 7876 ADAMS CLERK 7788 23-MAY-87 1100 RESEARCH DALLAS
30 7900 JAMES CLERK 7698 03-DEC-81 950 SALES CHICAGO
20 7902 FORD ANALYST 7566 03-DEC-81 3000 RESEARCH DALLAS
10 7934 MILLER CLERK 7782 23-JAN-82 1300 ACCOUNTING NEW YORK
14 rows selected.
SQL>
SQL> select * from emp,dept where emp.deptno=dept.deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- -------------
7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK
14 rows selected.
SQL>
SQL> ed
Wrote file afiedt.buf
1* select * from emp,dept where emp.deptno>=dept.deptno
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- -------------
7369 SMITH CLERK 7902 17-DEC-80 800 20 10 ACCOUNTING NEW YORK
7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 10 ACCOUNTING NEW YORK
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 10 ACCOUNTING NEW YORK
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 20 RESEARCH DALLAS
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 02-APR-81 2975 20 10 ACCOUNTING NEW YORK
7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 10 ACCOUNTING NEW YORK
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 10 ACCOUNTING NEW YORK
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 20 RESEARCH DALLAS
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 20 RESEARCH DALLAS
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 10 ACCOUNTING NEW YORK
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-DEC-81 950 30 10 ACCOUNTING NEW YORK
7900 JAMES CLERK 7698 03-DEC-81 950 30 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 03-DEC-81 3000 20 10 ACCOUNTING NEW YORK
7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK
31 rows selected.
SQL> ed
Wrote file afiedt.buf
1* select * from emp,dept where emp.deptno<=dept.deptno
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- -------------
7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 17-DEC-80 800 20 30 SALES CHICAGO
7369 SMITH CLERK 7902 17-DEC-80 800 20 40 OPERATIONS BOSTON
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 40 OPERATIONS BOSTON
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 40 OPERATIONS BOSTON
7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCH DALLAS
7566 JONES MANAGER 7839 02-APR-81 2975 20 30 SALES CHICAGO
7566 JONES MANAGER 7839 02-APR-81 2975 20 40 OPERATIONS BOSTON
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 40 OPERATIONS BOSTON
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 40 OPERATIONS BOSTON
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 20 RESEARCH DALLAS
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 30 SALES CHICAGO
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 40 OPERATIONS BOSTON
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 30 SALES CHICAGO
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 40 OPERATIONS BOSTON
7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 17-NOV-81 5000 10 20 RESEARCH DALLAS
7839 KING PRESIDENT 17-NOV-81 5000 10 30 SALES CHICAGO
7839 KING PRESIDENT 17-NOV-81 5000 10 40 OPERATIONS BOSTON
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 40 OPERATIONS BOSTON
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 30 SALES CHICAGO
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 40 OPERATIONS BOSTON
7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES CHICAGO
7900 JAMES CLERK 7698 03-DEC-81 950 30 40 OPERATIONS BOSTON
7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 03-DEC-81 3000 20 30 SALES CHICAGO
7902 FORD ANALYST 7566 03-DEC-81 3000 20 40 OPERATIONS BOSTON
7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 7782 23-JAN-82 1300 10 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 23-JAN-82 1300 10 30 SALES CHICAGO
7934 MILLER CLERK 7782 23-JAN-82 1300 10 40 OPERATIONS BOSTON
39 rows selected.
SQL>
SQL> ed
Wrote file afiedt.buf
1* select * from emp inner join dept where emp.deptno=dept.deptno
SQL> /
select * from emp inner join dept where emp.deptno=dept.deptno
*
ERROR at line 1:
ORA-00905: missing keyword
SQL> ed
Wrote file afiedt.buf
1* select * from emp inner join dept on emp.deptno=dept.deptno
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- -------------
7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK
14 rows selected.
SQL> ed
Wrote file afiedt.buf
1* select * from emp outer join dept on emp.deptno=dept.deptno
SQL> /
select * from emp outer join dept on emp.deptno=dept.deptno
*
ERROR at line 1:
ORA-00904: "EMP"."DEPTNO": invalid identifier
SQL> ed
Wrote file afiedt.buf
1* select * from emp,dept where emp.deptno=dept.deptno(+)
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- -------------
1021 122
7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK
15 rows selected.
SQL> ed
Wrote file afiedt.buf
1* select * from emp,dept where emp.deptno(+)=dept.deptno
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- -------------
7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK
7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCH DALLAS
7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES CHICAGO
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO
40 OPERATIONS BOSTON
15 rows selected.
SQL>
SQL> ed
Wrote file afiedt.buf
1* select * from emp full outer join dept on emp.deptno=dept.deptno
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- -------------
1021 122
7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK
40 OPERATIONS BOSTON
16 rows selected.
SQL> select * from emp a,emp b where a.empno=b.mgr;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- ---------- --------- --------
7902 FORD ANALYST 7566 03-DEC-81 3000 20 7369 SMITH CLERK 7902 17-DEC-80 800
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 3
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 50
7839 KING PRESIDENT 17-NOV-81 5000 10 7566 JONES MANAGER 7839 02-APR-81 2975
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1
7839 KING PRESIDENT 17-NOV-81 5000 10 7698 BLAKE MANAGER 7839 01-MAY-81 2850
7839 KING PRESIDENT 17-NOV-81 5000 10 7782 CLARK MANAGER 7839 09-JUN-81 2450
7566 JONES MANAGER 7839 02-APR-81 2975 20 7788 SCOTT ANALYST 7566 19-APR-87 3000
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7844 TURNER SALESMAN 7698 08-SEP-81 1500
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7876 ADAMS CLERK 7788 23-MAY-87 1100
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7900 JAMES CLERK 7698 03-DEC-81 950
7566 JONES MANAGER 7839 02-APR-81 2975 20 7902 FORD ANALYST 7566 03-DEC-81 3000
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7934 MILLER CLERK 7782 23-JAN-82 1300
13 rows selected.
SQL>
catisian join?(cross join)
natural join
equi join(by using = operator)
non eqi-join
self join(joining a table by itself)
inner join
outer join---left outer join,right outer join,full outer join
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1021 122
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 10
15 rows selected.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from emp,dept;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- -------------
1021 122 10 ACCOUNTING NEW YORK
7369 SMITH CLERK 7902 17-DEC-80 800 20 10 ACCOUNTING NEW YORK
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 10 ACCOUNTING NEW YORK
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 10 ACCOUNTING NEW YORK
7566 JONES MANAGER 7839 02-APR-81 2975 20 10 ACCOUNTING NEW YORK
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 10 ACCOUNTING NEW YORK
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 10 ACCOUNTING NEW YORK
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 10 ACCOUNTING NEW YORK
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 10 ACCOUNTING NEW YORK
7900 JAMES CLERK 7698 03-DEC-81 950 30 10 ACCOUNTING NEW YORK
7902 FORD ANALYST 7566 03-DEC-81 3000 20 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK
1021 122 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 20 RESEARCH DALLAS
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 20 RESEARCH DALLAS
7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 20 RESEARCH DALLAS
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 20 RESEARCH DALLAS
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 17-NOV-81 5000 10 20 RESEARCH DALLAS
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-DEC-81 950 30 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 23-JAN-82 1300 10 20 RESEARCH DALLAS
1021 122 30 SALES CHICAGO
7369 SMITH CLERK 7902 17-DEC-80 800 20 30 SALES CHICAGO
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 02-APR-81 2975 20 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 30 SALES CHICAGO
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 30 SALES CHICAGO
7839 KING PRESIDENT 17-NOV-81 5000 10 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 30 SALES CHICAGO
7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 03-DEC-81 3000 20 30 SALES CHICAGO
7934 MILLER CLERK 7782 23-JAN-82 1300 10 30 SALES CHICAGO
1021 122 40 OPERATIONS BOSTON
7369 SMITH CLERK 7902 17-DEC-80 800 20 40 OPERATIONS BOSTON
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 40 OPERATIONS BOSTON
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 40 OPERATIONS BOSTON
7566 JONES MANAGER 7839 02-APR-81 2975 20 40 OPERATIONS BOSTON
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 40 OPERATIONS BOSTON
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 40 OPERATIONS BOSTON
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 40 OPERATIONS BOSTON
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 40 OPERATIONS BOSTON
7839 KING PRESIDENT 17-NOV-81 5000 10 40 OPERATIONS BOSTON
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 40 OPERATIONS BOSTON
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 40 OPERATIONS BOSTON
7900 JAMES CLERK 7698 03-DEC-81 950 30 40 OPERATIONS BOSTON
7902 FORD ANALYST 7566 03-DEC-81 3000 20 40 OPERATIONS BOSTON
7934 MILLER CLERK 7782 23-JAN-82 1300 10 40 OPERATIONS BOSTON
60 rows selected.
SQL>
SQL> ed
Wrote file afiedt.buf
1* select * from emp natural join dept
SQL> /
DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DNAME LOC
---------- ---------- ---------- --------- ---------- --------- ---------- ---------- -------------- -------------
20 7369 SMITH CLERK 7902 17-DEC-80 800 RESEARCH DALLAS
30 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 SALES CHICAGO
30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 SALES CHICAGO
20 7566 JONES MANAGER 7839 02-APR-81 2975 RESEARCH DALLAS
30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 SALES CHICAGO
30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 SALES CHICAGO
10 7782 CLARK MANAGER 7839 09-JUN-81 2450 ACCOUNTING NEW YORK
20 7788 SCOTT ANALYST 7566 19-APR-87 3000 RESEARCH DALLAS
10 7839 KING PRESIDENT 17-NOV-81 5000 ACCOUNTING NEW YORK
30 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 SALES CHICAGO
20 7876 ADAMS CLERK 7788 23-MAY-87 1100 RESEARCH DALLAS
30 7900 JAMES CLERK 7698 03-DEC-81 950 SALES CHICAGO
20 7902 FORD ANALYST 7566 03-DEC-81 3000 RESEARCH DALLAS
10 7934 MILLER CLERK 7782 23-JAN-82 1300 ACCOUNTING NEW YORK
14 rows selected.
SQL>
SQL> select * from emp,dept where emp.deptno=dept.deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- -------------
7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK
14 rows selected.
SQL>
SQL> ed
Wrote file afiedt.buf
1* select * from emp,dept where emp.deptno>=dept.deptno
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- -------------
7369 SMITH CLERK 7902 17-DEC-80 800 20 10 ACCOUNTING NEW YORK
7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 10 ACCOUNTING NEW YORK
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 10 ACCOUNTING NEW YORK
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 20 RESEARCH DALLAS
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 02-APR-81 2975 20 10 ACCOUNTING NEW YORK
7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 10 ACCOUNTING NEW YORK
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 10 ACCOUNTING NEW YORK
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 20 RESEARCH DALLAS
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 20 RESEARCH DALLAS
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 10 ACCOUNTING NEW YORK
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-DEC-81 950 30 10 ACCOUNTING NEW YORK
7900 JAMES CLERK 7698 03-DEC-81 950 30 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 03-DEC-81 3000 20 10 ACCOUNTING NEW YORK
7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK
31 rows selected.
SQL> ed
Wrote file afiedt.buf
1* select * from emp,dept where emp.deptno<=dept.deptno
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- -------------
7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 17-DEC-80 800 20 30 SALES CHICAGO
7369 SMITH CLERK 7902 17-DEC-80 800 20 40 OPERATIONS BOSTON
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 40 OPERATIONS BOSTON
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 40 OPERATIONS BOSTON
7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCH DALLAS
7566 JONES MANAGER 7839 02-APR-81 2975 20 30 SALES CHICAGO
7566 JONES MANAGER 7839 02-APR-81 2975 20 40 OPERATIONS BOSTON
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 40 OPERATIONS BOSTON
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 40 OPERATIONS BOSTON
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 20 RESEARCH DALLAS
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 30 SALES CHICAGO
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 40 OPERATIONS BOSTON
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 30 SALES CHICAGO
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 40 OPERATIONS BOSTON
7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 17-NOV-81 5000 10 20 RESEARCH DALLAS
7839 KING PRESIDENT 17-NOV-81 5000 10 30 SALES CHICAGO
7839 KING PRESIDENT 17-NOV-81 5000 10 40 OPERATIONS BOSTON
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 40 OPERATIONS BOSTON
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 30 SALES CHICAGO
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 40 OPERATIONS BOSTON
7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES CHICAGO
7900 JAMES CLERK 7698 03-DEC-81 950 30 40 OPERATIONS BOSTON
7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 03-DEC-81 3000 20 30 SALES CHICAGO
7902 FORD ANALYST 7566 03-DEC-81 3000 20 40 OPERATIONS BOSTON
7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 7782 23-JAN-82 1300 10 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 23-JAN-82 1300 10 30 SALES CHICAGO
7934 MILLER CLERK 7782 23-JAN-82 1300 10 40 OPERATIONS BOSTON
39 rows selected.
SQL>
SQL> ed
Wrote file afiedt.buf
1* select * from emp inner join dept where emp.deptno=dept.deptno
SQL> /
select * from emp inner join dept where emp.deptno=dept.deptno
*
ERROR at line 1:
ORA-00905: missing keyword
SQL> ed
Wrote file afiedt.buf
1* select * from emp inner join dept on emp.deptno=dept.deptno
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- -------------
7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK
14 rows selected.
SQL> ed
Wrote file afiedt.buf
1* select * from emp outer join dept on emp.deptno=dept.deptno
SQL> /
select * from emp outer join dept on emp.deptno=dept.deptno
*
ERROR at line 1:
ORA-00904: "EMP"."DEPTNO": invalid identifier
SQL> ed
Wrote file afiedt.buf
1* select * from emp,dept where emp.deptno=dept.deptno(+)
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- -------------
1021 122
7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK
15 rows selected.
SQL> ed
Wrote file afiedt.buf
1* select * from emp,dept where emp.deptno(+)=dept.deptno
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- -------------
7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK
7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCH DALLAS
7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES CHICAGO
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO
40 OPERATIONS BOSTON
15 rows selected.
SQL>
SQL> ed
Wrote file afiedt.buf
1* select * from emp full outer join dept on emp.deptno=dept.deptno
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- -------------
1021 122
7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK
40 OPERATIONS BOSTON
16 rows selected.
SQL> select * from emp a,emp b where a.empno=b.mgr;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- ---------- --------- --------
7902 FORD ANALYST 7566 03-DEC-81 3000 20 7369 SMITH CLERK 7902 17-DEC-80 800
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 3
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 50
7839 KING PRESIDENT 17-NOV-81 5000 10 7566 JONES MANAGER 7839 02-APR-81 2975
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1
7839 KING PRESIDENT 17-NOV-81 5000 10 7698 BLAKE MANAGER 7839 01-MAY-81 2850
7839 KING PRESIDENT 17-NOV-81 5000 10 7782 CLARK MANAGER 7839 09-JUN-81 2450
7566 JONES MANAGER 7839 02-APR-81 2975 20 7788 SCOTT ANALYST 7566 19-APR-87 3000
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7844 TURNER SALESMAN 7698 08-SEP-81 1500
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7876 ADAMS CLERK 7788 23-MAY-87 1100
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7900 JAMES CLERK 7698 03-DEC-81 950
7566 JONES MANAGER 7839 02-APR-81 2975 20 7902 FORD ANALYST 7566 03-DEC-81 3000
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7934 MILLER CLERK 7782 23-JAN-82 1300
13 rows selected.
SQL>
No comments:
Post a Comment