OR 改写union数据变少

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