JOIN: Reading Data from Related Tables

Welcome back.

In the previous lesson, you learned about relationships.

You learned:

what a primary key is;
what a foreign key is;
why tables should be connected;
how one-to-many relationships work;
how PostgreSQL protects relationships.

Very good.

Now we know how to connect tables.

But there is still one problem.

When we query products, we see this:

Laptop | category_id = 1
Mouse  | category_id = 1
Chair  | category_id = 2

Useful.

But not very friendly.

Humans do not want to read category IDs.

Humans want names.

We want this:

Laptop | Electronics
Mouse  | Electronics
Chair  | Furniture

That is where JOIN helps.

JOIN lets us read data from multiple related tables in one query.

This is where relational databases start to feel powerful.

And slightly fancy.

Like SQL wearing a jacket.

What You Will Learn

In this lesson, you will learn:

what JOIN does;
why JOIN is useful;
how to use INNER JOIN;
how to connect tables using primary keys and foreign keys;
how to select columns from multiple tables;
how to use table aliases;
how to avoid confusing column names;
how to use LEFT JOIN;
the difference between INNER JOIN and LEFT JOIN;
how to join more than two tables;
common mistakes with JOIN.

By the end of this lesson, you will be able to combine data from related tables.

This is a very important step.

Without JOIN, relationships are hidden.

With JOIN, relationships become readable.

The database stops showing you secret numbers.

And starts showing useful information.

Very kind.

Very PostgreSQL.

Prepare the Database

Open PostgreSQL:

sudo -iu postgres psql

Connect to your database:

\c learning_postgresql

If you do not have this database, create it:

CREATE DATABASE learning_postgresql;

Then connect:

\c learning_postgresql

Now remove old tables if they exist:

DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;

We drop products first because it depends on categories.

PostgreSQL cares about relationships.

Like a strict database parent.

Create the Tables

Create the categories table:

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) UNIQUE NOT NULL
);

Create the products table:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price NUMERIC(10, 2) CHECK (price >= 0),
  category_id INTEGER REFERENCES categories(id)
);

Now we have two related tables.

categories.id is the primary key.

products.category_id is the foreign key.

This is the relationship:

categories.id -> products.category_id

One category can have many products.

Very normal.

Very useful.

Very database.

Insert Data

Insert categories:

INSERT INTO categories (name)
VALUES
  ('Electronics'),
  ('Furniture'),
  ('Office');

Insert products:

INSERT INTO products (name, price, category_id)
VALUES
  ('Laptop', 900.00, 1),
  ('Mouse', 25.00, 1),
  ('Keyboard', 70.00, 1),
  ('Desk Chair', 150.00, 2),
  ('Bookshelf', 120.00, 2),
  ('Notebook', 5.00, 3),
  ('Pen', 2.00, 3);

Check both tables:

SELECT * FROM categories;
SELECT * FROM products;

The products table shows category_id.

That is correct.

But not beautiful.

The database knows the connection.

Now we need to ask it to show the connection.

That is the job of JOIN.

The Problem Without JOIN

Run:

SELECT * FROM products;

You will see something like:

id | name       | price  | category_id
---|------------|--------|------------
1  | Laptop     | 900.00 | 1
2  | Mouse      | 25.00  | 1
3  | Keyboard   | 70.00  | 1
4  | Desk Chair | 150.00 | 2

This is useful for PostgreSQL.

But for a human, category_id = 1 is not very helpful.

We need to see the category name.

The category name is in another table.

So we need to combine:

products
categories

That is exactly what JOIN does.

What Is JOIN?

JOIN combines rows from two or more tables.

It uses a relationship between the tables.

Example relationship:

products.category_id = categories.id

This means:

A product belongs to a category when its category_id matches the category id.

A basic JOIN query looks like this:

SELECT products.name, categories.name
FROM products
JOIN categories
ON products.category_id = categories.id;

This tells PostgreSQL:

Start with products.
Join categories.
Match rows where products.category_id equals categories.id.
Show product name and category name.

This is the heart of JOIN.

Tables meet.

Rows match.

Data becomes readable.

Very civilized.

Your First JOIN

Run this query:

SELECT products.name, categories.name
FROM products
JOIN categories
ON products.category_id = categories.id;

You should see something like:

name       | name
-----------|-------------
Laptop     | Electronics
Mouse      | Electronics
Keyboard   | Electronics
Desk Chair | Furniture
Bookshelf  | Furniture
Notebook   | Office
Pen        | Office

This works.

But there is a small problem.

Both columns are called name.

That can be confusing.

One name is the product name.

The other name is the category name.

PostgreSQL allows it.

Your brain may not.

So we should use aliases.

Column Aliases

A column alias gives a better name to a result column.

Use AS.

Example:

SELECT
  products.name AS product_name,
  categories.name AS category_name
FROM products
JOIN categories
ON products.category_id = categories.id;

Now the result is clearer:

product_name | category_name
-------------|---------------
Laptop       | Electronics
Mouse        | Electronics
Keyboard     | Electronics
Desk Chair   | Furniture
Bookshelf    | Furniture
Notebook     | Office
Pen          | Office

