PostgreSQL JOINs Explained: INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL OUTER JOIN

-

 PostgreSQL JOINs help you retrieve related data from two or more tables. They are essential for SQL developers, Data Analysts, and anyone working with relational databases.

For example, a company may store employee information in one table and department information in another. JOINs allow you to combine these tables and generate meaningful reports.

What You Will Learn

  • What is a JOIN in PostgreSQL?

  • INNER JOIN

  • LEFT JOIN

  • RIGHT JOIN

  • FULL OUTER JOIN

  • JOIN conditions and common mistakes

  • Practical exercises and interview questions

1. Create Sample Tables

We will use two tables: employees and departments.

Departments table

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

INSERT INTO departments VALUES
(1, 'Analytics'),
(2, 'Finance'),
(3, 'IT'),
(4, 'HR');

Employees table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    department_id INT
);

INSERT INTO employees VALUES
(101, 'Rahul', 1),
(102, 'Priya', 2),
(103, 'Arjun', 3),
(104, 'Sneha', 1),
(105, 'Kiran', NULL);

In this example, Kiran does not have a department assigned. The HR department also has no employees.

2. What Is INNER JOIN?

INNER JOIN returns only the rows that match the join condition in both tables.

Example: Display employees with their department names.

SELECT
    e.employee_id,
    e.employee_name,
    d.department_name
FROM employees e
INNER JOIN departments d
    ON e.department_id = d.department_id;

Expected result:

Employee IDEmployee NameDepartment
101RahulAnalytics
102PriyaFinance
103ArjunIT
104SnehaAnalytics

Kiran is excluded because his department_id is NULL. HR is excluded because no employee belongs to it.

When to use INNER JOIN: When you only need records with matching values in both tables.

3. What Is LEFT JOIN?

LEFT JOIN returns every row from the left table and matching rows from the right table. If there is no match, the right-table columns contain NULL.

Example: Display every employee, including employees without a department.

SELECT
    e.employee_id,
    e.employee_name,
    d.department_name
FROM employees e
LEFT JOIN departments d
    ON e.department_id = d.department_id
ORDER BY e.employee_id;

Expected result:

Employee IDEmployee NameDepartment
101RahulAnalytics
102PriyaFinance
103ArjunIT
104SnehaAnalytics
105KiranNULL

Kiran appears because LEFT JOIN preserves every row from the employees table.

When to use LEFT JOIN: When you want all records from the main table, even if related information is missing.

4. What Is RIGHT JOIN?

RIGHT JOIN returns every row from the right table and matching rows from the left table. If there is no match, the left-table columns contain NULL.

Example: Display every department, including departments without employees.

SELECT
    e.employee_name,
    d.department_name
FROM employees e
RIGHT JOIN departments d
    ON e.department_id = d.department_id
ORDER BY d.department_id, e.employee_name;

Expected result:

Employee NameDepartment
RahulAnalytics
SnehaAnalytics
PriyaFinance
ArjunIT
NULLHR

HR appears even though no employee belongs to it.

When to use RIGHT JOIN: When you need all rows from the right-side table. Many developers rewrite RIGHT JOIN as LEFT JOIN by swapping the table order.

5. What Is FULL OUTER JOIN?

FULL OUTER JOIN returns all matching and non-matching rows from both tables. Missing values from either side appear as NULL.

Example: Display all employees and all departments.

SELECT
    e.employee_name,
    d.department_name
FROM employees e
FULL OUTER JOIN departments d
    ON e.department_id = d.department_id
ORDER BY d.department_name NULLS LAST, e.employee_name;

Expected result includes:

Employee NameDepartment
RahulAnalytics
SnehaAnalytics
PriyaFinance
ArjunIT
NULLHR
KiranNULL

This query includes the employee without a department and the department without employees.

When to use FULL OUTER JOIN: When comparing two datasets and keeping both matched and unmatched records.

6. JOIN Types Compared

JOIN TypeWhat It Returns
INNER JOINMatching rows from both tables
LEFT JOINAll left-table rows and matching right-table rows
RIGHT JOINAll right-table rows and matching left-table rows
FULL OUTER JOINAll rows from both tables, matched where possible

7. Find Employees Without a Matching Department

A common SQL interview question is to find employees who do not have a matching department.

SELECT
    e.employee_id,
    e.employee_name
