Introduction to SQL Union

Introduction

SQL is an important tool that every data scientist and data analyst should know. Its UNION statement allows you to combine the results of two or more SQL SELECT statements. The SELECT command may be on the same table or a different table. We will delve into the basics of UNION and explore different ways to use it. Furthermore, we will use some sample tables to experiment with UNION commands.

Overview: 

• Learn about the SQL UNION statement.
• Gain an understanding of the basic syntax and conditions for SQL UNION.
• Explore practical examples and sample data.
• Discover how to sort and filter combined results.
• Understand best practices for using SQL UNION.
• Compare SQL UNION with SQL JOIN.

What is SQL UNION?

The SQL UNION combines two or more SELECT statements, i.e., the result sets we get from SELECT statements. We stack them on one other to get the UNION operation done. There are some conditions to execute a UNION statement. Each SELECT statement within the UNION must have the same number of columns in the result sets with similar data types. The SQL UNION operator removes duplicate rows from the result set by default.

Basic Syntax

Below is the basic syntax for the SQL UNION of two result sets from two SELECT statements.

SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;

Creation of Sample data

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO Employees (employee_id, name, department, salary)
VALUES 
(1, 'Alice', 'HR', 60000.00),
(2, 'Bob', 'IT', 75000.00),
(3, 'Charlie', 'Finance', 70000.00),
(4, 'Dana', 'IT', 80000.00);

This will create our first table of Employees and insert sample data into our table.

CREATE TABLE Contractors (
    contractor_id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    hourly_rate DECIMAL(10, 2)
);

INSERT INTO Contractors (contractor_id, name, department, hourly_rate)
VALUES 
(1, 'David', 'IT', 50.00),
(2, 'Eve', 'Finance', 45.00),
(3, 'Frank', 'HR', 40.00),
(4, 'Grace', 'IT', 55.00);

This will create our second table, Contractors, and insert sample data into our table.

Basic Usage of SQL UNION

Combining data from Employees and Contractors

SELECT *
FROM Employees
UNION
SELECT *
FROM Contractors;

The column name comes from the Employee table. Note that SQL UNION takes the column names from the first SELECT statement.

Combining data and Sorting the results

SELECT name, department
FROM Employees
UNION
SELECT name, department
FROM Contractors
ORDER BY salary;

In our final result set, we have sorted the records by salary. Hence, you can use ORDER BY to sort records in the final result set. Note that you can only order based on the selected columns, not those not selected.

Using UNION with WHERE clause

SELECT name, department
FROM Employees
WHERE department = 'IT'
UNION
SELECT name, department
FROM Contractors
WHERE department = 'IT';

In the above image, only the selected columns and records that satisfy the WHERE clause are present. The WHERE clause will help you filter records as your condition in SQL UNION.

Best Practises and Considerations

• Column Order: Ensure that the columns in each SELECT statement are in the same order and have compatible data types.
• Debugging: When troubleshooting, run each SELECT statement independently to verify that it returns the expected results before combining it with SQL UNION.

Comparison between SQL JOIN and SQL UNION

JOIN and UNION are both used to combine data from multiple tables. They both have different purposes.

Conclusion

SQL UNION gives you diverse solutions for combining results from SELECT statements from the same or different tables. With a good understanding of UNION statements and their behavior, we should be able to effectively manage and manipulate data. UNION operators simplify the process and make it easy to combine results.

Also Read: Most Important SQL Queries for Beginners

Frequently Asked Questions

Badrinarayan M

Data science Trainee at Analytics Vidhya, specializing in ML, DL and Gen AI. Dedicated to sharing insights through articles on these subjects. Eager to learn and contribute to the field's advancements. Passionate about leveraging data to solve complex problems and drive innovation.