SQL UNION

What is SQL UNION?

SQL UNION is a set operation that combines the result sets of two or more SELECT queries into a single result set. The UNION operator removes duplicate rows from the combined result, providing a distinct list of rows from all the SELECT statements. It is often used to combine data from multiple tables or queries.

When you would use it

You would use SQL UNION when you want to:

1. Combine data: Merge the results of multiple SELECT queries into one result set.

2. Remove duplicates: Ensure that the final result contains unique rows by automatically eliminating duplicate records.

3. Create a single result set: When you need to retrieve data from multiple tables or conditions and present it as a single result for further analysis or presentation.

4. Handle data from different sources: When working with data from different tables, databases, or even different systems, to create a cohesive dataset for analysis or reporting.

Syntax

The syntax for SQL UNION is as follows:

SELECT column1, column2, ...
FROM table1
WHERE condition1

UNION

SELECT column1, column2, ...
FROM table2
WHERE condition2

• column1, column2, ...: The columns you want to retrieve from the tables.
• table1, table2, ...: The names of the tables to select data from.
• condition1, condition2, ...: Optional conditions to filter the data.

Parameter values

• column1, column2, ...: Column names you want to include in the result set.
• table1, table2, ...: Table names or subqueries where you want to retrieve data.
• condition1, condition2, ...: Optional conditions to filter data.

Example query

Suppose you have two tables, "employees" and "contractors," and you want to retrieve a list of all people working in your organization. You can use SQL UNION like this:

SELECT employee_id, employee_name, 'Employee' AS job_title
FROM employees
UNION
SELECT contractor_id, contractor_name, 'Contractor' AS job_title
FROM contractors;

Example table response

Assuming the "employees" and "contractors" tables contain the following data:

employees:

| employee_id | employee_name |
| ----------- | ------------- |
| 1           | John Smith    |
| 2           | Mary Johnson  |
| 3           | Sam Brown     |

contractors:

| contractor_id | contractor_name |
| ------------- | --------------- |
| 101           | Alice White     |
| 102           | Tom Green       |
| 103           | Emma Lee        |

The query mentioned earlier would return the following result:

| employee_id | employee_name | job_title  |
| ----------- | ------------- | ---------- |
| 1           | John Smith    | Employee   |
| 2           | Mary Johnson  | Employee   |
| 3           | Sam Brown     | Employee   |
| 101         | Alice White   | Contractor |
| 102         | Tom Green     | Contractor |
| 103         | Emma Lee      | Contractor |

This result combines data from both tables and adds a "job_title" column to indicate whether each person is an employee or a contractor.

Use cases

• Combining data from multiple tables or queries into a single result set.
• Ensuring that the result set contains only distinct rows by eliminating duplicates.
• Merging data from different sources for analysis or reporting.
• Combining data from different tables, databases, or systems.

SQL languages this is available for

SQL UNION is a standard SQL feature and is available in most relational database management systems (RDBMS), including but not limited to:

• MySQL
• PostgreSQL
• Oracle Database
• SQL Server
• SQLite
• IBM Db2
• MariaDB

The syntax and behavior of UNION are consistent across these database systems.