PostgreSQL - UNION Operator

PostgreSQL - UNION and UNION ALL

In PostgreSQL, the UNION operator combines the result sets of two or more SELECT statements into a single result set. It removes duplicate rows from the combined results, ensuring every row is unique.

To use UNION, all SELECT statements must have the same number of columns, and the corresponding columns must have compatible data types. The column names are determined by the first SELECT statement.

Syntax of UNION

SELECT column1, column2, ... FROM table1 [WHERE condition1]
UNION
SELECT column1, column2, ... FROM table2 [WHERE condition2];

Practical Example: Combining Datasets

Imagine a company stores its engineering and sales employees in separate tables. We want to create a unified contact list.

First, let’s create and populate our sample tables:

-- Table for engineering employees
CREATE TABLE engineering_team (
    id SERIAL PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE
);

-- Table for sales employees
CREATE TABLE sales_team (
    id SERIAL PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE
);

-- Insert data
INSERT INTO engineering_team (full_name, email) VALUES
('Alice Johnson', 'alice.j@example.com'),
('Bob Williams', 'bob.w@example.com'),
('Charlie Brown', 'charlie.b@example.com');

INSERT INTO sales_team (full_name, email) VALUES
('David Smith', 'david.s@example.com'),
('Charlie Brown', 'charlie.b@example.com'),
('Eve Davis', 'eve.d@example.com');

Notice that ‘Charlie Brown’ exists in both tables. Now, let’s use UNION to get a unique list of all employees:

SELECT full_name, email FROM engineering_team
UNION
SELECT full_name, email FROM sales_team;

This query produces the following result, with ‘Charlie Brown’ appearing only once:

full_name	email
Alice Johnson	alice.j@example.com
Bob Williams	bob.w@example.com
Charlie Brown	charlie.b@example.com
David Smith	david.s@example.com
Eve Davis	eve.d@example.com

The UNION ALL Operator

The UNION ALL operator also combines result sets, but it includes all rows, including duplicates. It is faster than UNION because it doesn’t need to perform the extra step of identifying and removing duplicates.

Syntax of UNION ALL

SELECT column1, column2, ... FROM table1 [WHERE condition1]
UNION ALL
SELECT column1, column2, ... FROM table2 [WHERE condition2];

Example of UNION ALL

Using the same tables, let’s see the result with UNION ALL:

SELECT full_name, email FROM engineering_team
UNION ALL
SELECT full_name, email FROM sales_team;

This query includes ‘Charlie Brown’ twice:

full_name	email
Alice Johnson	alice.j@example.com
Bob Williams	bob.w@example.com
Charlie Brown	charlie.b@example.com
David Smith	david.s@example.com
Charlie Brown	charlie.b@example.com
Eve Davis	eve.d@example.com

Sorting Combined Results with ORDER BY

To sort the final result set of a UNION or UNION ALL query, place the ORDER BY clause at the very end of the entire statement. It will be applied to the combined results.

SELECT full_name, email FROM engineering_team
UNION
SELECT full_name, email FROM sales_team
ORDER BY full_name ASC;

This produces a single, sorted list of unique employees:

full_name	email
Alice Johnson	alice.j@example.com
Bob Williams	bob.w@example.com
Charlie Brown	charlie.b@example.com
David Smith	david.s@example.com
Eve Davis	eve.d@example.com

Best Practices and Common Pitfalls

• Performance: Use UNION ALL instead of UNION if you are certain that there are no duplicates or if duplicates are acceptable. UNION ALL avoids the overhead of sorting and removing duplicates, making it significantly faster.
• Column Mismatch: A common error is having a different number of columns or incompatible data types in the SELECT statements. Ensure they match perfectly.
• Data Types: If corresponding columns have different but compatible data types (e.g., INT and BIGINT), PostgreSQL will cast them to a common supertype (e.g., BIGINT).
• Column Aliases: It’s good practice to use column aliases in the first SELECT statement for clarity, as these names will be used for the final result set.