比较两表数据

CREATE OR REPLACE VIEW v AS
SELECT * FROM emp WHERE deptno != 10
UNION ALL
SELECT * FROM emp WHERE ename = 'WARD';

SQL> set linesize 200
SQL> select * from v where v.ename='WARD';

     EMPNO ENAME      JOB		MGR HIREDATE		  SAL	    COMM     DEPTNO
---------- ---------- ---------- ---------- -------------- ---------- ---------- ----------
      7521 WARD       SALESMAN	       7698 22-2月 -81		 1250	     500	 30
      7521 WARD       SALESMAN	       7698 22-2月 -81		 1250	     500	 30

SQL> select * from emp where ename='WARD';

     EMPNO ENAME      JOB		MGR HIREDATE		  SAL	    COMM     DEPTNO
---------- ---------- ---------- ---------- -------------- ---------- ---------- ----------
      7521 WARD       SALESMAN	       7698 22-2月 -81		 1250	     500	 30

用全关联的话:
SQL> SELECT v.empno, v.ename, b.empno, b.ename
  FROM v
  FULL JOIN emp b ON (b.empno = v.empno)
 WHERE (v.empno IS NULL OR b.empno IS NULL);  2    3    4  

     EMPNO ENAME	   EMPNO ENAME
---------- ---------- ---------- ----------
			    7839 KING
			    7782 CLARK
			    7934 MILLER

会丢失ENAME=WARD的数据,可以使用标量子查询:

SQL>  SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno,
               /*这儿使用的语句就叫标量子查询，以主表返回每一行的值为条件进行查询*/
               (SELECT COUNT(*) FROM v v2 WHERE v2.empno = v.empno) AS cnt
          FROM v  2    3    4  ;

     EMPNO ENAME      JOB		MGR HIREDATE		  SAL	    COMM     DEPTNO	   CNT
---------- ---------- ---------- ---------- -------------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK	       7902 17-12月-80		  800			 20	     1
      7499 ALLEN      SALESMAN	       7698 20-2月 -81		 1600	     300	 30	     1
      7521 WARD       SALESMAN	       7698 22-2月 -81		 1250	     500	 30	     2
      7566 JONES      MANAGER	       7839 02-4月 -81		 2975			 20	     1
      7654 MARTIN     SALESMAN	       7698 28-9月 -81		 1250	    1400	 30	     1
      7698 BLAKE      MANAGER	       7839 01-5月 -81		 2850			 30	     1
      7788 SCOTT      ANALYST	       7566 09-12月-82		 3000			 20	     1
      7844 TURNER     SALESMAN	       7698 08-9月 -81		 1500	       0	 30	     1
      7876 ADAMS      CLERK	       7788 12-1月 -83		 1100			 20	     1
      7900 JAMES      CLERK	       7698 03-12月-81		  950			 30	     1
      7902 FORD       ANALYST	       7566 03-12月-81		 3000			 20	     1

     EMPNO ENAME      JOB		MGR HIREDATE		  SAL	    COMM     DEPTNO	   CNT
---------- ---------- ---------- ---------- -------------- ---------- ---------- ---------- ----------
      7521 WARD       SALESMAN	       7698 22-2月 -81		 1250	     500	 30	     2

已选择12行。

SQL> SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno,
                    (SELECT COUNT(*) FROM emp b WHERE b.empno = emp.empno) AS cnt
               FROM emp  2    3  ;

     EMPNO ENAME      JOB		MGR HIREDATE		  SAL	    COMM     DEPTNO	   CNT
---------- ---------- ---------- ---------- -------------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK	       7902 17-12月-80		  800			 20	     1
      7499 ALLEN      SALESMAN	       7698 20-2月 -81		 1600	     300	 30	     1
      7521 WARD       SALESMAN	       7698 22-2月 -81		 1250	     500	 30	     1
      7566 JONES      MANAGER	       7839 02-4月 -81		 2975			 20	     1
      7654 MARTIN     SALESMAN	       7698 28-9月 -81		 1250	    1400	 30	     1
      7698 BLAKE      MANAGER	       7839 01-5月 -81		 2850			 30	     1
      7782 CLARK      MANAGER	       7839 09-6月 -81		 2450			 10	     1
      7788 SCOTT      ANALYST	       7566 09-12月-82		 3000			 20	     1
      7839 KING       PRESIDENT 	    17-11月-81		 5000			 10	     1
      7844 TURNER     SALESMAN	       7698 08-9月 -81		 1500	       0	 30	     1
      7876 ADAMS      CLERK	       7788 12-1月 -83		 1100			 20	     1

     EMPNO ENAME      JOB		MGR HIREDATE		  SAL	    COMM     DEPTNO	   CNT
---------- ---------- ---------- ---------- -------------- ---------- ---------- ---------- ----------
      7900 JAMES      CLERK	       7698 03-12月-81		  950			 30	     1
      7902 FORD       ANALYST	       7566 03-12月-81		 3000			 20	     1
      7934 MILLER     CLERK	       7782 23-1月 -82		 1300			 10	     1

已选择14行。


SQL>      SELECT v.empno, v.ename, v.cnt, emp.empno, emp.ename, emp.cnt
  FROM (SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno,
               /*这儿使用的语句就叫标量子查询，以主表返回每一行的值为条件进行查询*/
               (SELECT COUNT(*) FROM v v2 WHERE v2.empno = v.empno) AS cnt
          FROM v) v
  FULL JOIN (SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno,
                    (SELECT COUNT(*) FROM emp b WHERE b.empno = emp.empno) AS cnt
               FROM emp) emp
    ON (emp.empno = v.empno AND emp.cnt = v.cnt)
WHERE (v.empno IS NULL OR emp.empno IS NULL);  2    3    4    5    6    7    8    9   10  

     EMPNO ENAME	     CNT      EMPNO ENAME	      CNT
---------- ---------- ---------- ---------- ---------- ----------
      7521 WARD 	       2
      7521 WARD 	       2
				       7521 WARD		1
				       7782 CLARK		1
				       7839 KING		1
				       7934 MILLER		1

已选择6行。