OR 改写union数据变少

<pre name="code" class="sql">SQL> SELECT deptno FROM emp WHERE mgr = 7698 OR job = 'SALESMAN' ORDER BY 1;

    DEPTNO
----------
        30
        30
        30
        30
        30

SQL> SELECT deptno FROM emp WHERE mgr = 7698
  2  UNION 
  3  SELECT deptno FROM emp WHERE job = 'SALESMAN'
  4  ORDER BY 1;

    DEPTNO
----------
        30

SQL> 


这样的情况下UNION去从后就不等价了,这时候须要加上主键列,假设没有主键列能够使用rowid或者rownum

SQL>  SELECT rownum,deptno FROM emp WHERE mgr = 7698 OR job = 'SALESMAN' ORDER BY 1;

    ROWNUM     DEPTNO
---------- ----------
         1         30
         2         30
         3         30
         4         30
         5         30

SQL> SELECT rownum,deptno FROM emp WHERE mgr = 7698
  2  UNION 
  3  SELECT rownum,deptno FROM emp WHERE job = 'SALESMAN'
  4  ORDER BY 1;


    ROWNUM     DEPTNO
---------- ----------
         1         30
         2         30
         3         30
         4         30
         5         30