Why JOINs combine related tables in MySQL - Why It Works This Way

Overview - Why JOINs combine related tables

What is it?

JOINs are a way to combine rows from two or more tables based on a related column between them. They let you see connected information stored separately, like matching a customer with their orders. Without JOINs, you would have to look at each table alone and miss the full picture. JOINs help databases answer questions that involve multiple sets of data together.

Why it matters

JOINs exist because data is often split into different tables to keep it organized and avoid repetition. Without JOINs, you would have to copy data everywhere or manually combine it outside the database, which is slow and error-prone. JOINs let you quickly and correctly combine related data, making databases powerful and efficient for real-world tasks like tracking sales, users, or inventory.

Where it fits

Before learning JOINs, you should understand what tables and columns are, and how data is stored in rows. After JOINs, you can learn about more complex queries, filtering combined data, and optimizing performance with indexes and query plans.

Mental Model

Core Idea

JOINs connect rows from different tables by matching related columns to show combined information as if it were one table.

Think of it like...

JOINs are like matching puzzle pieces from two different boxes by their shapes so they fit together to show a bigger picture.

Table A (Customers)       Table B (Orders)
┌─────────────┐           ┌─────────────┐
│ CustomerID  │           │ OrderID     │
│ Name        │           │ CustomerID  │
└─────────────┘           └─────────────┘
     │                          │
     └───────────JOIN ON────────┘
             CustomerID
             ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
Combined Result Table
┌─────────────┬─────────────┐
│ Name        │ OrderID     │
└─────────────┴─────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding tables and rows

Concept: Learn what tables and rows are in a database and how data is stored.

A database stores data in tables. Each table has rows (records) and columns (fields). For example, a Customers table might have columns like CustomerID and Name. Each row is one customer’s data.

Result

You can see data organized in rows and columns, like a spreadsheet.

Knowing tables and rows is essential because JOINs work by combining rows from different tables.

2

Foundation

What is a related column?

Concept: Identify columns that link tables, usually keys like CustomerID.

Tables often share a column that connects them. For example, Orders table has CustomerID that matches CustomerID in Customers table. This shared column is called a key and is how tables relate.

Result

You understand that CustomerID in both tables points to the same customer.

Recognizing related columns is key to knowing how JOINs find matching rows.

3

Intermediate

Basic INNER JOIN usage

🤔Before reading on: do you think INNER JOIN returns all rows from both tables or only matching rows? Commit to your answer.

Concept: Learn how INNER JOIN returns only rows where the related columns match in both tables.

An INNER JOIN combines rows from two tables where the related column values are equal. For example: SELECT Customers.Name, Orders.OrderID FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID; This query shows customers who have orders.

Result

Only customers with matching orders appear in the result.

Understanding INNER JOIN helps you see how databases combine only related data, avoiding unrelated rows.

4

Intermediate

Different JOIN types explained

🤔Before reading on: do you think LEFT JOIN includes rows without matches or excludes them? Commit to your answer.

Concept: Explore how LEFT JOIN, RIGHT JOIN, and FULL JOIN include unmatched rows differently.

LEFT JOIN returns all rows from the left table and matching rows from the right. If no match, right columns are NULL. RIGHT JOIN is the opposite. FULL JOIN returns all rows from both tables, matching where possible. Example LEFT JOIN: SELECT Customers.Name, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID; Shows all customers, even those without orders.

Result

You see how JOIN types control which rows appear when matches are missing.

Knowing JOIN types lets you choose how to combine data depending on what you want to see.

5

Intermediate

JOINs with multiple tables

Concept: Learn how to join more than two tables by chaining JOINs.

You can join several tables by adding more JOIN clauses. For example, joining Customers, Orders, and Products: SELECT Customers.Name, Orders.OrderID, Products.ProductName FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID JOIN Products ON Orders.ProductID = Products.ProductID; This shows customer orders with product names.

Result

You get combined data from three tables in one result.

Understanding multi-table JOINs lets you build complex queries that answer detailed questions.

6

Advanced

JOIN performance and indexes

🤔Before reading on: do you think JOINs are always fast or can they slow down without indexes? Commit to your answer.

Concept: Learn how indexes on related columns speed up JOINs and why missing indexes cause slow queries.

JOINs compare rows between tables, which can be slow if tables are large. Indexes on the columns used in JOIN conditions let the database find matches quickly. Without indexes, the database scans all rows, causing delays.

Result

Queries with proper indexes run much faster.

Knowing how indexes affect JOIN speed helps you write efficient queries and design better databases.

7

Expert

Unexpected JOIN behavior and NULLs

🤔Before reading on: do you think NULL values in JOIN columns match or never match? Commit to your answer.

Concept: Understand how NULLs affect JOINs and why NULL = NULL does not match in SQL JOINs.

In SQL, NULL means unknown. When JOINing on columns with NULLs, rows with NULL do not match each other because NULL = NULL is false. This can cause missing rows in JOIN results unexpectedly. To handle this, you may need special conditions or use IS NULL checks.

Result

You realize JOINs exclude NULL matches unless handled explicitly.

Understanding NULL behavior prevents subtle bugs and missing data in JOIN queries.

Under the Hood

