Introduction to SQL Union

Badrinarayan M Last Updated : 25 Jun, 2024
4 min read

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);
Creation of Sample data | Introduction to SQL UNION

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);
Creation of Sample data | Introduction to SQL UNION

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;
Basic Usage of SQL UNION

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;
Basic Usage of SQL UNION

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';
Using UNION with WHERE clause

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.

JOINUNION
PurposeJOIN combines columns from two or more tables based on a related column between them.Combines the results of two or more SELECT statements into a single result set, stacking them vertically.
StructureMerges tables horizontally by adding columns from the second table to the columns of the first table.Merges tables vertically by adding rows from the second SELECT statement to the rows of the first SELECT statement.

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

Q1. What are the requirements for columns in UNION?

A. All SELECT statements used in the UNION must have the same number of columns, and their corresponding columns must have compatible data types.

Q2. Can I use ORDER BY with UNION?

A. Yes, you can use ORDER BY with UNION, but it should be placed after the last SELECT statement to sort the entire result set.

Q3. How does JOIN affect performance compared to UNION?

A. JOIN can be more resource-intensive, especially with large tables or multiple joins, due to the need for matching rows across tables. UNION can also be costly, particularly when removing duplicates. Use UNION ALL if duplicates are acceptable to improve performance.

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.

Responses From Readers

Clear

We use cookies essential for this site to function well. Please click to help us improve its usefulness with additional cookies. Learn about our use of cookies in our Privacy Policy & Cookies Policy.

Show details