Joining Multiple Tables in SQL

SQL Tutorials

Joining Multiple Tables in SQL enables you to combine data from different tables into a single meaningful result. In most real-world databases, data is organized in multiple tables. This means that you have to join the tables to create reports, view data, and answer business questions.

In most of your real-world projects, you will be joining three or more tables.

Why Do We Join Multiple Tables?

Good database design prevents the problem of repeating data. Therefore, we break down information into different tables.

Example:

Customers table holds customer information
Orders table holds order information
Products table holds product information

Each table has a different function. To view complete order information, you have to join them.

Example Tables

Customers

customer_id	customer_name
1	Ram
2	Vedika

Orders

order_id	customer_id	product_id
101	1	18
102	2	22

Products

product_id	product_name
18	Laptop
22	Mobile

The tables connect using keys:

customer_id links Customers and Orders
product_id links Orders and Products

Joining Three Tables

You can join them using INNER JOIN.

SELECT 
    customers.customer_name,
    products.product_name,
    orders.order_id
FROM orders
INNER JOIN customers 
    ON orders.customer_id = customers.customer_id
INNER JOIN products 
    ON orders.product_id = products.product_id;

This query works in steps.

First, SQL joins Orders with Customers.
In Next, it joins that result with Products.
Finally, it shows the selected columns.

Result

customer_name	product_name	order_id
Ram	Laptop	101
vedika	Mobile	102

Use Aliases to Keep It Simple

Long table names make queries harder to read. So, use aliases in your queries

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

This version is shorter and cleaner.

Using LEFT JOIN with Multiple Tables

Sometimes you want all customers, even if they have no orders. In that case, use LEFT JOIN.

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

Now, customers without orders will still appear. The order fields will show NULL.

Then, write JOIN conditions carefully. With practice, multi table joins become easy to understand and write.