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  ;

Keywords: