SQL - UNION Clause
The SQL UNION clause is used to combine the result-set of two or more SQL SELECT statements. While using UNION clause, the following criteria must be satisfied:
- Number of columns in each SELECT statement should equal.
- Datatype of columns in each SELECT statement should match.
- Order of columns in each SELECT statement should match.
Syntax
The syntax for using UNION clause is given below:
SELECT column1, column2, ... FROM table1 UNION SELECT column1, column2, ... FROM table2
Note: UNION clause only takes distinct values. To combine duplicate values, UNION ALL clause can be used.
The syntax for using UNION ALL clause is given below:
SELECT column1, column2, ... FROM table1 UNION ALL SELECT column1, column2, ... FROM table2
Example:
Consider database tables called Employee and Vendor with the following records:
Table 1: Employee table
EmpID | Name | City | Age | Salary |
---|---|---|---|---|
1 | John | London | 30 | 3000 |
2 | Marry | London | 24 | 2750 |
3 | Jo | Paris | 27 | 2800 |
Table 2: Vendor table
VendorID | Name | City | Age |
---|---|---|---|
1 | Kim | London | 29 |
2 | Ramesh | New Delhi | 28 |
3 | Suresh | New Delhi | 31 |
-
Using UNION clause: To fetch distinct cities from tables Employee and Vendor, the SQL code is given below:
SELECT City FROM Employee UNION SELECT City FROM Vendors ORDER BY City;
This will produce the result as shown below:
City London New Delhi Paris -
Using UNION ALL clause: To fetch all cities from tables Employee and Vendor, the SQL code is given below:
SELECT City FROM Employee UNION ALL SELECT City FROM Vendors ORDER BY City;
This result of the above code will be:
City London London London New Delhi New Delhi Paris -
Using UNION clause with WHERE clause: To fetch distinct cities from tables Employee and Vendor where age of the person is less than 30, the SQL code is given below:
SELECT City FROM Employee WHERE Age < 30 UNION SELECT City FROM Vendors WHERE Age < 30 ORDER BY City;
This will produce the following result:
City London New Delhi Paris -
Using UNION ALL clause with WHERE clause: To fetch all cities from tables Employee and Vendor where age of the person is less than 30, the SQL code is given below:
SELECT City FROM Employee WHERE Age < 30 UNION SELECT City FROM Vendors WHERE Age < 30 ORDER BY City;
This will produce the result as shown below:
City London London New Delhi Paris