unionåunion allçåºå«æ¯
unionä¼èªå¨å缩å¤ä¸ªç»æéåä¸çéå¤ç»æ
union allåå°ææçç»æå ¨é¨æ¾ç¤ºåºæ¥ï¼ä¸ç®¡æ¯ä¸æ¯éå¤ã
Unionï¼å¯¹ä¸¤ä¸ªç»æéè¿è¡å¹¶éæä½ï¼ä¸å
æ¬éå¤è¡ï¼åæ¶è¿è¡é»è®¤è§åçæåºï¼
UNIONå¨è¿è¡è¡¨é¾æ¥åä¼çéæéå¤çè®°å½ï¼æ以å¨è¡¨é¾æ¥åä¼å¯¹æ产ççç»æéè¿è¡æåºè¿ç®ï¼å é¤éå¤çè®°å½åè¿åç»æãå®é 大é¨ååºç¨ä¸æ¯ä¸ä¼äº§çéå¤çè®°å½ï¼æ常è§çæ¯è¿ç¨è¡¨ä¸åå²è¡¨UNION
union Allï¼å¯¹ä¸¤ä¸ªç»æéè¿è¡å¹¶éæä½ï¼å
æ¬éå¤è¡ï¼ä¸è¿è¡æåºï¼
å¦æè¿åç两个ç»æéä¸æéå¤çæ°æ®ï¼é£ä¹è¿åçç»æéå°±ä¼å å«éå¤çæ°æ®äºã
Intersectï¼å¯¹ä¸¤ä¸ªç»æéè¿è¡äº¤éæä½ï¼ä¸å æ¬éå¤è¡ï¼åæ¶è¿è¡é»è®¤è§åçæåºï¼
Minusï¼å¯¹ä¸¤ä¸ªç»æéè¿è¡å·®æä½ï¼ä¸å æ¬éå¤è¡ï¼åæ¶è¿è¡é»è®¤è§åçæåºã
å¯ä»¥å¨æåä¸ä¸ªç»æéä¸æå®Order byåå¥æ¹åæåºæ¹å¼ã
ä¾å¦ï¼
select employee_id,job_id from employees
union
select employee_id,job_id from job_history
以ä¸å°ä¸¤ä¸ªè¡¨çç»æèåå¨ä¸èµ·ãè¿ä¸¤ä¸ªä¾åä¼å°ä¸¤ä¸ªselectè¯å¥çç»æä¸çéå¤å¼è¿è¡å缩ï¼ä¹å°±æ¯ç»æçæ°æ®å¹¶ä¸æ¯ä¸¤æ¡ç»æçæ¡æ°çåãå¦æå¸æå³ä½¿éå¤çç»ææ¾ç¤ºåºæ¥å¯ä»¥ä½¿ç¨union all,ä¾å¦ï¼
2.å¨oracleçscottç¨æ·ä¸æ表emp
select * from emp where deptno >= 20
union all
select * from emp where deptno <= 30
è¿éçç»æå°±æå¾å¤éå¤å¼äºã
æå ³unionåunion allå ³é®åéè¦æ³¨æçé®é¢æ¯ï¼
union å union allé½å¯ä»¥å°å¤ä¸ªç»æéå并ï¼èä¸ä»
ä»
æ¯ä¸¤ä¸ªï¼ä½ å¯ä»¥å°å¤ä¸ªç»æé串起æ¥ã
使ç¨unionåunion allå¿
é¡»ä¿è¯å个select éåçç»ææç¸å个æ°çåï¼å¹¶ä¸æ¯ä¸ªåçç±»åæ¯ä¸æ ·çãä½åååä¸ä¸å®éè¦ç¸åï¼oracleä¼å°ç¬¬ä¸ä¸ªç»æçååä½ä¸ºç»æéçååãä¾å¦ä¸é¢æ¯ä¸ä¸ªä¾åï¼
select empno,ename from emp
union
select deptno,dname from dept
没æå¿
è¦å¨æ¯ä¸ä¸ªselectç»æéä¸ä½¿ç¨order byåå¥æ¥è¿è¡æåºï¼å¯ä»¥å¨æå使ç¨ä¸æ¡order byæ¥å¯¹æ´ä¸ªç»æè¿è¡æåºãä¾å¦ï¼
select empno,ename from emp
union
select deptno,dname from dept