Understanding Joins in PostgreSQL: INNER, LEFT, RIGHT, FULL

This section will be using the Sample Database ‘carrentaldb’ article as references for tables to be used. Joins in PostgreSQL are used to combine rows from two or more tables based on a related column. They help retrieve meaningful data from relational databases by linking tables together.

---

1. INNER JOIN : Fetch Matching Records in Both Tables

An INNER JOIN returns only the rows where there is a match in both tables. If there is no match, the row is excluded from the result.

Example : Retrieve a list of customers along with their reservation details.

SELECT 
    customers.first_name, 
    customers.last_name, 
    reservations.reservation_id, 
    reservations.pickup_date, 
    reservations.return_date, 
    reservations.total_rental_price
FROM customers
INNER JOIN reservations ON customers.customer_id = reservations.customer_id;

Output will be as shown below :

  first_name | last_name | reservation_id | pickup_date | return_date | total_rental_price 
------------+-----------+----------------+-------------+-------------+--------------------
 John       | Doe       |              1 | 2025-02-01  | 2025-02-05  |             180.00
 Jane       | Smith     |              2 | 2025-02-10  | 2025-02-12  |              80.00
 Alice      | Johnson   |              3 | 2025-03-15  | 2025-03-18  |             126.00
 Robert     | Brown     |              4 | 2025-04-05  | 2025-04-10  |             190.00
 Emily      | Davis     |              5 | 2025-05-10  | 2025-05-14  |             200.00
 Michael    | Wilson    |              6 | 2025-06-01  | 2025-06-03  |             190.00
 Jessica    | Taylor    |              7 | 2025-07-20  | 2025-07-22  |              78.00
 David      | Anderson  |              8 | 2025-08-15  | 2025-08-17  |             240.00
 Sarah      | Martinez  |              9 | 2025-09-25  | 2025-09-30  |             500.00
 James      | Harris    |             10 | 2025-10-10  | 2025-10-15  |             175.00
(10 rows)

2. LEFT JOIN : Fetch All Records from the Left Table, and Matches from the Right Table

A LEFT JOIN returns all rows from the left table and only matching rows from the right table. If there is no match, NULL values appear for columns from the right table. Retrieve all cars and their associated reservations, if any.

Example : Get All Cars with Their Reservations (Including Cars Not Reserved)

SELECT 
    cars.car_id, 
    cars.brand, 
    cars.model, 
    reservations.reservation_id, 
    reservations.pickup_date, 
    reservations.return_date
FROM cars
LEFT JOIN reservations ON cars.car_id = reservations.car_id;

Output will be as shown below :

 car_id |   brand   |  model  | reservation_id | pickup_date | return_date 
--------+-----------+---------+----------------+-------------+-------------
      2 | Honda     | Civic   |              1 | 2025-02-01  | 2025-02-05
      1 | Toyota    | Corolla |              2 | 2025-02-10  | 2025-02-12
      5 | Nissan    | Altima  |              3 | 2025-03-15  | 2025-03-18
      3 | Ford      | Focus   |              4 | 2025-04-05  | 2025-04-10
      4 | Chevrolet | Malibu  |              5 | 2025-05-10  | 2025-05-14
      7 | Mercedes  | C-Class |              6 | 2025-06-01  | 2025-06-03
      8 | Hyundai   | Sonata  |              7 | 2025-07-20  | 2025-07-22
      9 | Tesla     | Model 3 |              8 | 2025-08-15  | 2025-08-17
      6 | BMW       | X5      |              9 | 2025-09-25  | 2025-09-30
     10 | Kia       | Optima  |             10 | 2025-10-10  | 2025-10-15
(10 rows)

3. RIGHT JOIN : Fetch All Records from the Right Table, and Matches from the Left Table

A RIGHT JOIN returns all rows from the right table and only matching rows from the left table. If there is no match, NULL values appear for columns from the left table.

Example : Get All Reservations with Employee Details

SELECT 
    reservations.reservation_id, 
    reservations.pickup_date, 
    reservations.return_date, 
    employees.first_name AS employee_first_name, 
    employees.last_name AS employee_last_name
FROM reservations
RIGHT JOIN employees ON reservations.employee_id = employees.employee_id;

Output will be as shown below :

 reservation_id | pickup_date | return_date | employee_first_name | employee_last_name 
----------------+-------------+-------------+---------------------+--------------------
              1 | 2025-02-01  | 2025-02-05  | Michael             | Brown
              2 | 2025-02-10  | 2025-02-12  | Sara                | Davis
              3 | 2025-03-15  | 2025-03-18  | Daniel              | Miller
              4 | 2025-04-05  | 2025-04-10  | Sophia              | Lopez
              5 | 2025-05-10  | 2025-05-14  | William             | Gonzalez
              6 | 2025-06-01  | 2025-06-03  | Olivia              | Hernandez
              7 | 2025-07-20  | 2025-07-22  | Ethan               | Moore
              8 | 2025-08-15  | 2025-08-17  | Isabella            | Clark
              9 | 2025-09-25  | 2025-09-30  | Mason               | Rodriguez
             10 | 2025-10-10  | 2025-10-15  | Ava                 | Lee
(10 rows)

4. FULL JOIN : Fetch All Records from Both Tables

A FULL JOIN returns all rows from both tables. If there is a match, the rows are combined. If there is no match, NULL values are used.

Example : Retrieve all payments and their associated reservations, including those with no payments.

SELECT 
    payments.payment_id, 
    payments.amount_paid, 
    payments.payment_date, 
    reservations.reservation_id, 
    reservations.status
FROM payments
FULL JOIN reservations ON payments.reservation_id = reservations.reservation_id;

Output will be as shown below :

 payment_id | amount_paid | payment_date | reservation_id |  status   
------------+-------------+--------------+----------------+-----------
          1 |      180.00 | 2025-02-01   |              1 | Confirmed
          2 |       80.00 | 2025-02-10   |              2 | Pending
          3 |      126.00 | 2025-03-15   |              3 | Completed
          4 |        0.00 | 2025-04-05   |              4 | Canceled
          5 |      200.00 | 2025-05-10   |              5 | Confirmed
          6 |      190.00 | 2025-06-01   |              6 | Completed
          7 |       78.00 | 2025-07-20   |              7 | Pending
          8 |      240.00 | 2025-08-15   |              8 | Confirmed
          9 |      500.00 | 2025-09-25   |              9 | Completed
         10 |        0.00 | 2025-10-10   |             10 | Canceled
(10 rows)

Summary of PostgreSQL Joins

Join Type	Left Table Rows?	Right Table Rows?	Matched Rows?	Unmatched Rows?
INNER JOIN	✅ Only matched	✅ Only matched	✅ Yes	❌ No
LEFT JOIN	✅ All	✅ Only matched	✅ Yes	✅ Left table only
RIGHT JOIN	✅ Only matched	✅ All	✅ Yes	✅ Right table only
FULL JOIN	✅ All	✅ All	✅ Yes	✅ Both tables