SQL UNION Keyword
The SQL UNION keyword is used to combine the result-set of two or more SQL SELECT statements. While using UNION keyword, 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 keyword is given below:
SELECT column1, column2, ... FROM table1 UNION SELECT column1, column2, ... FROM table2
Note: UNION keyword only takes distinct values. To combine duplicate values, UNION ALL keyword can be used.
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 |
-
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 -
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
❮ SQL Keywords