PostgreSQL SELF JOIN, CROSS JOIN, UNION and UNION ALL: Practical SQL Guide

These concepts are useful for SQL Developers, Data Analysts, and technical interviews.

What You Will Learn

What is a SELF JOIN?
What is a CROSS JOIN?
Difference between UNION and UNION ALL
Practical SQL examples
Common mistakes and interview questions

1. What Is a SELF JOIN?

A SELF JOIN joins a table with itself. It is useful when records in the same table are related to each other.

For example, an employee table may contain both employees and their managers.

Create an Employee Table

CREATE TABLE company_employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    manager_id INT
);

INSERT INTO company_employees VALUES
(1, 'Rahul', NULL),
(2, 'Priya', 1),
(3, 'Arjun', 1),
(4, 'Sneha', 2),
(5, 'Kiran', 2);

Here, manager_id refers to another employee_id in the same table.

Example: Display Employees and Their Managers

SELECT
    e.employee_name AS employee,
    m.employee_name AS manager
FROM company_employees e
LEFT JOIN company_employees m
    ON e.manager_id = m.employee_id
ORDER BY e.employee_id;

Expected result:

Employee	Manager
Rahul	NULL
Priya	Rahul
Arjun	Rahul
Sneha	Priya
Kiran	Priya

Explanation: The table is used twice with different aliases: e represents employees, and m represents managers. LEFT JOIN keeps Rahul in the result even though he has no manager.

When Do We Use SELF JOIN?

Finding employees and their managers
Comparing records within the same table
Finding products in the same category
Comparing current and previous records when a relationship exists

2. What Is a CROSS JOIN?

A CROSS JOIN returns every possible combination of rows from two tables. This is also called a Cartesian product.

Example

CREATE TABLE colors (
    color_name VARCHAR(20)
);

INSERT INTO colors VALUES
('Red'),
('Blue');

CREATE TABLE sizes (
    size_name VARCHAR(20)
);

INSERT INTO sizes VALUES
('Small'),
('Medium'),
('Large');

Now combine every color with every size:

SELECT
    c.color_name,
    s.size_name
FROM colors c
CROSS JOIN sizes s
ORDER BY c.color_name, s.size_name;

Expected result:

Color	Size
Blue	Large
Blue	Medium
Blue	Small
Red	Large
Red	Medium
Red	Small

There are 2 colors and 3 sizes, so the result contains 2 × 3 = 6 rows.

Formula:

Number of result rows = rows in the first table × rows in the second table.

When Do We Use CROSS JOIN?

Generating all product and size combinations
Creating combinations for testing
Building date and category combinations for reporting

Important: A CROSS JOIN can produce a very large result if the tables contain many rows.

3. What Is UNION?

UNION combines the results of two or more SELECT queries and removes duplicate rows.

The queries must return the same number of columns, and corresponding columns must have compatible data types.

Example

SELECT 'Analytics' AS department
UNION
SELECT 'IT' AS department
UNION
SELECT 'Analytics' AS department;

Expected result:

Department
Analytics
IT

Analytics appears only once because UNION removes duplicate rows.

4. What Is UNION ALL?

UNION ALL combines the results of multiple SELECT queries and keeps duplicate rows.

Example

SELECT 'Analytics' AS department
UNION ALL
SELECT 'IT' AS department
UNION ALL
SELECT 'Analytics' AS department;

Expected result:

Department
Analytics
IT
Analytics

Analytics appears twice because UNION ALL preserves duplicates.

5. UNION vs UNION ALL

Feature	UNION	UNION ALL
Combines query results	Yes	Yes
Removes duplicate rows	Yes	No
Preserves duplicates	No	Yes
Duplicate-removal work	Required	Not required
Typical performance	May require extra work	Often faster when duplicates are acceptable

Rule of thumb: Use UNION when duplicates must be removed. Use UNION ALL when duplicates should be retained.

