>>-+-subselect---------+----------------------------------------> +-(fullselect)------+ '-| values-clause |-' .----------------------------------------------. V | >----+------------------------------------------+-+-------------> '-+-UNION---------+--+-subselect---------+-' +-UNION ALL-----+ +-(fullselect)------+ +-EXCEPT--------+ '-| values-clause |-' +-EXCEPT ALL----+ +-INTERSECT-----+ '-INTERSECT ALL-' >--+-----------------+--+--------------------+------------------> '-order-by-clause-' '-fetch-first-clause-' >--+------------------+---------------------------------------->< '-isolation-clause-' values-clause .-,--------------. V | |--VALUES----| values-row |-+-----------------------------------| values-row |--+-+-expression-----+-------+---------------------------------| | +-NULL-----------+ | | '-row-expression-' | | .-,--------------. | | V | | '-(----+-expression-+-+--)-' '-NULL-------'
The fullselect is a component of the select-statement, the INSERT statement, and the CREATE VIEW statement. It is also a component of certain predicates which, in turn, are components of a statement. A fullselect that is a component of a predicate is called a subquery, and a fullselect that is enclosed in parentheses is sometimes called a subquery.
The set operators UNION, EXCEPT, and INTERSECT correspond to the relational operators union, difference, and intersection.
A fullselect specifies a result table. If a set operator is not used, the result of the fullselect is the result of the specified subselect or values-clause.
The authorization for a fullselect is described in the Authorization section in "SQL queries".
NULL can only be used with multiple specifications of values-row, either as the column value of a single column result table or within a row-expression, and at least one row in the same column must not be NULL (SQLSTATE 42608).
A multiple row VALUES clause must have the same number of columns in each values-row (SQLSTATE 42826).
VALUES (1),(2),(3) - 3 rows of 1 column
VALUES 1, 2, 3 - 3 rows of 1 column
VALUES (1, 2, 3) - 1 row of 3 columns
VALUES (1,21),(2,22),(3,23) - 3 rows of 2 columns
RE1 UNION ALL RE2 ... UNION ALL REn
This means that the corresponding columns of each values-row must be comparable (SQLSTATE 42825).
For compatibility with other SQL implementations, MINUS can be specified as a synonym for EXCEPT.
SELECT NAME FROM PRODUCT
UNION
SELECT NAME FROM CATALOG
WITH UR
Even though the isolation clause WITH
UR could apply only to the subselect SELECT NAME FROM CATALOG, it
is applied to the whole fullselect.The number of columns in the result tables R1 and R2 must be the same (SQLSTATE 42826). If the ALL keyword is not specified, R1 and R2 must not include any columns having a data type of CLOB, DBCLOB, BLOB, distinct type on any of these types, or structured type (SQLSTATE 42907).
The column name of the nth column of the result table is the name of the nth column of R1 if it is named. Otherwise, the nth column of the result table is unnamed. If the fullselect is used as a select-statement, a generated name is provided when the statement is described. The generated name cannot be used in other parts of the SQL statement such as the ORDER BY clause or the UPDATE clause. The generated name can be determined by performing a DESCRIBE of the SQL statement and consulting the SQLNAME field.
The generated name can be determined by performing a DESCRIBE of the SQL statement and consulting the SQLNAME field.
Duplicate rows: Two rows are duplicates if each value in the first is equal to the corresponding value of the second. For determining duplicates, two null values are considered equal, and two decimal floating-point representations of the same number are considered equal. For example, 2.00 and 2.0 have the same value (2.00 and 2.0 compare as equal) but have different exponents, which allows you to represent both 2.00 and 2.0. So, for example, if the result table of a UNION operation contains a decimal floating-point column and multiple representations of the same number exist, the one that is returned (for example, 2.00 or 2.0) is unpredictable. For more information, see Numeric comparisons.
When multiple operations are combined in an expression, operations within parentheses are performed first. If there are no parentheses, the operations are performed from left to right with the exception that all INTERSECT operations are performed before UNION or EXCEPT operations.
In the following example, the values of tables R1 and R2 are shown on the left. The other headings listed show the values as a result of various set operations on R1 and R2.
R1 | R2 | UNION ALL | UNION | EXCEPT ALL | EXCEPT | INTER- SECT ALL | INTER- SECT |
---|---|---|---|---|---|---|---|
1 | 1 | 1 | 1 | 1 | 2 | 1 | 1 |
1 | 1 | 1 | 2 | 2 | 5 | 1 | 3 |
1 | 3 | 1 | 3 | 2 | 3 | 4 | |
2 | 3 | 1 | 4 | 2 | 4 | ||
2 | 3 | 1 | 5 | 4 | |||
2 | 3 | 2 | 5 | ||||
3 | 4 | 2 | |||||
4 | 2 | ||||||
4 | 3 | ||||||
5 | 3 | ||||||
3 | |||||||
3 | |||||||
3 | |||||||
4 | |||||||
4 | |||||||
4 | |||||||
5 |
SELECT * FROM EMPLOYEE
SELECT EMPNO
FROM EMPLOYEE
WHERE WORKDEPT LIKE 'E%'
UNION
SELECT EMPNO
FROM EMP_ACT
WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112')
SELECT EMPNO, 'emp'
FROM EMPLOYEE
WHERE WORKDEPT LIKE 'E%'
UNION
SELECT EMPNO, 'emp_act' FROM EMP_ACT
WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112')
SELECT EMPNO
FROM EMPLOYEE
WHERE WORKDEPT LIKE 'E%'
UNION ALL
SELECT EMPNO
FROM EMP_ACT
WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112')
SELECT EMPNO, 'emp'
FROM EMPLOYEE
WHEREWORKDEPTLIKE 'E%'
UNION
SELECT EMPNO, 'emp_act'
FROM EMP_ACT
WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112')
UNION
VALUES ('NEWAAA', 'new'), ('NEWBBB', 'new')
(SELECT * FROM T1)
EXCEPT ALL
(SELECT * FROM T2)
SELECT ALL *
FROM T1
WHERE NOT EXISTS (SELECT * FROM T2
WHERE T1.C1 = T2.C1 AND T1.C2 = T2.C2 AND...)
(SELECT * FROM T1)
INTERSECT
(SELECT * FROM T2)
SELECT DISTINCT * FROM T1
WHERE EXISTS (SELECT * FROM T2
WHERE T1.C1 = T2.C1 AND T1.C2 = T2.C2 AND...)
where
C1, C2, and so on represent the columns of T1 and T2.