SQL UNION

The UNION operator in SQL is used to combine the result sets of two or more SELECT statements. Each SELECT statement within the UNION must have the same number of columns in the result sets with similar data types, and the columns must also be in the same order.

Key Points:

  • The UNION operator selects only distinct values by default.
  • If you want to include duplicate values, use UNION ALL.
  • The column names in the result set are usually taken from the first SELECT statement.

Syntax:

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

Example:

Consider two tables, employees and contractors, with the following data:

Table: employees

id name department
1 Alice HR
2 Bob IT
3 Charlie Sales

Table: contractors

id name department
4 David IT
5 Eve Marketing
6 Frank Sales

To combine the names from both tables into a single result set, we can use the UNION operator:

SELECT name, department
FROM employees
UNION
SELECT name, department
FROM contractors;

Output:

name department
Alice HR
Bob IT
Charlie Sales
David IT
Eve Marketing
Frank Sales

Using UNION ALL:

If we use UNION ALL instead, all rows including duplicates will be included:

SELECT name, department
FROM employees
UNION ALL
SELECT name, department
FROM contractors;

Output with UNION ALL:

name department
Alice HR
Bob IT
Charlie Sales
David IT
Eve Marketing
Frank Sales

Since there were no duplicates in our example data, the output remains the same. However, if there were any duplicate rows between employees and contractors, UNION ALL would include them all in the final result set, whereas UNION would filter them out.

Practical Example with Duplicate Values:

Updated Table: contractors

id name department
4 David IT
5 Eve Marketing
6 Charlie Sales

Using UNION:

SELECT name, department
FROM employees
UNION
SELECT name, department
FROM contractors;

Output:

name department
Alice HR
Bob IT
Charlie Sales
David IT
Eve Marketing

Using UNION ALL:

SELECT name, department
FROM employees
UNION ALL
SELECT name, department
FROM contractors;

Output with UNION ALL:

name department
Alice HR
Bob IT
Charlie Sales
David IT
Eve Marketing
Charlie Sales

As you can see, UNION ALL includes the duplicate entry of "Charlie" in the final result set.

Summary:

  • Use UNION to combine result sets and automatically remove duplicates.
  • Use UNION ALL to combine result sets without removing duplicates.


twitter-white sharing button
keyboard_arrow_leftPrevious
Nextkeyboard_arrow_right

Online Learner

At Online Learner, we're on a mission to ignite a passion for learning and empower individuals to reach their full potential. Founded by a team of dedicated educators and industry experts, our platform is designed to provide accessible and engaging educational resources for learners of all ages and backgrounds.

twitter-white sharing button

Disclaimer About Us

Copyright 2023-2024 © All rights reserved.

logo
Frontend
Backend