SQL - Unions

The UNION operator in SQL is used to combine the result sets of two or more SELECT statements into a single result set. Each SELECT statement within the UNION must have the same number of columns in the result sets with similar data types. Here’s a detailed tutorial on how to use the UNION operator effectively.

Basic Syntax

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

Important Points

1. Each SELECT statement must have the same number of columns.
2. The columns must have similar data types.
3. The columns in each SELECT statement must be in the same order.

Types of UNION

1. UNION: Combines the result sets of two queries and removes duplicate rows.
2. UNION ALL: Combines the result sets of two queries and includes all duplicates.

Examples

Assume you have two tables, employees_usa and employees_uk, both with columns first_name, last_name, and salary.

SELECT first_name, last_name, salary
FROM employees_usa
UNION
SELECT first_name, last_name, salary
FROM employees_uk;

This query combines the results from both tables and removes any duplicate rows.

To include duplicates in the result set:

SELECT first_name, last_name, salary
FROM employees_usa
UNION ALL
SELECT first_name, last_name, salary
FROM employees_uk;

This query combines the results from both tables and includes all duplicate rows.