In our previous lesson, you mastered the art of summarizing data within a single table using aggregate functions and the GROUP BY clause. This is a powerful skill, but real-world data is rarely stored in one monolithic table. This lesson will teach you how to reach across tables and combine related information, a cornerstone of relational database querying.
Specifically, we will focus on combining related tables using INNER JOIN and LEFT JOIN. You'll learn why data is often split into multiple tables and how to use JOIN clauses to piece it back together to answer more complex questions. This moves you from analyzing a single dataset to weaving together a complete picture from multiple, related sources—a fundamental task for any data engineer.
Why Data is Stored in Multiple Tables
Before we start joining tables, it's essential to understand why we need to. In most well-designed databases, data is organized into multiple tables through a process called normalization. The primary goals are to reduce data redundancy and improve data integrity.
For example, instead of storing a customer's full name and address with every single order they place, we can have one customers table and a separate orders table. The orders table would only need a small customer_id to link back to the full customer details. This way, if a customer updates their address, we only need to change it in one place.
Let's read a short explanation of this concept.
SQL Lesson 6: Multi-table queries with JOINs
This section from SQLBolt introduces the concept of normalization and why it's a common practice in database design.
Please read the first two sections of the article, from the beginning down to just before the "Multi-table queries with JOINs" heading. This will give you the foundational context for why joins are necessary.
Now that you understand the "why," let's dive into the "how."
Visually Understanding Joins
At its core, a JOIN operation combines columns from two or more tables into a new, wider result set. To understand the different types of joins, it's incredibly helpful to visualize them. The most common analogy uses Venn diagrams, where each table is a circle, and the type of join determines which part of the circles (and their overlap) is returned.
The video below from "Data with Baraa" provides an excellent visual introduction to the concept of joins.
SQL Joins Basics (Visually Explained) | INNER, LEFT, RIGHT, FULL | #SQL Course 8
This video explains the fundamental difference between combining rows (set operators) and combining columns (joins). It then introduces the main reasons for using joins and presents the visual Venn diagram analogy.
Watch the first part of the video, from the beginning up to the introduction of the different join types. This will set a strong conceptual foundation. Focus on: How joins make tables wider. The three main reasons we use joins. The Venn diagram visualization of matching vs. unmatching data.
The INNER JOIN: Finding the Overlap
The INNER JOIN is the most common type of join. It retrieves records that have matching values in both tables. Think of it as the intersection or overlapping area in our Venn diagram. If a row in the first table doesn't have a corresponding match in the second table (based on the join condition), it won't appear in the result.
This is useful when you only want to see data where a relationship explicitly exists, for example, "show me only the customers who have actually placed an order."
Let's continue with the "Data with Baraa" video for a detailed explanation of INNER JOIN.
SQL Joins Basics (Visually Explained) | INNER, LEFT, RIGHT, FULL | #SQL Course 8
This segment focuses exclusively on the INNER JOIN, covering its definition, syntax, and best practices like using table aliases.
Watch the section on INNER JOIN, from the definition through the explanation of the syntax and table order. Then, watch the practical example, paying close attention to how table aliases (c, o) are used to simplify the query.
To solidify your understanding of the syntax and try it yourself, let's turn back to the SQLBolt tutorial.
SQL Lesson 6: Multi-table queries with JOINs
This resource provides the formal syntax for INNER JOIN and gives you an interactive environment to practice.
Read the section Multi-table queries with JOINs. Note that the JOIN keyword by itself is shorthand for INNER JOIN. Then, complete the three tasks in the Exercise section at the bottom of the page. This will give you hands-on practice combining the Movies and BoxOffice tables.
The LEFT JOIN: Keeping All Data from the "Left"
What if you want to retrieve all customers, and for those who have placed orders, you want to see their order details? An INNER JOIN would exclude customers who haven't ordered. This is where the LEFT JOIN comes in.
A LEFT JOIN returns all records from the left table (the one specified after FROM), and the matched records from the right table (the one specified after LEFT JOIN). If there is no match, the result is NULL on the side of the right table. The order of tables is crucial here.
The video below gives a fantastic, practical comparison of INNER JOIN and LEFT JOIN and when to use each.
Inner Joins vs Left Joins in SQL [The Only Video You Need]
Jess Ramos from "Data, AI, & Tech" clearly explains the business context for choosing between an INNER and a LEFT join.
Watch the following segments: What is a Join? This section gives a great real-world framing for why we connect tables. Inner Join Example: Watch this part to see how an INNER JOIN returns only customers with orders. Left Join Example: Now watch this part to see how a LEFT JOIN can include customers who have not placed an order. Recap: The summary provides clear guidelines on when to use each join.
To complete your understanding, let's get the formal definition and practice with LEFT JOIN on SQLBolt.
SQLBolt - Learn SQL - SQL Lesson 7: OUTER JOINs
This tutorial introduces "outer" joins, which include LEFT JOIN. It explains how they differ from INNER JOIN by including non-matching rows.
Read the introductory paragraphs, from the beginning down to the description of FULL JOIN. You'll notice RIGHT JOIN is also mentioned; it behaves like a LEFT JOIN but keeps all rows from the right table. In practice, most RIGHT JOINs can be rewritten as LEFT JOINs by swapping the table order, which is often preferred for readability. After reading, complete the exercises at the bottom of the page to practice writing LEFT JOIN queries.
Conclusion
You've now added one of the most powerful tools in SQL to your toolkit. You can move beyond single-table analysis and start connecting disparate data sources to build a comprehensive view.
Let's recap the key takeaways:
- Normalization is the practice of splitting data into multiple tables to reduce redundancy and improve integrity.
JOINclauses are used to combine these tables back together based on related columns (keys).- An
INNER JOINreturns only the set of records that have a match in both tables. It's for finding the intersection. - A
LEFT JOINreturns all records from the left table and only the matching records from the right table. Unmatched records from the right side will appear asNULL. It's for keeping a complete set from one table while enriching it with data from another.
In our next lesson, we will build upon this by learning how to structure more complex, multi-step transformations. You'll see how to use the results of a join as an intermediate step in a larger query by using Common Table Expressions (CTEs).