PostgreSQL - UNION operator - GeeksforGeeks

PostgreSQL - UNION operator

Last Updated : 4 Nov, 2026

The UNION operator in PostgreSQL is used to combine the results of two or more SELECT statements into a single result set. It automatically removes duplicate rows from the combined result.

  • Each SELECT statement must return the same number of columns.
  • Corresponding columns must have compatible data types.

Syntax

SELECT column_1, column_2, ...
FROM table_1

UNION

SELECT column_1, column_2, ...
FROM table_2;

Where:

  • table1 and table_2: The tables whose results are combined.
  • column_1, column_2, ...: The columns to retrieve. Each SELECT statement must have the same number of columns with compatible data types.
  • UNION: Combines the result sets and removes duplicate rows.

Examples

Firstly, create two tables and insert some records into them.

CREATE TABLE section_a (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50),
    course VARCHAR(50)
);

INSERT INTO section_a (student_id, student_name, course)
VALUES
(101, 'John', 'Computer Science'),
(102, 'Emma', 'Mathematics'),
(103, 'Michael', 'Physics');

CREATE TABLE section_b (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50),
    course VARCHAR(50)
);

INSERT INTO section_b (student_id, student_name, course)
VALUES
(103, 'Michael', 'Physics'),
(104, 'Olivia', 'Chemistry'),
(105, 'William', 'Biology');

Output:

section_a table:

Screenshot-2026-10-01-170121

section_b table:

Screenshot-2026-10-01-170130

Example 1: Combine Results Using UNION

Query:

SELECT student_id, student_name, course
FROM section_a

UNION

SELECT student_id, student_name, course
FROM section_b;

Output:

Screenshot-2026-10-01-170159

Example 2: Use UNION with ORDER BY

Query:

SELECT student_id, student_name, course
FROM section_a

UNION

SELECT student_id, student_name, course
FROM section_b

ORDER BY student_name;

Output:

Screenshot-2026-10-01-170216

Example 3: UNION with WHERE Clause

Query:

SELECT student_name, course
FROM section_a
WHERE course <> 'Physics'

UNION

SELECT student_name, course
FROM section_b
WHERE course <> 'Physics';

Output:

Screenshot-2026-10-01-170231

Troubleshooting UNION Operator Issues

  • Different number of columns: Ensure that each SELECT statement returns the same number of columns.
  • Incompatible data types: Corresponding columns in each SELECT statement must have compatible data types.
  • Unexpected duplicate removal: UNION removes duplicate rows automatically. Use UNION ALL if you want to keep duplicates.
  • ORDER BY error: Place the ORDER BY clause only after the final SELECT statement in the UNION query.
Comment

Explore