SQL UNION

UNION combines the results of two or more SELECT statements.

SELECT name FROM students
UNION
SELECT name FROM teachers;

The results from both queries are displayed as one result set.

The basic syntax of UNION is:

SELECT column1, column2
FROM table1

UNION

SELECT column1, column2
FROM table2;

Both SELECT statements must have compatible column structures.

UNION combines rows from multiple SELECT results.

SELECT name
FROM students

UNION

SELECT name
FROM teachers;

The result contains names from both tables.

By default, UNION removes duplicate rows.

SELECT city FROM students
UNION
SELECT city FROM teachers;

If the same city appears in both tables, it normally appears only once in the result.

UNION ALL combines results and keeps duplicate rows.

SELECT city FROM students
UNION ALL
SELECT city FROM teachers;

Duplicate cities are included in the final result.

Each SELECT statement in a UNION should return the same number of columns.

SELECT name, city
FROM students

UNION

SELECT name, city
FROM teachers;

Both queries return two columns.

The corresponding columns should contain compatible data types.

SELECT name, age
FROM students

UNION

SELECT name, age
FROM teachers;

Here, the first columns are names and the second columns are ages.

The tables can have different names as long as the SELECT results are compatible.

SELECT name, email
FROM students

UNION

SELECT employee_name, email
FROM employees;

The column names do not have to be identical.

The column names in the final result are generally taken from the first SELECT statement.

SELECT name AS person_name
FROM students

UNION

SELECT employee_name
FROM employees;

The result uses the column name from the first query.

Each SELECT statement can have its own WHERE condition.

SELECT name
FROM students
WHERE status = 'Active'

UNION

SELECT name
FROM teachers
WHERE status = 'Active';

This combines active students and active teachers.

An ORDER BY clause can be used to sort the final UNION result.

SELECT name
FROM students

UNION

SELECT name
FROM teachers

ORDER BY name;

The combined result is sorted by name.

LIMIT can be used to restrict the final combined result.

SELECT name
FROM students

UNION

SELECT name
FROM teachers

LIMIT 10;

This returns up to 10 rows from the combined result.

UNION can combine more than two SELECT statements.

SELECT name FROM students

UNION

SELECT name FROM teachers

UNION

SELECT name FROM employees;

All three result sets are combined into one result.

You can continue adding UNION operators when multiple sources need to be combined.

SELECT email FROM students
UNION
SELECT email FROM teachers
UNION
SELECT email FROM employees
UNION
SELECT email FROM customers;

This creates one combined list of email addresses.

Expressions can also be used in UNION queries.

SELECT name, marks + 5 AS score
FROM students

UNION

SELECT name, salary / 100 AS score
FROM employees;

The corresponding expressions should produce compatible result types.

Aliases can make UNION results easier to understand.

SELECT name AS person_name, 'Student' AS person_type
FROM students

UNION

SELECT name AS person_name, 'Teacher' AS person_type
FROM teachers;

This creates a combined list with a type column.

You can use NULL when one source does not have a corresponding value.

SELECT name, email
FROM students

UNION

SELECT name, NULL AS email
FROM teachers;

This allows the two SELECT statements to have the same number of columns.

UNION can combine people from different tables.

SELECT name, 'Student' AS type
FROM students

UNION

SELECT name, 'Employee' AS type
FROM employees;

This creates one list containing both students and employees.

The column names can be different in the source tables.

SELECT name
FROM students

UNION

SELECT employee_name
FROM employees;

The first SELECT determines the displayed column name.

UNION already removes duplicate rows by default.

SELECT city
FROM students

UNION

SELECT city
FROM employees;

Therefore, using DISTINCT is normally unnecessary with UNION.

UNION ALL is useful when every row from every query must be retained.

SELECT name
FROM students

UNION ALL

SELECT name
FROM students_archive;

If the same student exists in both tables, both rows remain in the result.

UNION combines rows from multiple SELECT results, while JOIN combines related columns from tables.

-- UNION
SELECT name FROM students
UNION
SELECT name FROM teachers;

-- JOIN
SELECT s.name, c.course_name
FROM students s
INNER JOIN courses c
ON s.course_id = c.id;

Use UNION when you want to stack compatible result sets.

• Different number of columns in the SELECT statements.
• Incompatible data types.
• Incorrect column order.
• Using ORDER BY incorrectly in individual SELECT statements.
• Expecting UNION to keep duplicate rows.

Use UNION ALL when duplicate rows are required.

UNION can combine aggregate results from different queries.

SELECT 'Students' AS category, COUNT(*) AS total
FROM students

UNION

SELECT 'Teachers' AS category, COUNT(*) AS total
FROM teachers;

This produces a simple summary of students and teachers.

A SELECT statement using a subquery can also participate in a UNION.

SELECT name
FROM students
WHERE marks > (
    SELECT AVG(marks)
    FROM students
)

UNION

SELECT name
FROM students
WHERE marks < (
    SELECT AVG(marks)
    FROM students
);

This combines students above and below the average.

Suppose students and employees both have contact information.

SELECT name, mobile
FROM students

UNION ALL

SELECT name, mobile
FROM employees;

This creates one combined contact list.

You can create a unique list of cities from different tables.

SELECT city
FROM students

UNION

SELECT city
FROM employees

ORDER BY city;

The result contains unique cities from both tables, sorted alphabetically.

UNION can create a combined people report.

SELECT name, 'Student' AS role
FROM students

UNION ALL

SELECT name, 'Teacher' AS role
FROM teachers

ORDER BY name;

This report displays both students and teachers with their respective roles.

Consider the following two tables:

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(100)
);

CREATE TABLE teachers (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(100)
);

Now combine the names and cities from both tables:

SELECT name, city
FROM students

UNION

SELECT name, city
FROM teachers

ORDER BY name;

To keep duplicate records, use:

SELECT name, city
FROM students

UNION ALL

SELECT name, city
FROM teachers

ORDER BY name;

This demonstrates the basic difference between UNION and UNION ALL.