Mastering How to Join 3 Tables in SQL with Where Condition: A Step-by-Step Guide

Joining tables in SQL is a fundamental operation that allows you to combine data from multiple tables into a single result set. Sometimes, you might want to join tables based on certain conditions, which is where the "WHERE" clause comes into play. In this guide, we'll walk you through the process of joining three tables in SQL with a "WHERE" condition, making your data analysis and querying more powerful and precise.

three vennuous circles with the words right table, left table and right table

Before we dive into the specifics, let's assume we have three simple tables: " Customers ", " Orders ", and " OrderDetails ". The goal is to retrieve orders along with their customer details and order details, but only for orders worth more than 100. Let's start by understanding the basic SQL JOIN syntax.

Give me 2 mins—I'll teach you how SQL JOINs work: The Join clause lets… | Neo Kim | 82 comments

Understanding SQL JOIN and WHERE Clause

The SQL JOIN clause is used to combine rows from two or more tables based on a related column between them. The WHERE clause, on the other hand, filters records. To join three tables and include a WHERE condition, we'll use the INNER JOIN operator for table linking and the WHERE operator to filter results.

SQL Joins Explained with Examples | Beginner SQL Guide

Now, let's break down this task into smaller, manageable subtopics to understand the process better.

Setting Up the JOIN Clause

SQL Joins Explained with Examples | Inner Join, Left Join, Right Join & Full Join

The JOIN clause combines data from multiple tables, creating a kind of "virtual" table that can be queried. To join three tables - "Customers", "Orders", and "OrderDetails" - we'll use the following syntax:

"INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID"

Here, we're joining the tables based on the related columns: "CustomerID" (Customers and Orders) and "OrderID" (Orders and OrderDetails).

Before proceeding with the WHERE clause, let's ensure we understand the ordered data. To do this, we can write a simple SQL query without any 'WHERE' condition:

SQL JOINS Made Easy 🔗 | INNER, LEFT, RIGHT & FULL JOIN with Examples

SELECT Customers.CustomerName, Orders.OrderID, OrderDetails.ProductName, OrderDetails.Quantity, OrderDetails.UnitPrice
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID;

Applying the WHERE Clause

Now that we have the joined tables, let's add a "WHERE" condition to filter out orders worth less than or equal to 100. To do this, we'll use the following syntax:

WHERE OrderDetails.Quantity * OrderDetails.UnitPrice > 100;

In this WHERE clause, we're multiplying the "Quantity" and "UnitPrice" columns from the "OrderDetails" table to calculate the total order value, and filtering out orders worth 100 or less.

What are Semi, Anti, and Natural Joins in SQL?

Here's the complete SQL query with the join and WHERE conditions:

SELECT Customers.CustomerName, Orders.OrderID, OrderDetails.ProductName, OrderDetails.Quantity, OrderDetails.UnitPrice
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
WHERE OrderDetails.Quantity * OrderDetails.UnitPrice > 100;

Handling Aggregated Data and Grouping Results

SQL Joins Explained with Examples | Complete JOIN Cheat Sheet for Beginners

a poster with the names and symbols for different types of letters

SQL Basics Cheat Sheet for Beginners | Learn SQL Fast 💻

Complete SQL Cheat Sheet for Beginners | SQL Commands, Joins, Queries & Database Guide

SQL Cheatsheet PDF | SQL Commands, Joins & Queries Explained

Types of Joins in SQL Explained Clearly 📊 | Inner Join, Left Join, Right Join, Full Join |

Creating Databases | MySQL for Beginners

SQL Joins Cheat Sheet Every Developer Should Save

SQL Tutorial: The Ultimate Beginners Guide

Here's my Ultimate SQL Joins Cheatsheet: (Save this post now for your interview prep) Most candidates learn JOINs by memorizing patterns: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN. But… | Sohan Sethi

What Is a Database in SQL? | SQL Database for Beginners

Sometimes, you might want to work with aggregated data or group results based on certain criteria. Let's say we want to find out the total order value for each customer, but only for orders worth more than 100. To achieve this, we'll use the "GROUP BY" clause and the "SUM" function.

Here's the updated SQL query with aggregation and grouping:

SELECT Customers.CustomerName, SUM(OrderDetails.Quantity * OrderDetails.UnitPrice) as TotalOrderValue
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
WHERE OrderDetails.Quantity * OrderDetails.UnitPrice > 100
GROUP BY Customers.CustomerName
ORDER BY TotalOrderValue DESC;

In this query, we're grouping results by the "CustomerName" column and calculating the total order value for each customer using the "SUM" function. The "ORDER BY" clause is optional but helpful, as it sorts the results by the total order value in descending order.

Congratulations! You've just learned how to join three tables in SQL with a "WHERE" condition. This skill will prove invaluable in your data analysis and querying tasks. Don't forget to explore other SQL features, like subqueries, CTEs (Common Table Expressions), and window functions, to make your queries even more powerful and efficient. Happy coding!

Understanding SQL JOIN and WHERE Clause

Setting Up the JOIN Clause

Applying the WHERE Clause

Handling Aggregated Data and Grouping Results

Related Articles