Imagine a university has two separate student lists:
You want to combine both lists into one result.
In PostgreSQL, we can use UNION and UNION ALL to combine the results of multiple SELECT statements.
Both operators combine records vertically (one result below another).
The UNION operator combines the results of two or more SELECT statements and removes duplicate rows.
Syntax:
SELECT column1, column2
FROM table1
UNION
SELECT column1, column2
FROM table2;
Important: UNION removes duplicate rows from the combined result.
The UNION ALL operator also combines the results of two or more SELECT statements, but it keeps duplicate rows.
Syntax:
SELECT column1, column2
FROM table1
UNION ALL
SELECT column1, column2
FROM table2;
Important: UNION ALL does not remove duplicate rows.
Let’s create two tables for university students.
Table 1: Current Students
CREATE TABLE current_students (
student_id INT,
student_name VARCHAR(50),
city VARCHAR(50)
);
Insert records:
INSERT INTO current_students
VALUES
(1, 'Ali', 'Multan'),
(2, 'Sara', 'Lahore'),
(3, 'Ahmed', 'DG Khan');
Table 2: Former Students
CREATE TABLE former_students (
student_id INT,
student_name VARCHAR(50),
city VARCHAR(50)
);
Insert records:
INSERT INTO former_students
VALUES
(2, 'Sara', 'Lahore'),
(4, 'Usman', 'Multan'),
(5, 'Ayesha', 'Karachi');
Let’s combine both student tables using UNION.
SELECT student_id, student_name, city
FROM current_students
UNION
SELECT student_id, student_name, city
FROM former_students;
Expected output:
| student_id | student_name | city |
|---|---|---|
| 1 | Ali | Multan |
| 2 | Sara | Lahore |
| 3 | Ahmed | DG Khan |
| 4 | Usman | Multan |
| 5 | Ayesha | Karachi |
Explanation:
Now combine the same tables using UNION ALL.
SELECT student_id, student_name, city
FROM current_students
UNION ALL
SELECT student_id, student_name, city
FROM former_students;
Expected output:
| student_id | student_name | city |
|---|---|---|
| 1 | Ali | Multan |
| 2 | Sara | Lahore |
| 3 | Ahmed | DG Khan |
| 2 | Sara | Lahore |
| 4 | Usman | Multan |
| 5 | Ayesha | Karachi |
Explanation:
| UNION | UNION ALL |
|---|---|
| Combines multiple SELECT results | Combines multiple SELECT results |
| Removes duplicate rows | Keeps duplicate rows |
| Returns unique rows | Returns all rows |
| Usually requires extra processing | Usually faster because it does not remove duplicates |
Visual Understanding
CURRENT STUDENTS (3 ROWS)
Ali
Sara
Ahmed
FORMER STUDENTS (3 ROWS)
Sara
Usman
Ayesha
UNION
Unique rows
UNION ALL
All rows, including duplicate
When combining SELECT statements, follow these rules:
Rule 1: Same number of columns
Both SELECT statements must return the same number of columns.
Correct:
SELECT student_id, student_name
FROM current_students
UNION
SELECT student_id, student_name
FROM former_students;
Incorrect:
SELECT student_id, student_name
FROM current_students
UNION
SELECT student_id
FROM former_students;
Rule 2: Compatible data types
Corresponding columns must have compatible data types.
For example, an integer column should be combined with another integer-compatible column.
Rule 3: Same column order
The columns must appear in the same logical order.
SELECT student_id, student_name
FROM current_students
UNION
SELECT student_id, student_name
FROM former_students;
Rule 4: Column names come from the first SELECT
The resulting column names are based on the first SELECT statement.
We can sort the combined result using ORDER BY.
For example, display students alphabetically by name.
SELECT student_id, student_name, city
FROM current_students
UNION
SELECT student_id, student_name, city
FROM former_students
ORDER BY student_name;
Important: Write ORDER BY after the final SELECT statement. It sorts the combined result, not each individual table separately.
Students often confuse UNION with JOIN.
Both combine data, but they work differently.
UNION: Combines rows vertically
SELECT student_name
FROM current_students
UNION
SELECT student_name
FROM former_students;
The results are placed one below another.
JOIN: Combines columns horizontally
For example, suppose we have two tables:
students
| student_id | student_name |
|---|---|
| 1 | Ali |
| 2 | Sara |
departments
| student_id | department |
|---|---|
| 1 | Computer Science |
| 2 | Mathematics |
Query:
SELECT
students.student_name,
departments.department
FROM students
JOIN departments
ON students.student_id = departments.student_id;
Result:
| student_name | department |
|---|---|
| Ali | Computer Science |
| Sara | Mathematics |
Remember:
Mistake 1: Different number of columns
Both SELECT statements must return the same number of columns.
Mistake 2: Incompatible data types
Corresponding columns must have compatible data types.
Mistake 3: Expecting UNION to remove duplicates based on one column only
UNION removes duplicate complete rows based on all selected columns.
For example:
SELECT student_id, student_name
FROM current_students
UNION
SELECT student_id, student_name
FROM former_students;
If two rows have the same student name but different student IDs, both rows remain in the result.
Use the current_students and former_students tables created in this lesson.
new_students and insert three records. Combine all three tables using UNION.| SQL Operator | Purpose |
|---|---|
| UNION | Combines results and removes duplicate rows |
| UNION ALL | Combines results and keeps duplicate rows |
| JOIN | Combines related data from tables horizontally |
| ORDER BY | Sorts the combined result |
Key point: Use UNION when you need unique rows. Use UNION ALL when you need all rows, including duplicates.