Overview Of SQL Union Operator
The UNION operator is used to JOIN the result-set of two or more two selected statements.
Using UNION, multiple SELECT statements can be described, and their results can be combined into a single result set.
Each query in a UNION must include the same columns, expressions or combined functions Column data types must be appropriate
The column names in the conclusion are normally balanced with the column names in the first SELECT statement in the UNION.
UNION Syntax
SELECT column_name FROM table1
UNION
SELECT column_name FROM table2;
Table name Order
| Order Id | Customer Number | Order Date | City |
|---|---|---|---|
| 001 | 4555 | 2020-02-03 | Lucknow |
| 002 | 7555 | 2020-07-23 | Kanpur |
| 003 | 6555 | 2020-01-22 | Noida |
| 004 | 3555 | 2020-06-04 | Mumbai |
| 005 | 8555 | 2020-03-18 | Kolkata |
Table name Customers
| Customer Number | Name | Country | City |
|---|---|---|---|
| 7555 | Alok | India | Kanpur |
| 4555 | Amit | India | Lucknow |
| 1555 | Pawan | India | Delhi |
| 2555 | Sumit | India | Pune |
Example
SELECT City FROM Customers
UNION
SELECT City FROM Order
ORDER BY City;
In both the table we have a common city each city will only be recorded
once because UNION selects only clear-cut values. Use UNION ALL to include duplicate values.
The UNION naturally deletes any duplicate rows from the query result set.
If you want all instances of all matches returned, then you can use UNION ALL.;
SQL UNION ALL syntax
SELECT column_name FROM table1
UNION ALL
SELECT column_name FROM table2 ;
Table name Order
| Order Id | Customer Number | Order Date | City |
|---|---|---|---|
| 001 | 4555 | 2020-02-03 | Lucknow |
| 002 | 7555 | 2020-07-23 | Kanpur |
| 003 | 6555 | 2020-01-22 | Noida |
| 004 | 3555 | 2020-06-04 | Mumbai |
| 005 | 8555 | 2020-03-18 | Kolkata |
Table name Customers
| Customer Number | Name | Country | City |
|---|---|---|---|
| 7555 | Alok | India | Kanpur |
| 4555 | Amit | India | Lucknow |
| 1555 | Pawan | India | Delhi |
| 2555 | Sumit | India | Pune |
Example
SELECT City FROM Order
UNION ALL
SELECT City FROM Customers
ORDER BY City;
SQL UNION ALL Instance
Example
Table Name Customers
| Customer Id | Name | Address | City | Postal code | Country |
|---|---|---|---|---|---|
|
001 |
Ankit | New Ashok Nagar H.NO 67/6 | Lucknow | 82878 | India |
|
002 |
Vinay | B clash society L810 | Pune | NA | India |
|
003 |
Raju | Mathur Road near ICIC bank H.no7/290 | Delhi | 29867 | India |
|
004 |
Karan | H.no 456 near kv school | Noida | 98689 | India |
|
005 |
Sumit | Near SPGI hospital house67 | Goa | NA | India |
Table Name Order
| Order Id | Customer Id | Employee Id | Order Date | Postal code | shipped Id |
|---|---|---|---|---|---|
| 1029 | 008 | 628790 | 2020-09-04 | NA | 11900 |
| 1022 | 009 | 272989 | 2020-07-04 | 48494 | 12900 |
| 1078 | 005 | 282896 | 2020-03-04 | NA | 13900 |
| 1078 | 004 | 278626 | 2020-11-04 | 49490 | 14900 |
SELECT Postalcode FROM Customers
UNION ALL
SELECT Postalcode FROM order
ORDER BY customer Id
SQL UNION ALL with the WHERE
SELECT Postalcode, Name FROM customers
WHERE Postalcode='00'
UNION ALL
SELECT Postalcode, Customer Id FROM customers
WHERE Postalcode='00'
ORDER BY OrderDate ;