问题：要知道两个表或视图中是否有相同的数据（基数和值）。考虑这个视图：

create view V

as 

    select * from emp where deptno !=10

        union all

    select * from emp where ename = 'WARD'   

现要检测这个视图与表emp中的数据是否完全相同。员工“WARD”行重复，说明解决方案不仅要显示不同行，还要显示重复行。

解决方案：

1.首先，查找出表emp中存在而试图v中没有的行

2.然后合并（union all）在视图V中存在，而在表emp中没有的行。

select * from 

    (

        select e.empno,e.ename,e.job,e.mgr,e.hiredate,

            e.sal,e.comm,e.deptno,count(*)  as cnt

        from emp e

        group by empno,ename,job,mgr,hiredate,sal,comm,deptno

    ) e

where not exists

    (

    select null from

        (

            select v.empno,v.ename,v.job,v.mgr,v.hiredate,v.sal,v.comm,v.deptno

                count (*) as cnt

            from v

            group by empno,ename,job,mgr,hiredate,sal,comm,deptno

        )v

        where v.empno = e.empno

            and v.ename = e.ename

            and v.job = e.job

            and v.mgr = e.mgr

            and v.hiredate = e.hiredate

            and v.sal = e.sal

            and v.deptno = e.deptno

            and v.cnt = e.cnt

            and coalesce(v.comm.0) = coalesce(e.comm,0)

    )

    union all

    select * from 

    (

        select e.empno ,e.ename,e.ename,e.job,e.mgr,e.hiredate,e.sal ,e.comm,e.deptno,count(*) as cnt

        from v 

        group by empno,ename,job,mgr,hiredate,sal,comm,deptno

    )v

    where not exists (

        select null from (

            select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno,count(*) as cnt

            from emp e

            group by empno,ename,job,mgr,hiredate,sal,comm,deptno

            )e

        where v.empno = e.empno

            and v.ename = e.ename

            and v.job = e.job

            and v.mgr = e.mgr

            and v.hiredate = e.hiredate

            and v.sal = e.sal

            and v.deptno = e.deptno

            and v.cnt = e.cnt

            and coalesce(v.comm.0) = coalesce(e.comm,0)

    );