å•?å?ˆã?›ã?«ã‚ˆã‚Šã€?æ—¢å˜è¡¨ã‚„表ã?«çµ„è¾¼ã?¿ã?®å®šæ•°ã‚’基ã?«ã?—ã?¦ã€?仮想的ã?ªè¡¨ã?Œä½œã‚‰ã‚Œã?¾ã?™ã€‚
{ ( Query ) | Query INTERSECT [ ALL | DISTINCT ] Query | Query EXCEPT [ ALL | DISTINCT ] Query | Query UNION [ ALL | DISTINCT ] Query | �択� | VALUES� }
å•?å?ˆã?›ã?®å‰?後ã?«ä»»æ„?ã?§æ‹¬å¼§ã‚’ç½®ã??ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚ã?¾ã?Ÿã€?INTERSECTã€?EXCEPTã€?UNION演算å?ã?®è©•ä¾¡é †ã‚’æ‹¬å¼§ã‚’ä½¿ã?£ã?¦å¤‰ã?ˆã‚‹ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚INTERSECTを除ã??ã?“れらã?®æ¼”ç®—å?ã?¯ã€?括弧ã?Œã?ªã?‘れã?°å·¦ã?‹ã‚‰å?³ã?«è©•価ã?•れã?¾ã?™ã€‚INTERSECTã?¯UNIONã?‚ã‚‹ã?„ã?¯EXCEPTよりå‰?ã?«è©•価ã?•れã?¾ã?™ã€‚
ALLã?¨DISTINCTã?¨ã?„ã?†ã‚ーワードã?«ã‚ˆã‚Šå‡¦ç?†çµ?æžœã?‹ã‚‰é‡?複をå?–り除ã??ã?‹å?¦ã?‹ã?Œæ±ºã?¾ã‚Šã?¾ã?™ã€‚ DISTINCTã?¨ã?„ã?†ã‚ーワードã?Œã?‚れã?°ã€?çµ?æžœã?«é‡?複行ã?¯ã?‚りã?¾ã?›ã‚“。ALLã?¨ã?„ã?†ã‚ーワードã?Œã?‚れã?°ã€?入力ã?«ã‚ˆã?£ã?¦çµ?æžœã?«é‡?複行ã?Œã?‚ã‚‹å ´å?ˆã?Œã?‚りã?¾ã?™ã€‚既定ã?¯DISTINCTã?ªã?®ã?§ã€?ALLã?‹DISTINCTã?‹æŒ‡å®šã?—ã?ªã?‘れã?°é‡?複ã?¯å?–り除ã?‹ã‚Œã?¾ã?™ã€‚例ã?ˆã?°UNIONã?®å ´å?ˆã?¯ã€?両方ã?®å•?å?ˆã?›ã?®å…¨è¡Œã‚’å…ƒã?«ä¸€æ™‚çš„ã?ªResultSetを生æˆ?ã?—ã?Ÿã?®ã?¡ã€?é‡?複行をå?–り除ã?„ã?¦ã?‹ã‚‰çµ?果を返ã?—ã?¾ã?™ã€‚ã?“れã?ŒUNION ALLã?ªã‚‰ã?°ã€?両方ã?®å•?å?ˆã?›ã?®å…¨è¡Œã?Œè¿”ã?•れã?¾ã?™ã€‚
-- é?¸æŠžå¼? 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')