Much better.

Aliases make query results easier to read.

The database is still doing the same work.

But now humans are less confused.

That is always good.

Usually.

Selecting More Columns

You can select more columns from both tables.

Example:

SELECT
  products.id,
  products.name AS product_name,
  products.price,
  categories.name AS category_name
FROM products
JOIN categories
ON products.category_id = categories.id;

This gives:

id | product_name | price  | category_name
---|--------------|--------|---------------
1  | Laptop       | 900.00 | Electronics
2  | Mouse        | 25.00  | Electronics
3  | Keyboard     | 70.00  | Electronics

Now we have useful product data and readable category data.

This is very common in real applications.

A user does not want:

category_id = 1

A user wants:

Electronics

Your application can show nicer data because SQL did the join.

Good SQL makes the frontend happier.

And a happy frontend complains less.

Sometimes.

Table Aliases

Writing full table names can become long.

This:

SELECT
  products.name AS product_name,
  categories.name AS category_name
FROM products
JOIN categories
ON products.category_id = categories.id;

works.

But we can make it shorter with table aliases:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id;

Here:

p means products;
c means categories.

This is shorter.

Very common.

Professional SQL often uses aliases.

Not because developers love mystery letters.

Well, maybe a little.

But mostly because queries become easier to write.

JOIN with WHERE

You can use WHERE with JOIN.

Example:

SELECT
  p.name AS product_name,
  p.price,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id
WHERE c.name = 'Electronics';

This returns only Electronics products.

Example result:

Laptop   | 900.00 | Electronics
Mouse    | 25.00  | Electronics
Keyboard | 70.00  | Electronics

This is powerful.

We are filtering by data from the joined table.

The product table does not store Electronics.

It stores category_id.

But with JOIN, we can filter by category name.

Very useful.

Very real.

JOIN with ORDER BY

You can also sort joined results.

Example:

SELECT
  p.name AS product_name,
  p.price,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id
ORDER BY p.price DESC;

This sorts products from most expensive to cheapest.

You can also sort by category name:

SELECT
  p.name AS product_name,
  p.price,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id
ORDER BY c.name ASC, p.name ASC;

This sorts first by category.

Then by product name.

Very neat.

Very organized.

Like a database that cleaned its room.

Rare but beautiful.

INNER JOIN

When you write:

JOIN categories

PostgreSQL treats it like:

INNER JOIN categories

So these two queries are equivalent:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id;

and:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
INNER JOIN categories AS c
ON p.category_id = c.id;

INNER JOIN returns only rows that have a match in both tables.

So if a product has no matching category, it will not appear.

But because we use a foreign key, PostgreSQL normally prevents products with invalid categories.

So INNER JOIN is often exactly what we need.

Clean.

Strict.

Useful.

LEFT JOIN

LEFT JOIN returns all rows from the left table.

Even if there is no match in the right table.

Example:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
LEFT JOIN categories AS c
ON p.category_id = c.id;

This means:

Show all products.
If a category exists, show it.
If not, show NULL.

In our current table, every product has a valid category.

So the result looks the same as INNER JOIN.

But LEFT JOIN becomes useful when some rows may not have related data.

For example:

users and profiles
customers and orders
posts and comments

Sometimes you want to show all users, even users without profiles.

That is where LEFT JOIN is useful.

INNER JOIN vs LEFT JOIN

Simple rule:

Use INNER JOIN when you only want rows with matches.

Use LEFT JOIN when you want all rows from the first table, even if the second table has no match.

Example:

INNER JOIN:
Only products with categories.

LEFT JOIN:
All products, with category if available.

Think of INNER JOIN like a strict party:

No match, no entry.

Think of LEFT JOIN like a kinder party:

Come in anyway. We will show NULL if your friend is missing.

Database parties are strange.

But educational.

Create an Example for LEFT JOIN

Let us allow a product without a category.

First, we need a new table where category_id can be empty.

Drop old products:

DROP TABLE IF EXISTS products;

Create it again:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price NUMERIC(10, 2) CHECK (price >= 0),
  category_id INTEGER REFERENCES categories(id)
);

This still allows category_id to be NULL, because we did not write NOT NULL.

Insert products:

INSERT INTO products (name, price, category_id)
VALUES
  ('Laptop', 900.00, 1),
  ('Mouse', 25.00, 1),
  ('Mystery Box', 50.00, NULL);

Now run INNER JOIN:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
INNER JOIN categories AS c
ON p.category_id = c.id;

Mystery Box does not appear.

Why?

Because it has no matching category.

Now run LEFT JOIN:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
LEFT JOIN categories AS c
ON p.category_id = c.id;

Now Mystery Box appears.

Its category is NULL.

This is the difference.

Small word.

Big behavior.

JOIN More Than Two Tables

You can join more than two tables.

Imagine:

customers
orders
products

But let us make a simpler example:

authors
posts
comments

For now, we will use:

customers
orders

Drop old tables if needed:

DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;

Create customers:

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

Create orders:

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  total NUMERIC(10, 2) CHECK (total >= 0),
  customer_id INTEGER REFERENCES customers(id)
);

