SQL JOINs: A Practical Guide to Combining Data from Multiple Tables

Introduction

Relational databases store data in multiple tables rather than placing everything into a single large table. This approach reduces duplication, improves data organization, and makes databases easier to maintain.

However, storing information across multiple tables creates an important question:

How do we retrieve related information from different tables at the same time?

This is where SQL JOINs come in.

A SQL JOIN allows you to combine rows from two or more tables based on a related column between them.

For example, imagine an e-commerce database with these tables:

• customers - contains customer information
• orders - contains customer orders
• products - contains product information

A customer might be stored in the customers table while their orders are stored in the orders table.

To answer a question such as:

"Show me each customer's name and the orders they have placed."

we need to combine the two tables using a JOIN.

This article explains SQL JOINs, the major types of JOINs, when to use each one, and practical examples using PostgreSQL.

 - What is an SQL JOIN?

An SQL JOIN combines rows from two or more tables based on a related column between them.

Consider these two tables.

customers

customer_id	customer_name	city
1	John	Nairobi
2	Mary	Mombasa
3	Peter	Kisumu

orders

order_id	customer_id	amount
101	1	5000
102	2	7500
103	1	3000

The customer_id column connects the two tables.

For example:

customers.customer_id
        ↓
orders.customer_id

We can use this relationship to retrieve information from both tables:

SELECT
    customers.customer_name,
    orders.order_id,
    orders.amount
FROM customers
JOIN orders
    ON customers.customer_id = orders.customer_id;

Result:

customer_name	order_id	amount
John	101	5000
Mary	102	7500
John	103	3000

The JOIN allows us to combine the customer information with their corresponding orders.

---

- Why Are JOINs Important?

JOINs are one of the most important concepts in SQL because real-world databases are usually normalized into multiple related tables.

For example, a banking database might contain:

customers
accounts
transactions
branches
loans

A company could ask:

Which customers made transactions above KSh 100,000?

The customer information and transaction information may exist in different tables.

A JOIN allows us to bring those datasets together.

JOINs are commonly used for:

• Combining customer and order information
• Connecting employees to departments
• Combining products and sales
• Connecting transactions to customers
• Creating business intelligence reports
• Building dashboards
• Data analysis
• Data cleaning and preparation
• Generating reports

---

- The Basic JOIN Syntax

The general syntax is:

SELECT columns
FROM table1
JOIN table2
    ON table1.common_column = table2.common_column;

For example:

SELECT
    customers.customer_name,
    orders.order_id
FROM customers
JOIN orders
    ON customers.customer_id = orders.customer_id;

The ON clause tells SQL how the tables are related.

In this example:

customers.customer_id = orders.customer_id

is the relationship between the tables.

---

- INNER JOIN

An INNER JOIN returns only rows that have matching values in both tables.

Syntax:

SELECT columns
FROM table1
INNER JOIN table2
    ON table1.column = table2.column;

Consider:

customers

customer_id	name
1	John
2	Mary
3	Peter

orders

order_id	customer_id
101	1
102	2
103	1

Peter has no order.

Run:

SELECT
    customers.name,
    orders.order_id
FROM customers
INNER JOIN orders
    ON customers.customer_id = orders.customer_id;

Result:

name	order_id
John	101
Mary	102
John	103

Peter does not appear because there is no matching record in orders.

When should you use INNER JOIN?

Use an INNER JOIN when you only want records that exist in both tables.

For example:

Show customers who have placed orders.

SELECT
    c.name,
    o.order_id
FROM customers c
INNER JOIN orders o
    ON c.customer_id = o.customer_id;

This is particularly useful when analyzing transactions, sales, purchases, or other activities where you only care about records with a valid relationship.

---

- LEFT JOIN

A LEFT JOIN returns:

• All rows from the left table
• Matching rows from the right table
• NULL when there is no match

Syntax:

SELECT columns
FROM table1
LEFT JOIN table2
    ON table1.column = table2.column;

For example:

SELECT
    c.name,
    o.order_id
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id;

Result:

name	order_id
John	101
John	103
Mary	102
Peter	NULL

Peter appears even though he has no order.

The NULL tells us that there is no matching order.

When should you use LEFT JOIN?

Use a LEFT JOIN when the records from the first table are important even if they do not have a match.

For example:

Show all customers, including customers who have never placed an order.

SELECT
    c.name,
    o.order_id
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id;

This is extremely useful in data analysis.

For example, you might want to identify:

• Customers who have never purchased
• Products that have never sold
• Employees who are not assigned to projects
• Branches with no transactions
• Students who have not submitted assignments

---

- Finding Records With No Match

One of the most useful patterns with LEFT JOIN is finding records that don't have a corresponding record in another table.

For example:

Find customers who have never placed an order.

SELECT
    c.customer_id,
    c.name
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

The LEFT JOIN keeps all customers.

The condition:

WHERE o.customer_id IS NULL

then keeps only customers who don't have a matching order.

This pattern is commonly called an anti-join pattern.

---

- RIGHT JOIN

A RIGHT JOIN is the opposite of a LEFT JOIN.

It returns:

• All rows from the right table
• Matching rows from the left table
• NULL where no match exists

Example:

SELECT
    c.name,
    o.order_id
FROM customers c
RIGHT JOIN orders o
    ON c.customer_id = o.customer_id;

When should you use RIGHT JOIN?

Use a RIGHT JOIN when the table on the right side of the JOIN is the table whose every row must be preserved.

---

- FULL  JOIN

