SQL UNION Operator Tutorial

Example Tables

We will use the following tables named employees and managers for our examples:


    CREATE TABLE employees (
        employee_id INT PRIMARY KEY,
        name VARCHAR(50),
        department VARCHAR(50)
    );

    CREATE TABLE managers (
        manager_id INT PRIMARY KEY,
        name VARCHAR(50),
        department VARCHAR(50)
    );

    INSERT INTO employees (employee_id, name, department) VALUES
    (1, 'Alice', 'HR'),
    (2, 'Bob', 'IT'),
    (3, 'Charlie', 'Finance');

    INSERT INTO managers (manager_id, name, department) VALUES
    (1, 'David', 'HR'),
    (2, 'Eva', 'IT'),
    (3, 'Frank', 'Finance');

Employees Table

employee_id	name	department
1	Alice	HR
2	Bob	IT
3	Charlie	Finance

Managers Table

manager_id	name	department
1	David	HR
2	Eva	IT
3	Frank	Finance

Using the SQL UNION Operator

The SQL UNION operator 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.

Example: Combining Employees and Managers

To combine the names and departments of employees and managers:


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

Result:

Query	SELECT name, department FROM employees UNION SELECT name, department FROM managers;
Result	Alice, HR Bob, IT Charlie, Finance David, HR Eva, IT Frank, Finance

Example: Combining with UNION ALL

To include duplicate rows in the result set, use UNION ALL:


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