6. UNION Example with Two Tables

Suppose two tables contain customer names from different regions.

CREATE TABLE customers_region_a (
    customer_name VARCHAR(50)
);

INSERT INTO customers_region_a VALUES
('Rahul'),
('Priya');

CREATE TABLE customers_region_b (
    customer_name VARCHAR(50)
);

INSERT INTO customers_region_b VALUES
('Priya'),
('Arjun');

Using UNION

SELECT customer_name FROM customers_region_a
UNION
SELECT customer_name FROM customers_region_b
ORDER BY customer_name;

Result: Arjun, Priya, Rahul.

Using UNION ALL

SELECT customer_name FROM customers_region_a
UNION ALL
SELECT customer_name FROM customers_region_b
ORDER BY customer_name;

Result: Arjun, Priya, Priya, Rahul.

Priya appears twice because she exists in both tables.

7. Important Rules for UNION and UNION ALL

Each SELECT must return the same number of columns.
Corresponding columns must have compatible data types.
Column names in the final output generally come from the first SELECT.
Use ORDER BY at the end to sort the combined result.
UNION removes duplicate complete rows, not just duplicate values in one selected column.

8. Common Mistakes to Avoid

Forgetting to use different aliases in a SELF JOIN.
Using INNER JOIN when employees without managers must also appear.
Using CROSS JOIN without realizing how many combinations it produces.
Expecting UNION ALL to remove duplicates.
Adding ORDER BY between individual UNION queries instead of at the end.
Selecting incompatible column types in UNION queries.

9. Practice Questions

Try these questions before checking the answers.

Display every employee with their manager's name.
Find all possible combinations of colors and sizes.
Combine two lists of department names and remove duplicates.
Combine two lists of customer names while preserving duplicates.
Explain why UNION ALL may be faster than UNION.
What happens if the two SELECT statements in a UNION return different numbers of columns?

Practice Answers

1. SELF JOIN

SELECT
    e.employee_name AS employee,
    m.employee_name AS manager
FROM company_employees e
LEFT JOIN company_employees m
    ON e.manager_id = m.employee_id;

2. CROSS JOIN

SELECT c.color_name, s.size_name
FROM colors c
CROSS JOIN sizes s;

3. Combine and remove duplicates

SELECT customer_name FROM customers_region_a
UNION
SELECT customer_name FROM customers_region_b;

4. Combine and preserve duplicates

SELECT customer_name FROM customers_region_a
UNION ALL
SELECT customer_name FROM customers_region_b;

5. Why can UNION ALL be faster?

It does not need to remove duplicate rows, so it can avoid the extra processing required for duplicate elimination.

6. Different numbers of columns

PostgreSQL returns an error because each SELECT in a UNION operation must return the same number of columns.

10. PostgreSQL Interview Questions

Q1. What is a SELF JOIN?

A SELF JOIN joins a table with itself using aliases to compare or relate rows within that table.

Q2. What is a CROSS JOIN?

A CROSS JOIN returns every possible combination of rows from two tables.

Q3. What is the difference between UNION and UNION ALL?

UNION removes duplicate complete rows, whereas UNION ALL retains duplicates.

Q4. Can UNION combine columns with different data types?

Corresponding columns must have compatible types. PostgreSQL may perform type conversion when a compatible common type exists.

Q5. Can SELF JOIN use LEFT JOIN?

Yes. LEFT JOIN is useful when you want to retain employees who do not have a manager.

Conclusion

SELF JOIN, CROSS JOIN, UNION, and UNION ALL are useful PostgreSQL techniques for relating records and combining datasets. Understanding their differences will help you write better queries for real-world reporting and SQL interviews.

Practice these examples in PostgreSQL before moving to the next topic.

Next tutorial: PostgreSQL Subqueries and Common Table Expressions (CTEs) with practical examples.

Keep learning with Tech Courses & Job Updates!

Search This Blog

Tech Courses & Job Updates