FROM employees e
LEFT JOIN departments d
    ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

Expected result: Kiran.

This technique is called an anti-join pattern. It is useful for identifying missing relationships.

8. Count Employees in Each Department

You can combine JOINs with aggregate functions and GROUP BY.

SELECT
    d.department_name,
    COUNT(e.employee_id) AS employee_count
FROM departments d
LEFT JOIN employees e
    ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
ORDER BY d.department_id;

Expected result:

DepartmentEmployee Count
Analytics2
Finance1
IT1
HR0

Notice that we use COUNT(e.employee_id), not COUNT(). With LEFT JOIN, COUNT() would count the unmatched HR row as one row, whereas COUNT(e.employee_id) correctly returns zero.

9. Common JOIN Mistakes

  1. Forgetting the ON condition: This can create an unintended Cartesian product.

  2. Using the wrong join type: Choose the JOIN based on whether unmatched rows must be preserved.

  3. Ignoring NULL values: Unmatched rows often produce NULL in columns from the other table.

  4. Using COUNT(*) incorrectly with LEFT JOIN: Use COUNT(non-nullable_right_table_key) when you need to count actual matching records.

  5. Duplicate results: A one-to-many relationship can return multiple rows for one record from the first table. This may be expected.

10. Practice Questions

Try writing the queries before checking your solutions.

  1. Display employee names and their department names using INNER JOIN.

  2. Display all employees using LEFT JOIN.

  3. Display all departments using RIGHT JOIN.

  4. Find employees without a matching department.

  5. Count the employees in every department, including departments with zero employees.

  6. Display all employees and departments using FULL OUTER JOIN.

Practice Answers

1. INNER JOIN

SELECT e.employee_name, d.department_name
FROM employees e
INNER JOIN departments d
    ON e.department_id = d.department_id;

2. All employees

SELECT e.employee_name, d.department_name
FROM employees e
LEFT JOIN departments d
    ON e.department_id = d.department_id;

3. All departments

SELECT e.employee_name, d.department_name
FROM employees e
RIGHT JOIN departments d
    ON e.department_id = d.department_id;

4. Employees without a matching department

SELECT e.employee_name
FROM employees e
LEFT JOIN departments d
    ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

5. Employee count by department

SELECT d.department_name, COUNT(e.employee_id) AS employee_count
FROM departments d
LEFT JOIN employees e
    ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

6. All employees and departments

SELECT e.employee_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d
    ON e.department_id = d.department_id;

11. PostgreSQL JOIN Interview Questions

Q1. What is a JOIN?

A JOIN combines related rows from two or more tables using a matching condition.

Q2. What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only matching rows. LEFT JOIN preserves every row from the left table and returns NULL for unmatched right-table values.

Q3. When would you use FULL OUTER JOIN?

When you need all records from both tables, including records without a match.

Q4. Can a table be joined with itself?

Yes. This is called a SELF JOIN. It is useful for relationships such as employees and their managers.

Q5. What happens when a JOIN condition matches multiple rows?

The query returns each matching combination, which can increase the number of result rows.

Conclusion

PostgreSQL JOINs are essential for combining data stored across related tables. Understanding INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN will help you write better queries for reporting, data analysis, and interviews.

Practice each JOIN using the sample tables before moving to more advanced SQL topics.

Next tutorial: PostgreSQL SELF JOIN, CROSS JOIN, and UNION vs UNION ALL with practical examples.

Keep learning with Tech Courses & Job Updates!

Comments

Post a Comment

Popular posts from this blog

PostgreSQL Tutorial for Beginners: Learn SQL with Practical Examples

-
Introduction PostgreSQL is a powerful, open-source relational database management system used to store, organize, and retrieve data. It is widely used in web applications, business software, reporting systems, and data analytics projects. In this tutorial, you will learn the fundamentals of PostgreSQL through practical examples. You will understand how to create tables, insert records, retrieve data, filter results, and summarize information using SQL queries. This guide is designed for beginners who want to develop practical database skills for software development, data analytics, and other technology-related careers. What You Will Learn By the end of this tutorial, you will understand: What PostgreSQL is and how it works. How to create a database table. How to insert and retrieve records. How to filter and sort data. How to summarize data using aggregate functions. How to practice SQL using a business-related example. What Is PostgreSQL? PostgreSQL is an open-source relational datab...
Read more