Insert customers:

INSERT INTO customers (name)
VALUES
  ('Anna'),
  ('Marco'),
  ('Sofia');

Insert orders:

INSERT INTO orders (total, customer_id)
VALUES
  (49.99, 1),
  (120.00, 1),
  (35.50, 2);

Now join:

SELECT
  o.id AS order_id,
  c.name AS customer_name,
  o.total
FROM orders AS o
JOIN customers AS c
ON o.customer_id = c.id;

Result:

order_id | customer_name | total
---------|---------------|-------
1        | Anna          | 49.99
2        | Anna          | 120.00
3        | Marco         | 35.50

Notice Sofia has no orders.

With INNER JOIN, Sofia does not appear.

With LEFT JOIN, we can show her too.

SELECT
  c.name AS customer_name,
  o.total
FROM customers AS c
LEFT JOIN orders AS o
ON c.id = o.customer_id;

Now Sofia appears with NULL total.

Very useful for reports.

For example:

Show all customers, even customers with no orders.

That is a classic LEFT JOIN situation.

Common Mistakes

Forgetting the ON Condition

Wrong:

SELECT *
FROM products
JOIN categories;

This is not what you want.

You must tell PostgreSQL how the tables are connected.

Correct:

SELECT *
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id;

The ON condition is the bridge.

Without it, you are not connecting the tables properly.

You are inviting chaos to dinner.

Joining the Wrong Columns

Wrong:

ON p.id = c.id

This may accidentally match rows with the same ID.

But that is not the relationship.

Correct:

ON p.category_id = c.id

The foreign key points to the primary key.

Use the actual relationship.

Do not join columns just because the numbers look friendly.

Numbers lie.

Especially in databases.

Not Using Aliases

This works:

SELECT products.name, categories.name
FROM products
JOIN categories
ON products.category_id = categories.id;

But this is clearer:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id;

Aliases make queries easier to read.

And readable SQL is less likely to betray you at midnight.

Selecting Ambiguous Columns

This may be confusing:

SELECT name
FROM products
JOIN categories
ON products.category_id = categories.id;

Both tables have a name column.

PostgreSQL may complain or the query may be unclear.

Better:

SELECT
  products.name AS product_name,
  categories.name AS category_name
FROM products
JOIN categories
ON products.category_id = categories.id;

Always be clear when columns have the same name.

The database likes clarity.

So do humans.

Mostly.

Practice

Write these queries.

Show product names with category names:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id;

Show product names, prices, and category names:

SELECT
  p.name AS product_name,
  p.price,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id;

Show only Electronics products:

SELECT
  p.name AS product_name,
  p.price,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id
WHERE c.name = 'Electronics';

Show products sorted by price:

SELECT
  p.name AS product_name,
  p.price,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id
ORDER BY p.price DESC;

Show all products, even those without categories:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
LEFT JOIN categories AS c
ON p.category_id = c.id;

Run the queries.

Change them.

Break them.

Fix them.

That is still the best way to learn.

SQL does not enter the brain through theory alone.

It enters through mistakes.

Very educational mistakes.

Mini Challenge

Create two tables:

authors
posts

Rules:

one author can have many posts;
each post belongs to one author;
author name is required;
post title is required;
post content can be long text.

Create tables:

CREATE TABLE authors (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(150) NOT NULL,
  content TEXT,
  author_id INTEGER REFERENCES authors(id)
);

Insert data:

INSERT INTO authors (name)
VALUES
  ('Anna'),
  ('Marco'),
  ('Sofia');

INSERT INTO posts (title, content, author_id)
VALUES
  ('First Post', 'Hello from PostgreSQL.', 1),
  ('SQL Notes', 'JOIN makes tables readable.', 1),
  ('Database Life', 'Data wants structure.', 2);

Now write queries to:

show all posts with author names;
show only posts by Anna;
show authors and their posts using LEFT JOIN;
show authors even if they have no posts;
sort posts by title.

This challenge is very close to real blog systems.

Authors.

Posts.

Relationships.

Queries.

Very real.

No magic.

Just keys and joins.

Beautiful little machinery.

Summary

Today you learned:

JOIN combines data from related tables;
JOIN uses relationships between tables;
INNER JOIN returns only matching rows;
LEFT JOIN returns all rows from the left table;
foreign keys help connect tables correctly;
ON defines how tables are connected;
aliases make queries easier to read;
column aliases make results clearer;
WHERE can filter joined results;
ORDER BY can sort joined results;
joining wrong columns gives wrong data;
JOIN makes relationships visible.

This is a huge step.

Now your tables are no longer isolated.

You can connect data.

You can show readable results.

You can stop showing users mysterious IDs and start showing real names.

That is database progress.

Small query.

Big upgrade.

Next Lesson

In the next lesson, we will learn aggregate functions.

We will count rows.

Calculate totals.

Find averages.

Find minimum and maximum values.

In other words, we will start asking questions like:

How many products do we have?
What is the total value of all orders?
What is the average price?
Which product is the most expensive?

PostgreSQL will answer.

Politely.

If we write the query correctly.