13增加右外连接，显示部门40的信息
Oracle
SQL> select *
2 from emp e,dept d
3 where e.deptno(+)=d.deptno;
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.PPAS
scott=# select *
scott-# from emp e,dept d
scott-# where e.deptno(+)=d.deptno;
empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | dname | loc
-------+--------+-----------+------+--------------------+---------+---------+--------+--------+------------+----------
7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20 | 20 | RESEARCH | DALLAS
7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO
7521 | WARD | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO
7566 | JONES | MANAGER | 7839 | 02-APR-81 00:00:00 | 2975.00 | | 20 | 20 | RESEARCH | DALLAS
7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO
7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 00:00:00 | 2850.00 | | 30 | 30 | SALES | CHICAGO
7782 | CLARK | MANAGER | 7839 | 09-JUN-81 00:00:00 | 2450.00 | | 10 | 10 | ACCOUNTING | NEW YORK
7788 | SCOTT | ANALYST | 7566 | 19-APR-87 00:00:00 | 3000.00 | | 20 | 20 | RESEARCH | DALLAS
7839 | KING | PRESIDENT | | 17-NOV-81 00:00:00 | 5000.00 | | 10 | 10 | ACCOUNTING | NEW YORK
7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO
7876 | ADAMS | CLERK | 7788 | 23-MAY-87 00:00:00 | 1100.00 | | 20 | 20 | RESEARCH | DALLAS
7900 | JAMES | CLERK | 7698 | 03-DEC-81 00:00:00 | 950.00 | | 30 | 30 | SALES | CHICAGO
7902 | FORD | ANALYST | 7566 | 03-DEC-81 00:00:00 | 3000.00 | | 20 | 20 | RESEARCH | DALLAS
7934 | MILLER | CLERK | 7782 | 23-JAN-82 00:00:00 | 1300.00 | | 10 | 10 | ACCOUNTING | NEW YORK
| | | | | | | | 40 | OPERATIONS | BOSTON
(15 rows)自身关联
14取出emp表中的全部数据
Oracle
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- ---------- ------------ ---------- ---------- ----------
8888 SongYuejie CLERK 7369 22-JUN-16 800 100
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.PPAS
scott=# select * from emp;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+------------+-----------+------+--------------------+---------+---------+--------
7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20
7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30
7521 | WARD | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 | 30
7566 | JONES | MANAGER | 7839 | 02-APR-81 00:00:00 | 2975.00 | | 20
7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 | 30
7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 00:00:00 | 2850.00 | | 30
7782 | CLARK | MANAGER | 7839 | 09-JUN-81 00:00:00 | 2450.00 | | 10
7788 | SCOTT | ANALYST | 7566 | 19-APR-87 00:00:00 | 3000.00 | | 20
7839 | KING | PRESIDENT | | 17-NOV-81 00:00:00 | 5000.00 | | 10
7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 | 0.00 | 30
7876 | ADAMS | CLERK | 7788 | 23-MAY-87 00:00:00 | 1100.00 | | 20
7900 | JAMES | CLERK | 7698 | 03-DEC-81 00:00:00 | 950.00 | | 30
7902 | FORD | ANALYST | 7566 | 03-DEC-81 00:00:00 | 3000.00 | | 20
7934 | MILLER | CLERK | 7782 | 23-JAN-82 00:00:00 | 1300.00 | | 10
8888 | SongYuejie | CLERK | 7369 | 22-JUN-16 11:09:17 | 800.00 | 100.00 |
(15 rows)15查询出每个雇员的编号、姓名、及其上级领导的编号、姓名
Oracle
SQL> select e.empno eno,e.ename ename,m.empno mno,m.ename mname
2 from emp e,emp m
3 where e.mgr=m.empno;
ENO ENAME MNO MNAME
---------- ---------- ---------- --------------------
8888 SongYuejie 7369 SMITH
7902 FORD 7566 JONES
7788 SCOTT 7566 JONES
7844 TURNER 7698 BLAKE
7900 JAMES 7698 BLAKE
7521 WARD 7698 BLAKE
7499 ALLEN 7698 BLAKE
7654 MARTIN 7698 BLAKE
7934 MILLER 7782 CLARK
7876 ADAMS 7788 SCOTT
7782 CLARK 7839 KING
7566 JONES 7839 KING
7698 BLAKE 7839 KING
7369 SMITH 7902 FORD
14 rows selected.PPAS
scott=# select e.empno eno,e.ename ename,m.empno mno,m.ename mname
scott-# from emp e,emp m
scott-# where e.mgr=m.empno;
eno | ename | mno | mname
------+------------+------+-------
7369 | SMITH | 7902 | FORD
7499 | ALLEN | 7698 | BLAKE
7521 | WARD | 7698 | BLAKE
7566 | JONES | 7839 | KING
7654 | MARTIN | 7698 | BLAKE
7698 | BLAKE | 7839 | KING
7782 | CLARK | 7839 | KING
7788 | SCOTT | 7566 | JONES
7844 | TURNER | 7698 | BLAKE
7876 | ADAMS | 7788 | SCOTT
7900 | JAMES | 7698 | BLAKE
7902 | FORD | 7566 | JONES
7934 | MILLER | 7782 | CLARK
8888 | SongYuejie | 7369 | SMITH
(14 rows)16查询出在1981年雇佣的全部雇员的编号、姓名、雇用日期（按照年-月-日显示）、工作、领导姓名、雇员月工资、雇员年工资（基本工资+奖金），雇员工资等级、部门编号、部门名称、部门位置、并且要求这些雇员的月基本工资在1500~3500元之间，将最后的结果按照年工资的降序排列，如果年工资相等，则按照工作时间进行排序
Oracle
SQL> SELECT e.empno,e.ename,e.hiredate,e.sal,(e.sal+NVL(e.comm,0))*12 income , m.ename mname ,
2 d.deptno,d.dname,d.loc ,
3 s.grade, DECODE(s.grade,1,'E等工资',2,'D等工资',3,'C等工资',4,'B等工资',5,'A等工资') 工资等级
4 FROM emp e , emp m , dept d , salgrade s
5 WHERE TO_CHAR(e.hiredate,'yyyy')='1981' AND e.sal BETWEEN 1500 AND 3500
6 AND e.mgr=m.empno(+)
7 AND e.deptno=d.deptno
8 AND e.sal BETWEEN s.losal AND s.hisal
9 ORDER BY income DESC , e.job ;
EMPNO ENAME HIREDATE SAL INCOME MNAME DEPTNO DNAME LOC GRADE 工资等
---------- ---------- ------------ ---------- ---------- -------------------- ---------- --------------- ---------- ---------- --------------------
7902 FORD 03-DEC-81 3000 36000 JONES 20 RESEARCH DALLAS 4 B等工资
7566 JONES 02-APR-81 2975 35700 KING 20 RESEARCH DALLAS 4 B等工资
7698 BLAKE 01-MAY-81 2850 34200 KING 30 SALES CHICAGO 4 B等工资
7782 CLARK 09-JUN-81 2450 29400 KING 10 ACCOUNTING NEW YORK 4 B等工资
7499 ALLEN 20-FEB-81 1600 22800 BLAKE 30 SALES CHICAGO 3 C等工资
7844 TURNER 08-SEP-81 1500 18000 BLAKE 30 SALES CHICAGO 3 C等工资
6 rows selected.PPAS
scott=# SELECT e.empno,e.ename,e.hiredate,e.sal,(e.sal+NVL(e.comm,0))*12 income , m.ename mname ,
scott-# d.deptno,d.dname,d.loc ,
scott-# s.grade, DECODE(s.grade,1,'E等工资',2,'D等工资',3,'C等工资',4,'B等工资',5,'A等工资') 工资等级
scott-# FROM emp e , emp m , dept d , salgrade s
scott-# WHERE TO_CHAR(e.hiredate,'yyyy')='1981' AND e.sal BETWEEN 1500 AND 3500
scott-# AND e.mgr=m.empno(+)
scott-# AND e.deptno=d.deptno
scott-# AND e.sal BETWEEN s.losal AND s.hisal
scott-# ORDER BY income DESC , e.job ;
empno | ename | hiredate | sal | income | mname | deptno | dname | loc | grade | 工资等级
-------+--------+--------------------+---------+----------+-------+--------+------------+----------+-------+----------
7902 | FORD | 03-DEC-81 00:00:00 | 3000.00 | 36000.00 | JONES | 20 | RESEARCH | DALLAS | 4 | B等工资
7566 | JONES | 02-APR-81 00:00:00 | 2975.00 | 35700.00 | KING | 20 | RESEARCH | DALLAS | 4 | B等工资
7698 | BLAKE | 01-MAY-81 00:00:00 | 2850.00 | 34200.00 | KING | 30 | SALES | CHICAGO | 4 | B等工资
7782 | CLARK | 09-JUN-81 00:00:00 | 2450.00 | 29400.00 | KING | 10 | ACCOUNTING | NEW YORK | 4 | B等工资
7499 | ALLEN | 20-FEB-81 00:00:00 | 1600.00 | 22800.00 | BLAKE | 30 | SALES | CHICAGO | 3 | C等工资
7844 | TURNER | 08-SEP-81 00:00:00 | 1500.00 | 18000.00 | BLAKE | 30 | SALES | CHICAGO | 3 | C等工资
(6 rows)17使用交叉连接（CROSS JOIN），产生笛卡尔积
Oracle
SQL> select *
2 from emp
3 CROSS JOIN dept;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- --------------- ----------
8888 SongYuejie CLERK 7369 22-JUN-16 800 100 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
8888 SongYuejie CLERK 7369 22-JUN-16 800 100 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
8888 SongYuejie CLERK 7369 22-JUN-16 800 100 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
8888 SongYuejie CLERK 7369 22-JUN-16 800 100 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.PPAS
scott=# select *
scott-# from emp
scott-# CROSS JOIN dept;
empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | dname | loc
-------+------------+-----------+------+--------------------+---------+---------+--------+--------+------------+----------
7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20 | 10 | ACCOUNTING | NEW YORK
7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20 | 20 | RESEARCH | DALLAS
7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20 | 30 | SALES | CHICAGO
7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20 | 40 | OPERATIONS | BOSTON
7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30 | 10 | ACCOUNTING | NEW YORK
7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30 | 20 | RESEARCH | DALLAS
7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO
7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30 | 40 | OPERATIONS | BOSTON
7521 | WARD | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 | 30 | 10 | ACCOUNTING | NEW YORK
| 30 | 20 | RESEARCH | DALLAS| 22-FEB-81 00:00:00 | 1250.00 | 500.00 --More--
| 30 | 30 | SALES | CHICAGO 22-FEB-81 00:00:00 | 1250.00 | 500.00 --More--
| 30 | 40 | OPERATIONS | BOSTON| 22-FEB-81 00:00:00 | 1250.00 | 500.00 --More--
| 20 | 10 | ACCOUNTING | NEW YORK02-APR-81 00:00:00 | 2975.00 | --More--
| 20 | 20 | RESEARCH | DALLAS| 02-APR-81 00:00:00 | 2975.00 | --More--
| 20 | 30 | SALES | CHICAGO 02-APR-81 00:00:00 | 2975.00 | --More--
| 20 | 40 | OPERATIONS | BOSTON| 02-APR-81 00:00:00 | 2975.00 | --More--
| 30 | 10 | ACCOUNTING | NEW YORK28-SEP-81 00:00:00 | 1250.00 | 1400.00 --More--
| 30 | 20 | RESEARCH | DALLAS| 28-SEP-81 00:00:00 | 1250.00 | 1400.00 --More--
| 30 | 30 | SALES | CHICAGO 28-SEP-81 00:00:00 | 1250.00 | 1400.00 --More--
| 30 | 40 | OPERATIONS | BOSTON| 28-SEP-81 00:00:00 | 1250.00 | 1400.00 --More--
| 30 | 10 | ACCOUNTING | NEW YORK01-MAY-81 00:00:00 | 2850.00 | --More--
| 30 | 20 | RESEARCH | DALLAS| 01-MAY-81 00:00:00 | 2850.00 | --More--
| 30 | 30 | SALES | CHICAGO 01-MAY-81 00:00:00 | 2850.00 | --More--
| 30 | 40 | OPERATIONS | BOSTON| 01-MAY-81 00:00:00 | 2850.00 | --More--
| 10 | 10 | ACCOUNTING | NEW YORK09-JUN-81 00:00:00 | 2450.00 | --More--
| 10 | 20 | RESEARCH | DALLAS| 09-JUN-81 00:00:00 | 2450.00 | --More--
| 10 | 30 | SALES | CHICAGO 09-JUN-81 00:00:00 | 2450.00 | --More--
| 10 | 40 | OPERATIONS | BOSTON| 09-JUN-81 00:00:00 | 2450.00 | --More--
| 20 | 10 | ACCOUNTING | NEW YORK19-APR-87 00:00:00 | 3000.00 | --More--
| 20 | 20 | RESEARCH | DALLAS| 19-APR-87 00:00:00 | 3000.00 | --More--
| 20 | 30 | SALES | CHICAGO 19-APR-87 00:00:00 | 3000.00 | --More--
| 20 | 40 | OPERATIONS | BOSTON| 19-APR-87 00:00:00 | 3000.00 | --More--
| 10 | 10 | ACCOUNTING | NEW YORK17-NOV-81 00:00:00 | 5000.00 | --More--
| 10 | 20 | RESEARCH | DALLAS| 17-NOV-81 00:00:00 | 5000.00 | --More--
| 10 | 30 | SALES | CHICAGO 17-NOV-81 00:00:00 | 5000.00 | --More--
| 10 | 40 | OPERATIONS | BOSTON| 17-NOV-81 00:00:00 | 5000.00 | --More--
| 30 | 10 | ACCOUNTING | NEW YORK08-SEP-81 00:00:00 | 1500.00 | 0.00 --More--
| 30 | 20 | RESEARCH | DALLAS| 08-SEP-81 00:00:00 | 1500.00 | 0.00 --More--
| 30 | 30 | SALES | CHICAGO 08-SEP-81 00:00:00 | 1500.00 | 0.00 --More--
| 30 | 40 | OPERATIONS | BOSTON| 08-SEP-81 00:00:00 | 1500.00 | 0.00 --More--
| 20 | 10 | ACCOUNTING | NEW YORK23-MAY-87 00:00:00 | 1100.00 | --More--
| 20 | 20 | RESEARCH | DALLAS| 23-MAY-87 00:00:00 | 1100.00 | --More--
| 20 | 30 | SALES | CHICAGO 23-MAY-87 00:00:00 | 1100.00 | --More--
| 20 | 40 | OPERATIONS | BOSTON| 23-MAY-87 00:00:00 | 1100.00 | --More--
| 30 | 10 | ACCOUNTING | NEW YORK03-DEC-81 00:00:00 | 950.00 | --More--
| 30 | 20 | RESEARCH | DALLAS| 03-DEC-81 00:00:00 | 950.00 | --More--
| 30 | 30 | SALES | CHICAGO 03-DEC-81 00:00:00 | 950.00 | --More--
| 30 | 40 | OPERATIONS | BOSTON| 03-DEC-81 00:00:00 | 950.00 | --More--
| 20 | 10 | ACCOUNTING | NEW YORK03-DEC-81 00:00:00 | 3000.00 | --More--
| 20 | 20 | RESEARCH | DALLAS| 03-DEC-81 00:00:00 | 3000.00 | --More--
| 20 | 30 | SALES | CHICAGO 03-DEC-81 00:00:00 | 3000.00 | --More--
| 20 | 40 | OPERATIONS | BOSTON| 03-DEC-81 00:00:00 | 3000.00 | --More--
| 10 | 10 | ACCOUNTING | NEW YORK23-JAN-82 00:00:00 | 1300.00 | --More--
| 10 | 20 | RESEARCH | DALLAS| 23-JAN-82 00:00:00 | 1300.00 | --More--
| 10 | 30 | SALES | CHICAGO 23-JAN-82 00:00:00 | 1300.00 | --More--
| 10 | 40 | OPERATIONS | BOSTON| 23-JAN-82 00:00:00 | 1300.00 | --More--
| | 10 | ACCOUNTING | NEW YORK22-JUN-16 11:09:17 | 800.00 | 100.00 --More--
| | 20 | RESEARCH | DALLAS| 22-JUN-16 11:09:17 | 800.00 | 100.00 --More--
| | 30 | SALES | CHICAGO 22-JUN-16 11:09:17 | 800.00 | 100.00 --More--
| | 40 | OPERATIONS | BOSTON| 22-JUN-16 11:09:17 | 800.00 | 100.00 --More--
(60 rows)18使用自然连接
Oracle
SQL> select *
2 from emp NATURAL JOIN dept;
DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DNAME LOC
---------- ---------- ---------- ---------- ---------- ------------ ---------- ---------- --------------- ----------
10 7839 KING PRESIDENT 17-NOV-81 5000 ACCOUNTING NEW YORK
10 7934 MILLER CLERK 7782 23-JAN-82 1300 ACCOUNTING NEW YORK
10 7782 CLARK MANAGER 7839 09-JUN-81 2450 ACCOUNTING NEW YORK
20 7369 SMITH CLERK 7902 17-DEC-80 800 RESEARCH DALLAS
20 7902 FORD ANALYST 7566 03-DEC-81 3000 RESEARCH DALLAS
20 7876 ADAMS CLERK 7788 23-MAY-87 1100 RESEARCH DALLAS
20 7788 SCOTT ANALYST 7566 19-APR-87 3000 RESEARCH DALLAS
20 7566 JONES MANAGER 7839 02-APR-81 2975 RESEARCH DALLAS
30 7900 JAMES CLERK 7698 03-DEC-81 950 SALES CHICAGO
30 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 SALES CHICAGO
30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 SALES CHICAGO
30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 SALES CHICAGO
30 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 SALES CHICAGO
30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 SALES CHICAGO
14 rows selected.PPAS
scott=# select *
scott-# from emp NATURAL JOIN dept;
deptno | empno | ename | job | mgr | hiredate | sal | comm | dname | loc
--------+-------+--------+-----------+------+--------------------+---------+---------+------------+----------
20 | 7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | RESEARCH | DALLAS
30 | 7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | SALES | CHICAGO
30 | 7521 | WARD | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 | SALES | CHICAGO
20 | 7566 | JONES | MANAGER | 7839 | 02-APR-81 00:00:00 | 2975.00 | | RESEARCH | DALLAS
30 | 7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 | SALES | CHICAGO
30 | 7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 00:00:00 | 2850.00 | | SALES | CHICAGO
10 | 7782 | CLARK | MANAGER | 7839 | 09-JUN-81 00:00:00 | 2450.00 | | ACCOUNTING | NEW YORK
20 | 7788 | SCOTT | ANALYST | 7566 | 19-APR-87 00:00:00 | 3000.00 | | RESEARCH | DALLAS
10 | 7839 | KING | PRESIDENT | | 17-NOV-81 00:00:00 | 5000.00 | | ACCOUNTING | NEW YORK
30 | 7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 | 0.00 | SALES | CHICAGO
20 | 7876 | ADAMS | CLERK | 7788 | 23-MAY-87 00:00:00 | 1100.00 | | RESEARCH | DALLAS
30 | 7900 | JAMES | CLERK | 7698 | 03-DEC-81 00:00:00 | 950.00 | | SALES | CHICAGO
20 | 7902 | FORD | ANALYST | 7566 | 03-DEC-81 00:00:00 | 3000.00 | | RESEARCH | DALLAS
10 | 7934 | MILLER | CLERK | 7782 | 23-JAN-82 00:00:00 | 1300.00 | | ACCOUNTING | NEW YORK
(14 rows)19USING子句，直接使用JOIN进行连接，同时指定关联的列
Oracle
SQL> select *
2 from emp JOIN dept USING(deptno);
DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DNAME LOC
---------- ---------- ---------- ---------- ---------- ------------ ---------- ---------- --------------- ----------
10 7839 KING PRESIDENT 17-NOV-81 5000 ACCOUNTING NEW YORK
10 7934 MILLER CLERK 7782 23-JAN-82 1300 ACCOUNTING NEW YORK
10 7782 CLARK MANAGER 7839 09-JUN-81 2450 ACCOUNTING NEW YORK
20 7369 SMITH CLERK 7902 17-DEC-80 800 RESEARCH DALLAS
20 7902 FORD ANALYST 7566 03-DEC-81 3000 RESEARCH DALLAS
20 7876 ADAMS CLERK 7788 23-MAY-87 1100 RESEARCH DALLAS
20 7788 SCOTT ANALYST 7566 19-APR-87 3000 RESEARCH DALLAS
20 7566 JONES MANAGER 7839 02-APR-81 2975 RESEARCH DALLAS
30 7900 JAMES CLERK 7698 03-DEC-81 950 SALES CHICAGO
30 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 SALES CHICAGO
30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 SALES CHICAGO
30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 SALES CHICAGO
30 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 SALES CHICAGO
30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 SALES CHICAGO
14 rows selected.PPAS
scott=# select *
scott-# from emp JOIN dept USING(deptno);
deptno | empno | ename | job | mgr | hiredate | sal | comm | dname | loc
--------+-------+--------+-----------+------+--------------------+---------+---------+------------+----------
20 | 7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | RESEARCH | DALLAS
30 | 7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | SALES | CHICAGO
30 | 7521 | WARD | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 | SALES | CHICAGO
20 | 7566 | JONES | MANAGER | 7839 | 02-APR-81 00:00:00 | 2975.00 | | RESEARCH | DALLAS
30 | 7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 | SALES | CHICAGO
30 | 7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 00:00:00 | 2850.00 | | SALES | CHICAGO
10 | 7782 | CLARK | MANAGER | 7839 | 09-JUN-81 00:00:00 | 2450.00 | | ACCOUNTING | NEW YORK
20 | 7788 | SCOTT | ANALYST | 7566 | 19-APR-87 00:00:00 | 3000.00 | | RESEARCH | DALLAS
10 | 7839 | KING | PRESIDENT | | 17-NOV-81 00:00:00 | 5000.00 | | ACCOUNTING | NEW YORK
30 | 7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 | 0.00 | SALES | CHICAGO
20 | 7876 | ADAMS | CLERK | 7788 | 23-MAY-87 00:00:00 | 1100.00 | | RESEARCH | DALLAS
30 | 7900 | JAMES | CLERK | 7698 | 03-DEC-81 00:00:00 | 950.00 | | SALES | CHICAGO
20 | 7902 | FORD | ANALYST | 7566 | 03-DEC-81 00:00:00 | 3000.00 | | RESEARCH | DALLAS
10 | 7934 | MILLER | CLERK | 7782 | 23-JAN-82 00:00:00 | 1300.00 | | ACCOUNTING | NEW YORK
(14 rows)20ON子句，直接编写条件
Oracle
SQL> select *
2 from emp e JOIN salgrade s
3 ON(e.sal between s.losal and s.hisal);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO GRADE LOSAL HISAL
---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ---------- ----------
8888 SongYuejie CLERK 7369 22-JUN-16 800 100 1 700 1200
7369 SMITH CLERK 7902 17-DEC-80 800 20 1 700 1200
7900 JAMES CLERK 7698 03-DEC-81 950 30 1 700 1200
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 1 700 1200
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 2 1201 1400
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 2 1201 1400
7934 MILLER CLERK 7782 23-JAN-82 1300 10 2 1201 1400
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 3 1401 2000
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 3 1401 2000
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 4 2001 3000
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 4 2001 3000
7566 JONES MANAGER 7839 02-APR-81 2975 20 4 2001 3000
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 4 2001 3000
7902 FORD ANALYST 7566 03-DEC-81 3000 20 4 2001 3000
7839 KING PRESIDENT 17-NOV-81 5000 10 5 3001 9999
15 rows selected.PPAS
scott=# select *
scott-# from emp e JOIN salgrade s
scott-# ON(e.sal between s.losal and s.hisal);
empno | ename | job | mgr | hiredate | sal | comm | deptno | grade | losal | hisal
-------+------------+-----------+------+--------------------+---------+---------+--------+-------+-------+-------
7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20 | 1 | 700 | 1200
7876 | ADAMS | CLERK | 7788 | 23-MAY-87 00:00:00 | 1100.00 | | 20 | 1 | 700 | 1200
7900 | JAMES | CLERK | 7698 | 03-DEC-81 00:00:00 | 950.00 | | 30 | 1 | 700 | 1200
8888 | SongYuejie | CLERK | 7369 | 22-JUN-16 11:09:17 | 800.00 | 100.00 | | 1 | 700 | 1200
7521 | WARD | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 | 30 | 2 | 1201 | 1400
7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 | 30 | 2 | 1201 | 1400
7934 | MILLER | CLERK | 7782 | 23-JAN-82 00:00:00 | 1300.00 | | 10 | 2 | 1201 | 1400
7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30 | 3 | 1401 | 2000
7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 | 0.00 | 30 | 3 | 1401 | 2000
7566 | JONES | MANAGER | 7839 | 02-APR-81 00:00:00 | 2975.00 | | 20 | 4 | 2001 | 3000
7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 00:00:00 | 2850.00 | | 30 | 4 | 2001 | 3000
7782 | CLARK | MANAGER | 7839 | 09-JUN-81 00:00:00 | 2450.00 | | 10 | 4 | 2001 | 3000
7788 | SCOTT | ANALYST | 7566 | 19-APR-87 00:00:00 | 3000.00 | | 20 | 4 | 2001 | 3000
7902 | FORD | ANALYST | 7566 | 03-DEC-81 00:00:00 | 3000.00 | | 20 | 4 | 2001 | 3000
7839 | KING | PRESIDENT | | 17-NOV-81 00:00:00 | 5000.00 | | 10 | 5 | 3001 | 9999
(15 rows)21使用SQL：1999语法实现左外连接
Oracle
SQL> select *
2 from emp e LEFT OUTER JOIN dept d
3 ON(e.deptno=d.deptno);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- --------------- ----------
7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK
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
7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 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
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO
8888 SongYuejie CLERK 7369 22-JUN-16 800 100
15 rows selected.PPAS
scott=# select *
scott-# from emp e LEFT OUTER JOIN dept d
scott-# ON(e.deptno=d.deptno);
empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | dname | loc
-------+------------+-----------+------+--------------------+---------+---------+--------+--------+------------+----------
7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20 | 20 | RESEARCH | DALLAS
7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO
7521 | WARD | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO
7566 | JONES | MANAGER | 7839 | 02-APR-81 00:00:00 | 2975.00 | | 20 | 20 | RESEARCH | DALLAS
7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO
7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 00:00:00 | 2850.00 | | 30 | 30 | SALES | CHICAGO
7782 | CLARK | MANAGER | 7839 | 09-JUN-81 00:00:00 | 2450.00 | | 10 | 10 | ACCOUNTING | NEW YORK
7788 | SCOTT | ANALYST | 7566 | 19-APR-87 00:00:00 | 3000.00 | | 20 | 20 | RESEARCH | DALLAS
7839 | KING | PRESIDENT | | 17-NOV-81 00:00:00 | 5000.00 | | 10 | 10 | ACCOUNTING | NEW YORK
7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO
7876 | ADAMS | CLERK | 7788 | 23-MAY-87 00:00:00 | 1100.00 | | 20 | 20 | RESEARCH | DALLAS
7900 | JAMES | CLERK | 7698 | 03-DEC-81 00:00:00 | 950.00 | | 30 | 30 | SALES | CHICAGO
7902 | FORD | ANALYST | 7566 | 03-DEC-81 00:00:00 | 3000.00 | | 20 | 20 | RESEARCH | DALLAS
7934 | MILLER | CLERK | 7782 | 23-JAN-82 00:00:00 | 1300.00 | | 10 | 10 | ACCOUNTING | NEW YORK
8888 | SongYuejie | CLERK | 7369 | 22-JUN-16 11:09:17 | 800.00 | 100.00 | | | |
(15 rows)22使用SQL:1999语法实现右外连接
Oracle
SQL> select *
2 from emp e RIGHT OUTER JOIN dept d
3 ON(e.deptno=d.deptno);
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.PPAS
scott=# select *
scott-# from emp e RIGHT OUTER JOIN dept d
scott-# ON(e.deptno=d.deptno);
empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | dname | loc
-------+--------+-----------+------+--------------------+---------+---------+--------+--------+------------+----------
7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20 | 20 | RESEARCH | DALLAS
7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO
7521 | WARD | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO
7566 | JONES | MANAGER | 7839 | 02-APR-81 00:00:00 | 2975.00 | | 20 | 20 | RESEARCH | DALLAS
7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO
7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 00:00:00 | 2850.00 | | 30 | 30 | SALES | CHICAGO
7782 | CLARK | MANAGER | 7839 | 09-JUN-81 00:00:00 | 2450.00 | | 10 | 10 | ACCOUNTING | NEW YORK
7788 | SCOTT | ANALYST | 7566 | 19-APR-87 00:00:00 | 3000.00 | | 20 | 20 | RESEARCH | DALLAS
7839 | KING | PRESIDENT | | 17-NOV-81 00:00:00 | 5000.00 | | 10 | 10 | ACCOUNTING | NEW YORK
7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO
7876 | ADAMS | CLERK | 7788 | 23-MAY-87 00:00:00 | 1100.00 | | 20 | 20 | RESEARCH | DALLAS
7900 | JAMES | CLERK | 7698 | 03-DEC-81 00:00:00 | 950.00 | | 30 | 30 | SALES | CHICAGO
7902 | FORD | ANALYST | 7566 | 03-DEC-81 00:00:00 | 3000.00 | | 20 | 20 | RESEARCH | DALLAS
7934 | MILLER | CLERK | 7782 | 23-JAN-82 00:00:00 | 1300.00 | | 10 | 10 | ACCOUNTING | NEW YORK
| | | | | | | | 40 | OPERATIONS | BOSTON
(15 rows)23使用SQL：1999语法实现全外连接
Oracle
SQL> select *
2 from emp e FULL OUTER JOIN dept d
3 ON(e.deptno=d.deptno);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- --------------- ----------
8888 SongYuejie CLERK 7369 22-JUN-16 800 100
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.PPAS
scott=# select *
scott-# from emp e FULL OUTER JOIN dept d
scott-# ON(e.deptno=d.deptno);
empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | dname | loc
-------+------------+-----------+------+--------------------+---------+---------+--------+--------+------------+----------
7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20 | 20 | RESEARCH | DALLAS
7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO
7521 | WARD | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO
7566 | JONES | MANAGER | 7839 | 02-APR-81 00:00:00 | 2975.00 | | 20 | 20 | RESEARCH | DALLAS
7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO
7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 00:00:00 | 2850.00 | | 30 | 30 | SALES | CHICAGO
7782 | CLARK | MANAGER | 7839 | 09-JUN-81 00:00:00 | 2450.00 | | 10 | 10 | ACCOUNTING | NEW YORK
7788 | SCOTT | ANALYST | 7566 | 19-APR-87 00:00:00 | 3000.00 | | 20 | 20 | RESEARCH | DALLAS
7839 | KING | PRESIDENT | | 17-NOV-81 00:00:00 | 5000.00 | | 10 | 10 | ACCOUNTING | NEW YORK
| 30 | 30 | SALES | CHICAGO 08-SEP-81 00:00:00 | 1500.00 | 0.00 --More--
| 20 | 20 | RESEARCH | DALLAS| 23-MAY-87 00:00:00 | 1100.00 | --More--
| 30 | 30 | SALES | CHICAGO 03-DEC-81 00:00:00 | 950.00 | --More--
| 20 | 20 | RESEARCH | DALLAS| 03-DEC-81 00:00:00 | 3000.00 | --More--
| 10 | 10 | ACCOUNTING | NEW YORK23-JAN-82 00:00:00 | 1300.00 | --More--
| | | | 7369 | 22-JUN-16 11:09:17 | 800.00 | 100.00 --More--
| | 40 | OPERATIONS | BOSTON| | | --More--
(16 rows)24查询dept表的全部记录
Oracle
SQL> select * from dept;
DEPTNO DNAME LOC
---------- --------------- ----------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTONPPAS
scott=# select * from dept;
deptno | dname | loc
--------+------------+----------
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
30 | SALES | CHICAGO
40 | OPERATIONS | BOSTON
(4 rows)25查询10部门的详细记录
Oracle
SQL> select * from dept where deptno = 10;
DEPTNO DNAME LOC
---------- --------------- ----------
10 ACCOUNTING NEW YORKPPAS
scott=# select * from dept where deptno = 10;
deptno | dname | loc
--------+------------+----------
10 | ACCOUNTING | NEW YORK
(1 row)26使用UNION将两个查询结果连接
Oracle
SQL> select * from dept
2 UNION
3 select * from dept where deptno=10;
DEPTNO DNAME LOC
---------- --------------- ----------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTONPPAS
scott=# select * from dept
scott-# UNION
scott-# select * from dept where deptno=10;
deptno | dname | loc
--------+------------+----------
10 | ACCOUNTING | NEW YORK
30 | SALES | CHICAGO
40 | OPERATIONS | BOSTON
20 | RESEARCH | DALLAS
(4 rows)27使用UNION ALL将两个查询结果连接
Oracle
SQL> select * from dept
2 UNION ALL
3 select * from dept where deptno=10;
DEPTNO DNAME LOC
---------- --------------- ----------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
10 ACCOUNTING NEW YORKPPAS
scott=# select * from dept
scott-# UNION ALL
scott-# select * from dept where deptno=10;
deptno | dname | loc
--------+------------+----------
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
30 | SALES | CHICAGO
40 | OPERATIONS | BOSTON
10 | ACCOUNTING | NEW YORK
(5 rows)28使用UNION代替OR，查询所有办事员与销售人员的信息
Oralce
SQL> select * from emp where job='SALESMAN'
2 UNION
3 select * from emp where job='CLERK';
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
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
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
7934 MILLER CLERK 7782 23-JAN-82 1300 10
8888 SongYuejie CLERK 7369 22-JUN-16 800 100
9 rows selected.PPAS
scott=# select * from emp where job='SALESMAN'
scott-# UNION
scott-# select * from emp where job='CLERK';
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+------------+----------+------+--------------------+---------+---------+--------
7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20
7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30
7521 | WARD | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 | 30
7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 | 30
7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 | 0.00 | 30
7876 | ADAMS | CLERK | 7788 | 23-MAY-87 00:00:00 | 1100.00 | | 20
7900 | JAMES | CLERK | 7698 | 03-DEC-81 00:00:00 | 950.00 | | 30
7934 | MILLER | CLERK | 7782 | 23-JAN-82 00:00:00 | 1300.00 | | 10
8888 | SongYuejie | CLERK | 7369 | 22-JUN-16 11:09:17 | 800.00 | 100.00 |
(9 rows)29使用MINUS执行差集操作
Oracle
SQL> select * from dept
2 MINUS
3 select * from dept where deptno=10;
DEPTNO DNAME LOC
---------- --------------- ----------
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTONPPAS
scott=# select * from dept
scott-# MINUS
scott-# select * from dept where deptno=10;
deptno | dname | loc
--------+------------+---------
30 | SALES | CHICAGO
40 | OPERATIONS | BOSTON
20 | RESEARCH | DALLAS
(3 rows)30使用INTERSECT执行交集操作
Oracle
SQL> select * from dept
2 INTERSECT
3 select * from dept where deptno=10;
DEPTNO DNAME LOC
---------- --------------- ----------
10 ACCOUNTING NEW YORKPPAS
scott=# select * from dept
scott-# INTERSECT
scott-# select * from dept where deptno=10;
deptno | dname | loc
--------+------------+----------
10 | ACCOUNTING | NEW YORK
(1 row)本连载博客主要探讨Oracle与PPAS(PostgreSQL)数据库的差异，以帮助更多读者了解如何实现数据库迁移！