JOINs work by the database engine scanning rows from one table and finding matching rows in the other table based on the JOIN condition. Internally, the engine uses algorithms like nested loops, hash joins, or merge joins depending on data size and indexes. It combines matching rows into a single output row. If no match is found, behavior depends on JOIN type (e.g., LEFT JOIN includes unmatched rows with NULLs).

Why designed this way?

JOINs were designed to keep data normalized—stored separately to avoid duplication—and still allow combining related data efficiently. Early databases needed a way to relate tables without copying data. The JOIN operation was created as a flexible, declarative way to express these relationships. Different JOIN types were added to cover various real-world needs for including or excluding unmatched data.

┌─────────────┐       ┌─────────────┐
│   Table A   │       │   Table B   │
│  (Rows)    │       │  (Rows)    │
└─────┬───────┘       └─────┬───────┘
      │                     │
      │  JOIN condition     │
      │  (e.g., A.id = B.id)│
      ▼                     ▼
┌───────────────────────────────┐
│   Database Join Engine         │
│  - Finds matching rows         │
│  - Combines into result rows   │
└─────────────┬─────────────────┘
              │
              ▼
      ┌─────────────────┐
      │ Result Table    │
      └─────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does INNER JOIN include rows without matches? Commit to yes or no.

Common Belief: INNER JOIN returns all rows from both tables, even if they don't match.

Reality: INNER JOIN returns only rows where the join condition matches in both tables.

Why it matters: Thinking INNER JOIN includes unmatched rows leads to wrong query results and confusion about missing data.

Quick: Do NULL values match each other in JOIN conditions? Commit to yes or no.

Common Belief: NULL values in join columns match each other like normal values.

Reality: NULL does not equal NULL in SQL, so rows with NULL in join columns do not match.

Why it matters: Misunderstanding NULL behavior causes missing rows in JOIN results and hard-to-find bugs.

Quick: Does adding more JOINs always make queries slower? Commit to yes or no.

Common Belief: More JOINs always slow down queries significantly.

Reality: With proper indexes and query plans, multiple JOINs can still run efficiently.

Why it matters: Assuming JOINs are always slow may prevent building needed complex queries.

Quick: Does LEFT JOIN always return more rows than INNER JOIN? Commit to yes or no.

Common Belief: LEFT JOIN always returns more rows than INNER JOIN.

Reality: LEFT JOIN returns all left table rows, but if all have matches, it returns same rows as INNER JOIN.

Why it matters: Misunderstanding JOIN types can lead to unexpected query results and wrong data interpretation.

Expert Zone

1

JOIN order can affect query performance but not the final result; understanding query planner helps optimize.

2

Using USING clause in JOINs can simplify syntax but hides which columns are joined, affecting readability.

3

Some JOINs can be rewritten as EXISTS or IN subqueries for performance or clarity in specific cases.

When NOT to use

JOINs are not ideal when data is denormalized or when you only need data from one table. Alternatives include subqueries, UNIONs, or application-level joins when data is from different sources or formats.

Production Patterns

In production, JOINs are used to combine user data with transactions, link products with categories, or merge logs with metadata. Indexes on join keys and query analysis tools are used to keep JOINs fast. Sometimes materialized views pre-join data for faster reads.

Connections

Relational Algebra

JOINs implement the relational algebra operation called 'join', which combines relations (tables) based on common attributes.

Understanding JOINs as a mathematical operation clarifies their behavior and helps in learning database theory and optimization.

Set Theory

JOINs relate to set operations like intersection and union, combining sets of rows based on conditions.

Knowing set theory helps grasp how JOINs include or exclude rows, especially with different JOIN types.

Social Networks

JOINs are like connecting people by their relationships, such as friends or followers, to find common connections.

Seeing JOINs as social connections helps understand why linking related data is powerful and natural.

Common Pitfalls

#1 Joining tables without specifying the join condition causes a huge result with all combinations.

Wrong approach:

SELECT * FROM Customers, Orders;

Correct approach:

SELECT * FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Root cause:

Not understanding that JOIN needs a condition to match rows leads to a Cartesian product, which is usually unintended.

#2 Using WHERE instead of ON for join conditions can cause incorrect filtering.

Wrong approach:

SELECT * FROM Customers LEFT JOIN Orders WHERE Customers.CustomerID = Orders.CustomerID;

Correct approach:

SELECT * FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Root cause:

Confusing WHERE and ON clauses causes the join to behave like an INNER JOIN, losing unmatched rows.

#3 Assuming NULL values match in JOIN conditions and expecting those rows to appear.

Wrong approach:

SELECT * FROM TableA JOIN TableB ON TableA.col = TableB.col; -- where col has NULLs

Correct approach:

SELECT * FROM TableA JOIN TableB ON TableA.col = TableB.col OR (TableA.col IS NULL AND TableB.col IS NULL);

Root cause:

Not knowing that NULL = NULL is false in SQL causes missing matches for NULL values.

Key Takeaways

JOINs combine rows from related tables by matching values in shared columns to show connected data.

Different JOIN types control whether unmatched rows are included or excluded in the result.

Proper indexes on join columns are essential for fast JOIN queries, especially on large tables.

NULL values do not match each other in JOIN conditions, which can cause unexpected missing rows.

Understanding JOINs is crucial for working with normalized databases and writing powerful queries.