Why JOINs combine related tables in MySQL - Visual Breakdown

Concept Flow - Why JOINs combine related tables

Start with Table A

↓

Start with Table B

↓

Find matching rows based on join condition

↓

Combine matching rows into one result row

↓

Repeat for all rows in Table A

↓

Output combined rows as result set

JOINs take two tables, find rows that match a condition, and combine those rows into one result row.

Execution Sample

MySQL

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

This query combines orders with their customers by matching customer IDs.

Execution Table

Step	orders.id	orders.customer_id	customers.id	customers.name	Join Condition Met?	Output Row
1	101	1	1	Alice	Yes	(101, Alice)
2	101	1	2	Bob	No	No output
3	102	2	1	Alice	No	No output
4	102	2	2	Bob	Yes	(102, Bob)
5	103	3	1	Alice	No	No output
6	103	3	2	Bob	No	No output
Exit	-	-	-	-	All rows checked	Query ends

💡 All rows in orders checked against customers; join condition no longer met for new pairs.

Variable Tracker

Variable	Start	After Step 1	After Step 4	Final
Current orders row	None	id=101, customer_id=1	id=102, customer_id=2	id=103, customer_id=3
Current customers row	None	id=1, name=Alice	id=2, name=Bob	id=2, name=Bob
Output rows	Empty	[(101, Alice)]	[(101, Alice), (102, Bob)]	[(101, Alice), (102, Bob)]

Key Moments - 2 Insights

Why do some rows from orders not appear in the output?

Rows from orders only appear if their customer_id matches a customers.id. Rows without a match are skipped, as shown in execution_table rows 3, 5, and 6.

Why does the join condition check both tables' columns?

The join condition connects related rows by comparing columns from both tables. Without this, rows wouldn't combine meaningfully, as seen in the 'Join Condition Met?' column.

Visual Quiz - 3 Questions

Look at the execution_table, what is the output row at Step 4?

A No output

B (101, Alice)

C (102, Bob)

D (103, Alice)

💡 Hint

Check the 'Output Row' column at Step 4 in the execution_table.

At which step does the join condition fail for orders.id=103 and customers.id=2?

A Step 6

B Step 5

C Step 4

D Step 1

💡 Hint

Look at the 'Join Condition Met?' column for orders.id=103 and customers.id=2 in the execution_table.

If orders.customer_id=3 matched customers.id=3, how would the output change?

A No change in output rows

B A new output row would appear for orders.id=103

C All output rows would be removed

D Only orders.id=101 would appear

💡 Hint

Refer to variable_tracker showing output rows after each step.

Concept Snapshot

JOINs combine rows from two tables where a condition matches.
Syntax: SELECT columns FROM A JOIN B ON condition;
Only rows meeting the condition appear combined.
Useful to relate data from different tables.
Without matching, rows are excluded from results.

Full Transcript

JOINs combine related tables by matching rows based on a condition. For example, orders and customers tables join on customer IDs. Each order row is checked against customer rows. When the customer_id in orders matches the id in customers, those rows combine into one output row. Rows without matches do not appear in the result. This process repeats for all rows in the first table. The output is a new table showing combined information from both tables where the join condition is true.

Practice

1. Why do we use JOINs in SQL when working with multiple tables?

easy

A. To create new tables automatically

B. To delete rows from multiple tables at once

C. To combine rows from related tables based on matching columns

D. To change the data type of columns

Solution

1. Step 1: Understand the purpose of JOINs

   JOINs are used to combine rows from two or more tables based on a related column between them.

2. Step 2: Identify the correct use case

   Combining related data helps to see connected information in one result, which matches To combine rows from related tables based on matching columns.

3. Final Answer:

   To combine rows from related tables based on matching columns -> Option C

4. Quick Check:

   JOINs combine related tables = A [OK]

Hint: JOINs link tables by matching columns to combine data [OK]

Common Mistakes:

• Thinking JOINs delete or create tables
• Confusing JOINs with data type changes
• Assuming JOINs work without matching columns

