SQL UNION
In SQL, the UNION operator is used to
combine the result sets of two or more SELECT statements into a single result
set. The UNION operator removes duplicate rows from the combined result set,
and the columns in the SELECT statements must have the same number of columns
and compatible data types.
UNION
Syntax
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
UNION
ALL Syntax
The UNION operator selects only distinct values by
default. To allow duplicate values, use UNION
ALL:
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
The syntax for using UNION is as follows:
SELECT
column1, column2, ...
FROM
table1
UNION
SELECT
column1, column2, ...
FROM
table2;
In this syntax:
- SELECT specifies the
columns you want to retrieve from the SELECT statement.
- FROM specifies the
table you want to retrieve data from in the first SELECT statement.
- UNION is used to
specify the union operation.
- SELECT specifies the
columns you want to retrieve from the SELECT statement in the second
SELECT statement.
- FROM specifies the
table you want to retrieve data from in the second SELECT statement.
Note that the number of columns and
their data types must be the same in both SELECT statements. Also, the columns
are combined vertically, meaning that the first SELECT statement's result set
will be followed by the second SELECT statement's result set in the combined
result set.
For example, let's say you have two
tables, customers and suppliers, and you want to retrieve a
combined list of names and addresses from both tables. You can use UNION as
follows:
SELECT
customer_name, customer_address
FROM
customers
UNION
SELECT
supplier_name, supplier_address
FROM
suppliers;
This query will retrieve the customer_name
and customer_address columns from the customers table, and the supplier_name
and supplier_address columns from the suppliers table, and
combine them into a single result set. Duplicate rows will be removed from the
combined result set. Note that the column names and data types must be
compatible in both SELECT statements to use UNION.
SQL UNION
Example
The following SQL statement returns the cities (only distinct values) from both the "Customers" and the "Suppliers" table:
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;
Note: If some customers or suppliers have the same city, each city will only be listed once, because UNION selects only distinct values. Use UNION ALL to also select duplicate values!
SQL UNION ALL Example
The following SQL statement returns the cities (duplicate values also) from both the "Customers" and the "Suppliers" table:
Example
SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;
SQL UNION With WHERE
The following SQL statement returns the German cities (only distinct values) from both the "Customers" and the "Suppliers" table:
Example
SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;
SQL UNION ALL With WHERE
The following SQL statement returns the German cities (duplicate values also) from both the "Customers" and the "Suppliers" table:
Example
SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION ALL
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;
Another UNION Example
The following SQL statement lists all customers and suppliers:
Example
SELECT 'Customer' AS
Type, ContactName, City, Country
FROM Customers
UNION
SELECT 'Supplier', ContactName, City, Country
FROM Suppliers;
0 Comments