The SQL UNION operator combines the result of two or more SELECT statements.
The SQL UNION Operator
The UNION operator is used to combine the result-set of two or more SELECT statements.
Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.
SQL UNION Syntax
UNION
SELECT column_name(s) FROM table2;
Note: The UNION operator selects only distinct values by default. To allow duplicate values, use the ALL keyword with UNION.
SQL UNION ALL Syntax
UNION ALL
SELECT column_name(s) FROM table2;
PS: The column names in the result-set of a UNION are usually equal to the column names in the first SELECT statement in the UNION.
SQL UNION Example
The following SQL statement selects all the different cities (only distinct values) from the "Customers" and the "Suppliers" tables:
Note: UNION cannot be used to list ALL cities from the two tables. If several customers and suppliers share the same city, each city will only be listed once. UNION selects only distinct values. Use UNION ALL to also select duplicate values!
SQL UNION ALL Example
The following SQL statement uses UNION ALL to select all (duplicate values also) cities from the "Customers" and "Suppliers" tables:
SQL UNION ALL With WHERE
The following SQL statement uses UNION ALL to select all (duplicate values also) German cities from the "Customers" and "Suppliers" tables:
Example
WHERE Country='Germany'
UNION ALL
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;
Try it yourself »
'SQL' 카테고리의 다른 글
CREATE DATABASE Statement (0) | 2015.06.11 |
---|---|
INSERT INTO SELECT Statement (0) | 2015.06.11 |
SELECT INTO Statement (0) | 2015.06.10 |
FULL OUTER JOIN (0) | 2015.06.10 |
RIGHT JOIN (0) | 2015.06.08 |