2. Which of the following is the correct syntax to join two tables orders and customers on the column customer_id?

easy

A. SELECT * FROM orders JOIN customers WHER orders.customer_id = customers.customer_id;

B. SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id;

C. SELECT * FROM orders, customers ON orders.customer_id = customers.customer_id;

D. SELECT * FROM orders JOIN customers USING customer_id;

Solution

1. Step 1: Review JOIN syntax

   The correct JOIN syntax uses the ON clause to specify the matching columns between tables.

2. Step 2: Compare options

   SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id; uses JOIN with ON correctly. SELECT * FROM orders JOIN customers WHERE orders.customer_id = customers.customer_id; uses WHERE incorrectly for JOIN. SELECT * FROM orders, customers ON orders.customer_id = customers.customer_id; misuses ON without JOIN. SELECT * FROM orders JOIN customers USING customer_id; uses USING correctly and is valid syntax.

3. Final Answer:

   SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id; -> Option B

4. Quick Check:

   JOIN with ON clause = A [OK]

Hint: Use ON to specify join condition, not WHERE [OK]

Common Mistakes:

• Using WHERE instead of ON for JOIN condition
• Missing JOIN keyword before ON
• Confusing USING and ON syntax

3. Given these tables:
employees(id, name, dept_id)
departments(dept_id, dept_name)
What will this query return?

SELECT employees.name, departments.dept_name FROM employees JOIN departments ON employees.dept_id = departments.dept_id;

medium

A. An error because dept_id is missing

B. A list of all employees without department names

C. A list of all departments without employee names

D. A list of employee names with their department names

Solution

1. Step 1: Understand the JOIN condition

   The query joins employees and departments where dept_id matches, linking employees to their departments.

2. Step 2: Analyze the SELECT columns

   The query selects employee names and their corresponding department names, so the output shows pairs of employee and department.

3. Final Answer:

   A list of employee names with their department names -> Option D

4. Quick Check:

   JOIN on dept_id returns employee and department pairs = D [OK]

Hint: JOIN returns combined rows matching ON condition [OK]

Common Mistakes:

• Assuming JOIN returns unmatched rows
• Confusing columns in SELECT with JOIN condition
• Expecting error when columns exist

4. What is wrong with this JOIN query?

SELECT * FROM orders JOIN customers ON orders.customer_id customers.customer_id;

medium

A. Missing '=' operator in ON clause

B. Missing JOIN keyword

C. Using WHERE instead of ON

D. No tables specified

Solution

1. Step 1: Check the ON clause syntax

   The ON clause must have a condition with an operator like '=', but here it is missing between the two columns.

2. Step 2: Verify other parts

   JOIN keyword and tables are present, so the main error is the missing '=' operator.

3. Final Answer:

   Missing '=' operator in ON clause -> Option A

4. Quick Check:

   ON clause needs '=' operator = C [OK]

Hint: ON clause must have '=' between columns [OK]

Common Mistakes:

• Omitting '=' in ON condition
• Confusing ON with WHERE syntax
• Leaving out JOIN keyword

5. You have two tables:
students(id, name, class_id)
classes(class_id, class_name)
You want to list all students with their class names, but also include students without a class assigned. Which JOIN should you use?

hard

A. LEFT JOIN

B. INNER JOIN

C. RIGHT JOIN

D. CROSS JOIN

Solution

1. Step 1: Understand JOIN types

   INNER JOIN returns only matching rows. LEFT JOIN returns all rows from the left table and matching rows from the right.

2. Step 2: Apply to problem

   To include all students even if they have no class, use LEFT JOIN from students to classes.

3. Final Answer:

   LEFT JOIN -> Option A

4. Quick Check:

   Include all left table rows = LEFT JOIN = B [OK]

Hint: Use LEFT JOIN to keep all left table rows [OK]

Common Mistakes:

• Using INNER JOIN excludes unmatched rows
• RIGHT JOIN keeps right table rows, not left
• CROSS JOIN creates all combinations, not related rows