A FULL  JOIN returns all rows from both tables.

It includes:

• Matching rows
• Rows that exist only in the first table
• Rows that exist only in the second table

Example:

SELECT
    c.name,
    o.order_id
FROM customers c
FULL OUTER JOIN orders o
    ON c.customer_id = o.customer_id;

If a customer has no order, the order columns will contain NULL.

If an order has no matching customer, the customer columns will contain NULL.

When should you use FULL  JOIN?

Use a FULL OUTER JOIN when you need to compare two datasets and want to preserve everything from both sides.

It can be useful for:

• Data reconciliation
• Comparing two datasets
• Identifying missing records

For example:

Compare customers in two different systems and identify records missing from either system.

---

- SELF JOIN

A SELF JOIN occurs when a table is joined to itself.

This is useful when rows within the same table have relationships with other rows in that table.

Consider an employee table:

employees

employee_id	employee_name	manager_id
1	James	NULL
2	Mary	1
3	Peter	1
4	Sarah	2

Here, manager_id references another employee in the same table.

We can retrieve employees and their managers:

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

Result:

employee	manager
James	NULL
Mary	James
Peter	James
Sarah	Mary

When should you use SELF JOIN?

Self joins are useful for hierarchical data such as:

• Employees and managers
• Categories and parent categories
• Organizational structures
• Referral relationships
• Parent-child records

---

- Joining More Than Two Tables

SQL allows you to join multiple tables.

Suppose we have:

customers
orders
products

The relationships are:

customers
    |
    | customer_id
    ↓
orders
    |
    | product_id
    ↓
products

We can retrieve information from all three tables:

SELECT
    c.name,
    o.order_id,
    p.product_name,
    o.quantity
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id
JOIN products p
    ON o.product_id = p.product_id;

This allows us to answer questions such as:

Which products did each customer purchase?

Result:

name	order_id	product_name	quantity
John	101	Laptop	1
Mary	102	Phone	2
John	103	Monitor	1

Multiple-table JOINs are extremely common in real-world SQL analysis.

---

- Using Table Aliases

When queries involve multiple tables, table names can become repetitive.

For example:

SELECT
    customers.customer_name,
    orders.order_id
FROM customers
JOIN orders
    ON customers.customer_id = orders.customer_id;

We can simplify this using aliases:

SELECT
    c.customer_name,
    o.order_id
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id;

Here:

customers c

means we can refer to customers as c.

Similarly:

orders o

means we can refer to orders as o.

Aliases make complex SQL queries easier to read.

---

- Choosing the Right JOIN

A simple way to choose a JOIN is to ask:

• Do I only want matching records?

Use:

INNER JOIN

Example:

Customers who have orders.

---

• Do I want everything from my main table?

Use:

LEFT JOIN

Example:

All customers, including those without orders.

---

• Do I want everything from the second table?

Use:

RIGHT JOIN

Example:

All orders, including those without matching customer records.

In practice, this can often be rewritten using LEFT JOIN.

---

• Do I want everything from both tables?

Use:

FULL JOIN

Example:

Compare two datasets and identify records that exist on either side.

---

• Does a table contain a relationship to itself?

Use:

SELF JOIN

Example:

Match employees with their managers.

---

- A Practical Business Example

Imagine a company has three tables.

Customers

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100),
    city VARCHAR(100)
);

Orders

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    order_date DATE,
    amount NUMERIC(10, 2)
);

Products

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(100),
    price NUMERIC(10, 2)
);

A business analyst might ask:

"Show the customer, order amount, and product purchased."

If orders also contains product_id, we can write:

SELECT
    c.customer_name,
    o.order_id,
    p.product_name,
    o.amount
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id
JOIN products p
    ON o.product_id = p.product_id;

We have now combined three related datasets.

This is the type of SQL query commonly used when preparing data for Power BI dashboards, business reports, and analytics.

---

- JOINs and Data Analytics

JOINs are particularly important for data analysts.

Consider a company with:

customers
transactions
products
branches
employees

A data analyst might need to answer:

• What is the total revenue by customer?
• Which products generate the most revenue?
• Which branch has the highest sales?
• Which customers have never purchased?
• What is the average transaction value?
• Which employees generated the most sales?
• How does revenue vary by region?

The required information may exist in several different tables.

JOINs allow the analyst to combine these tables before performing calculations.

For example:

SELECT
    b.branch_name,
    SUM(t.amount) AS total_revenue
FROM transactions t
JOIN branches b
    ON t.branch_id = b.branch_id
GROUP BY b.branch_name
ORDER BY total_revenue DESC;

This query combines transactional data with branch information and calculates revenue for each branch.

---

Conclusion

SQL JOINs allow us to combine related information stored across multiple tables. They are fundamental to working with relational databases and are used extensively in data analysis, reporting, business intelligence, and application development.

The most important JOINs to master first are:

1. INNER JOIN — when you only need matching records.
2. LEFT JOIN — when you need all records from your main table.
3. FULL  JOIN — when you need records from both sides, including unmatched records.
4. SELF JOIN — when rows within the same table are related.

For most data analyst workflows, INNER JOIN and LEFT JOIN should be your first priority.

Once you understand how tables are related and how ON, WHERE, GROUP BY, and aggregate functions work with JOINs, you can begin writing SQL queries that answer real business questions rather than simply retrieving individual tables.

The key question to ask whenever you encounter multiple tables is:

"How are these tables related, and which records do I need to keep?"

That question will usually guide you toward the correct JOIN.