SQL union operator

SQL UNION Operator ( Union operator )

UNION The operator is used to merge two or more SELECT The result set of the statement .

- UNION Each of the SELECT Statement must have the same number of columns

- These columns are in order , There must be similar data types

UNION grammar

SELECT column_name(s) FROM table1

UNION

SELECT column_name(s) FROM table2;

UNION ALL grammar

UNION The different values after the combination of the two result sets obtained by the operator . Allow duplicate values , Please use UNION ALL.

SELECT column_name(s) FROM table1

UNION ALL

SELECT column_name(s) FROM table2;

Be careful ： The column names in the result set usually use the first SELECT Column names in statements .

UNION The non repetition of the operator means , Only when the data in a row are different .

Example

Use Microsoft's famous sample database ：Northwind.

Customers table One of the select result :

Suppliers table One of the select result ：

perform UNION Achieve non repetitive results ：

SELECT City FROM Customers

UNION

SELECT City FROM Suppliers

ORDER BY City;

Use UNION ALL Merge all results , Include repeated ：

SELECT City FROM Customers

UNION ALL

SELECT City FROM Suppliers

ORDER BY City;

add WHERE Clause ：

SELECT City, Country FROM Customers

WHERE Country='Germany'

UNION

SELECT City, Country FROM Suppliers

WHERE Country='Germany'

ORDER BY City;

Add one more Type Field , Used to distinguish customer and supplier type ：

SELECT 'Customer' AS Type, ContactName, City, Country

FROM Customers

UNION

SELECT 'Supplier', ContactName, City, Country

FROM Suppliers;

Reference resources ：

SQL UNION Operator