[Basic Grammar] SQL UNION Operator

The UNION operator is ues to combine the result-set of two or more SELECT statements. In other words, it is basically combining two or more tables. 

 

There are three rules for UNION operator: 

• Every SELECT statement within UNION must have the same number of columns 
• They must have have similar data types 
• The columns in every SELECT statement must also be in the same order

 

• UNION Syntax 

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

Example

Write an SQL query to combine the names of all employees from the "Employees" table and all customers from the "Customers" table into a single list of names.

SELECT EmployeeName AS Name FROM Employees 
UNION 
SELECT CustomerName AS Name FROM Customers;

 

• UNION ALL Syntax 

Under the circumstance that you want to allow duplicate values, use UNION ALL.

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

Example 

The following SQL statement returns the cities (duplicate values also) from both the "Customers" and the "Suppliers" table:

SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

 

• SQL UNION with WHERE 

Example 

Write an SQL query to combine the names of employees and customers into a single list, but filter the results to include only names starting with the letter "A." Use the "Employees" and "Customers" tables for this task.

SELECT EmployeeName As Name FROM Employees WHERE Employees LIKE 'A%'
UNION 
SELECT CustomerName AS NAME FROM Customers WHERE CustomerName LIKe 'A%'
;

 

Exercises 

1. Write an SQL query to combine the names of products and suppliers into a single list, but filter the results to include only products with a price greater than $100. Use the "Products" and "Suppliers" tables for this task.

SELECT ProductsName AS Name FROM Products WHERE Price > $100 
UNION 
SELECT SupplierName AS Name FROM Suppliers;

 

2. Create an SQL query to combine the names of students and teachers into a single list, but filter the results to include only students whose names contain the letter "S." Use the "Students" and "Teachers" tables for this task.

SELECT StudentName AS Name FROM Students WHERE StudentName LIKE '%S%'
UNION 
SELECT TeacherName AS Name FROM Teachers;