Union and Union All In SQL

Union and Union All

In SQL, UNION and UNION ALL are used to combine the results of two or more SELECT statements. Both operations are used to combine rows from multiple tables or queries, but they have some key differences.

UNION:

• The UNION operator is used to combine the result sets of two or more SELECT statements while eliminating duplicate rows.
• It returns only distinct (unique) rows from the combined result sets.
• The columns in each SELECT statement must be of the same data type and in the same order.

Syntax

SELECT column1, column2, ...

FROM table1

UNION

SELECT column1, column2, ...

FROM table2;

UNION ALL:

• The UNION ALL operator, like UNION, is used to combine the result sets of two or more SELECT statements, but it does not eliminate duplicate rows.
• It includes all rows from all SELECT statements, regardless of whether they are duplicates.
• UNION ALL is generally faster than UNION because it does not need to perform the additional step of removing duplicates.

Syntax

SELECT column1, column2, ...

FROM table1

UNION ALL

SELECT column1, column2, ...

FROM table2;

Example

Certainly! Let's consider three tables: employees, contractors, and clients. We will use the following example tables:

Create and Insert Query for employees table:

Create employees table

CREATE TABLE employees (

    employee_id INT PRIMARY KEY,

    employee_name VARCHAR(50)

);

Insert data into employees table

INSERT INTO employees (employee_id, employee_name) VALUES

(1, 'John'),

(2, 'Alice'),

(3, 'Bob'),

(4, 'Mary');

Create and Insert Query for contractors table:

Create contractors table

CREATE TABLE contractors (

    contractor_id INT PRIMARY KEY,

    contractor_name VARCHAR(50)

);

Insert data into contractors table

INSERT INTO contractors (contractor_id, contractor_name) VALUES

(2, 'Alice'),

(3, 'Bob'),

(5, 'Charlie'),

(6, 'Diane');

Create and Insert Query for clients table:

Create clients table

CREATE TABLE clients (

    client_id INT PRIMARY KEY,

    client_name VARCHAR(50)

);

Insert data into clients table

INSERT INTO clients (client_id, client_name) VALUES

(1, 'XYZ Corp'),

(3, 'ABC Inc'),

(5, 'Acme Co'),

(6, 'Tech Ltd');

Now, let's see examples for both UNION and UNION ALL

Example using UNION:

SELECT employee_id, employee_name

FROM employees

UNION

SELECT contractor_id, contractor_name

FROM contractors;

Result

Explanation: The UNION operator combines the unique rows from both tables, and duplicates are removed. In this case, it returns a result set with distinct combinations of employee_id and employee_name from the employees table and contractor_id and contractor_name from the contractors table.

Example using UNION ALL:

SELECT employee_id, employee_name

FROM employees

UNION ALL

SELECT contractor_id, contractor_name

FROM contractors;

Result

Explanation: The UNION ALL operator combines all rows from both tables, including duplicates. In this case, it returns a result set with all combinations of employee_id and employee_name from the employees table and contractor_id and contractor_name from the contractors table, without removing duplicates.

Now, let's see an example using UNION and UNION ALL with these three tables.

Example using UNION:

SELECT employee_id, employee_name

FROM employees

UNION

SELECT contractor_id, contractor_name

FROM contractors

UNION

SELECT client_id, client_name

FROM clients;

Result

Example using UNION ALL:

SELECT employee_id, employee_name

FROM employees

UNION ALL

SELECT contractor_id, contractor_name

FROM contractors

UNION ALL

SELECT client_id, client_name

FROM clients;

Result