���

����より�既存表や表�組込��定数を基����仮想的�表�作られ��。

Syntax

{
    ( Query ) |
    Query INTERSECT [ ALL | DISTINCT ] Query |
    Query EXCEPT [ ALL | DISTINCT ] Query |
    Query UNION [ ALL | DISTINCT ] Query |
    �択� | VALUES�
}

å•?å?ˆã?›ã?®å‰?後ã?«ä»»æ„?ã?§æ‹¬å¼§ã‚’ç½®ã??ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚ã?¾ã?Ÿã€?INTERSECTã€?EXCEPTã€?UNION演算å­?ã?®è©•価順を括弧を使ã?£ã?¦å¤‰ã?ˆã‚‹ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚INTERSECTを除ã??ã?“れらã?®æ¼”ç®—å­?ã?¯ã€?括弧ã?Œã?ªã?‘れã?°å·¦ã?‹ã‚‰å?³ã?«è©•価ã?•れã?¾ã?™ã€‚INTERSECTã?¯UNIONã?‚ã‚‹ã?„ã?¯EXCEPTよりå‰?ã?«è©•価ã?•れã?¾ã?™ã€‚

UNION�INTERSECT�EXCEPT ALL���る�果��複

ALLã?¨DISTINCTã?¨ã?„ã?†ã‚­ãƒ¼ãƒ¯ãƒ¼ãƒ‰ã?«ã‚ˆã‚Šå‡¦ç?†çµ?æžœã?‹ã‚‰é‡?複をå?–り除ã??ã?‹å?¦ã?‹ã?Œæ±ºã?¾ã‚Šã?¾ã?™ã€‚ DISTINCTã?¨ã?„ã?†ã‚­ãƒ¼ãƒ¯ãƒ¼ãƒ‰ã?Œã?‚れã?°ã€?çµ?æžœã?«é‡?複行ã?¯ã?‚りã?¾ã?›ã‚“。ALLã?¨ã?„ã?†ã‚­ãƒ¼ãƒ¯ãƒ¼ãƒ‰ã?Œã?‚れã?°ã€?入力ã?«ã‚ˆã?£ã?¦çµ?æžœã?«é‡?複行ã?Œã?‚ã‚‹å ´å?ˆã?Œã?‚りã?¾ã?™ã€‚既定ã?¯DISTINCTã?ªã?®ã?§ã€?ALLã?‹DISTINCTã?‹æŒ‡å®šã?—ã?ªã?‘れã?°é‡?複ã?¯å?–り除ã?‹ã‚Œã?¾ã?™ã€‚例ã?ˆã?°UNIONã?®å ´å?ˆã?¯ã€?両方ã?®å•?å?ˆã?›ã?®å…¨è¡Œã‚’å…ƒã?«ä¸€æ™‚çš„ã?ªResultSetを生æˆ?ã?—ã?Ÿã?®ã?¡ã€?é‡?複行をå?–り除ã?„ã?¦ã?‹ã‚‰çµ?果を返ã?—ã?¾ã?™ã€‚ã?“れã?ŒUNION ALLã?ªã‚‰ã?°ã€?両方ã?®å•?å?ˆã?›ã?®å…¨è¡Œã?Œè¿”ã?•れã?¾ã?™ã€‚

左表ã?«ã?‚ã‚‹é‡?複ã?—ã?Ÿè¡Œã?®æ•°ã‚’Lã€?å?³è¡¨ã?«ã?‚る左表ã?¨å?Œã?˜å†…容ã?§é‡?複ã?—ã?Ÿè¡Œã?®æ•°ã‚’Rã?¨ã?™ã‚‹ã?¨ã€?ã?©ã?®æ“?作を行ã?†ã?‹ã?«ã‚ˆã‚Šã€?çµ?果表ã?«ã?‚ã‚‹é‡?複ã?—ã?Ÿè¡Œã?®æ•°ã?¯æ¬¡ã?®ã‚ˆã?†ã?«ã??ã?¾ã‚Šã?¾ã?™ã€‚(ALLã?ŒæŒ‡å®šã?•れã?Ÿã?¨ã?—ã?¾ã?™ã€‚)
  • UNION: ( L + R )。
  • EXCEPT: ( L – R )ã?¨0(é›¶)ã?®ã?†ã?¡å¤§ã??ã?„ã?»ã?†
  • INTERSECT: Lã?¨Rã?®ã?†ã?¡å°?ã?•ã?„ã?»ã?†

例

-- �択� 
SELECT *
FROM ORG

-- 副��� 
SELECT *
FROM (SELECT CLASS_CODE FROM CL_SCHED) AS CS

-- 副���
SELECT *
FROM (SELECT CLASS_CODE FROM CL_SCHED) AS CS (CLASS_CODE)

-- UNION�より
-- ORG���表��る全行�
-- DEPTNUMB�MANAGER���列�加��
-- (1,2)�(3,4)を返�。
-- ���DEPTNUMB�MANAGER�smallint型�列��る。
SELECT DEPTNUMB, MANAGER 
FROM ORG
UNION ALL
VALUES (1,2), (3,4)

-- 値��
VALUES (1,2,3)

-- EMPLOYEE���表��部署番�(WORKDEPT)�'E'�始�る社員�よ��
-- EMP_ACT���表��プロジェクト番�(PROJNO)�
-- 'MA2100'�'MA2110'�る��'MA2112'�プロジェクト�アサイン�れ��る社員�
-- 社員番�(EMPNO)を一覧�る。
SELECT EMPNO
     FROM EMPLOYEE
     WHERE WORKDEPT LIKE 'E%'
  UNION
  SELECT EMPNO
     FROM EMP_ACT
     WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112')
-- ��例���内容����を行��EMPLOYEE���表�ら得�行��'emp'�
-- EMP_ACT���表�ら得�行��'emp_act'���"タグ"を��る。
-- ��ら�表�ら���を�ら��"タグ"��る���
-- ã?“ã?®å•?å?ˆã?›ã?¯å‰?ã?®å•?å?ˆã?›ã?¨ã?¯é?•ã?„ã€?å?Œã?˜EMPNOを一回より多ã??è¿”ã?™ã?“ã?¨ã?Œã?‚る。
SELECT EMPNO, 'emp'
     FROM EMPLOYEE
     WHERE WORKDEPT LIKE 'E%'
  UNION
  SELECT EMPNO, 'emp_act' FROM EMP_ACT
     WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112')
-- ��例�����内容������る。
-- UNION ALLã?ŒæŒ‡å®šã?•れã?Ÿã?®ã?§é‡?複ã?¯å?–り除ã?‹ã‚Œã?ªã??ã?ªã‚‹ã€‚
SELECT EMPNO
      FROM EMPLOYEE
      WHERE WORKDEPT LIKE 'E%'
  UNION ALL
  SELECT EMPNO
     FROM EMP_ACT
     WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112')
-- ��例�����内容������る。
-- ��れ�表�も���二人�社員を加���
-- ã??ã?®è¡Œã?«"new"ã?¨ã?„ã?†ã‚¿ã‚°ã‚’ã?¤ã?‘ã?Ÿã€‚
 SELECT EMPNO, 'emp'
     FROM EMPLOYEE
     WHERE WORKDEPT LIKE 'E%'
  UNION
  SELECT EMPNO, 'emp_act'
     FROM EMP_ACT
     WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112')
  UNION
     VALUES ('NEWAAA', 'new'), ('NEWBBB', 'new')