PostgreSQL Joins Explained: Inner, Left, Right & Outer

Understanding Joins

In real-world applications, you rarely work with a single table. A restaurant, for example, has a menu table and an orders table that communicate with each other. Joins allow you to connect these tables together in a single query.

Without joins, you'd have two bad options: duplicate data everywhere (causing data integrity failures) or make multiple separate queries (error-prone and slower). Joins are how relational databases connect related data cleanly without duplication.

The Relationship Between Tables

Consider two tables: users and likes. The users table has a primary key column id. The likes table has a foreign key column user_id that references the primary key of the users table. This shows what user does what. For example, Patrick (ID 1) likes climbing and coding, while Albert doesn't like anything.

Inner Join

An inner join connects two tables on a shared condition, such as IDs being equal. It only includes rows where the condition matches in both tables. Users without likes and activities not liked by anyone are excluded.

Setting Up the Environment

Start PostgreSQL by clicking the Start PostgreSQL button. Once the environment is running, open VS Code and open the integrated terminal (three lines, Terminal, New Terminal).

Check the PostgreSQL service:

sudo systemctl status postgresql

Connect to the restaurant database:

sudo -u postgres psql restaurant

List the tables:

\dt

Look at the menu table:

SELECT * FROM menu;

Look at the orders table:

SELECT * FROM orders;

Performing an Inner Join

SELECT name AS dish_name, price, customer_name, quantity
FROM menu
INNER JOIN orders ON menu.id = orders.menu_id;

The result shows Alice with two orders, Bob with two orders, Carla with two orders, Dave, Eve, and Frank. Grace is not in the result because her order has a null value.

Left Join

A left join keeps all data from the left table, regardless of whether it has a corresponding connection to the right table. Rows without matches get null values for the right table's columns.

SELECT name AS dish_name, customer_name, price, quantity
FROM menu
LEFT JOIN orders ON menu.id = orders.menu_id;

This shows the same inner join results plus items that haven't been bought by anyone: cheesecake, Caesar salad, bruschetta, beef burger, soup of the day.

Right Join

A right join keeps all data from the right table. Items not liked by anyone appear with null values for the left table's columns.

SELECT name AS dish_name, customer_name, price, quantity
FROM menu
RIGHT JOIN orders ON menu.id = orders.menu_id;

This shows orders bought by everyone plus the unknown order bought by Grace.

Full Outer Join

A full outer join is the combination of left and right joins. It keeps all rows from both tables, with nulls where there's no match.

SELECT name AS dish_name, customer_name, price, quantity
FROM menu
FULL JOIN orders ON menu.id = orders.menu_id;

This shows entries from the left join (products not bought by anyone) as well as the entry from the right join (Grace's unknown product order).

When to Use Each Join

• Inner join: Get only records that are connected (products that have been bought and customers who bought products)
• Left join: Get all products, even those not bought by anyone
• Right join: Get all customers, even those who haven't bought anything (like Grace)
• Full outer join: Get both products not bought and customers who haven't bought

In practice, right joins are often written as left joins with the tables swapped, as it feels more natural.