sql union代替or

---原始SQL
SQL> SELECT deptno FROM emp WHERE empno = 7788 OR job = 'SALESMAN' ORDER BY 1;

    DEPTNO
----------
	20
	30
	30
	30
	30

SQL> SELECT deptno FROM emp WHERE empno = 7788
UNION
SELECT deptno FROM emp WHERE job = 'SALESMAN';  2    3  

    DEPTNO
----------
	20
	30


为什么丢数据呢？

SQL> SELECT deptno FROM emp WHERE empno = 7788;

    DEPTNO
----------
	20

SQL> SELECT deptno FROM emp WHERE job = 'SALESMAN';

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


原来emp表被去从数据了

SQL> SELECT deptno
FROM
(
SELECT empno,deptno FROM emp WHERE empno = 7788
UNION
SELECT empno,deptno FROM emp WHERE job = 'SALESMAN'
)
ORDER BY 1;  2    3    4    5    6    7    8  

    DEPTNO
----------
	20
	30
	30
	30
	30


SQL> SELECT empno,deptno FROM emp WHERE empno = 7788;

     EMPNO     DEPTNO
---------- ----------
      7788	   20

SQL> SELECT empno,deptno FROM emp WHERE job = 'SALESMAN';

     EMPNO     DEPTNO
---------- ----------
      7499	   30
      7521	   30
      7654	   30
      7844	   30

加上empno唯一列后,就不会在去从的时候丢失数据。

转载于:https://www.cnblogs.com/zhaoyangjian724/p/3797912.html