INNER JOIN in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does an INNER JOIN do in SQL?

An INNER JOIN returns only the rows where there is a match in both tables based on the join condition.

beginner

Write the basic syntax of an INNER JOIN between two tables table1 and table2 on column id.

SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

beginner

If a row in table1 has no matching row in table2, will it appear in the INNER JOIN result?

No, only rows with matching values in both tables appear in the INNER JOIN result.

intermediate

How is INNER JOIN different from LEFT JOIN?

INNER JOIN returns only matching rows in both tables, while LEFT JOIN returns all rows from the left table and matching rows from the right table (or NULL if no match).

intermediate

Can you use INNER JOIN to join more than two tables?

Yes, you can chain multiple INNER JOINs to combine more than two tables by specifying join conditions for each pair.

What rows does an INNER JOIN return?

A Only rows with matching values in both tables

B All rows from the left table

C All rows from the right table

D All rows from both tables

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

Which keyword can replace INNER JOIN and produce the same result?

A OUTER JOIN

B LEFT JOIN

C JOIN

D CROSS JOIN

JOIN by itself is the same as INNER JOIN in SQL.

What happens if there is no matching row in the second table for INNER JOIN?

A The row is excluded from the result

B The row is duplicated

C An error occurs

D The row from the first table is included with NULLs

INNER JOIN excludes rows without matches in both tables.

Which clause specifies the condition for an INNER JOIN?

A WHERE

B ON

C GROUP BY

D HAVING

The ON clause defines the join condition for INNER JOIN.

Can INNER JOIN be used to join tables on multiple columns?

A Only with subqueries

B No, only one column can be used

C Only with UNION

D Yes, by specifying multiple conditions in ON

You can join on multiple columns by adding conditions combined with AND in the ON clause.

Explain in your own words what an INNER JOIN does and when you would use it.

Think about matching pairs in two lists.

Describe the difference between INNER JOIN and LEFT JOIN with an example scenario.

Imagine a guest list and RSVP responses.

Practice

1. What does an INNER JOIN do in MySQL?

easy

A. Combines rows from two tables where matching values exist in both tables.

B. Returns all rows from the first table regardless of matches.

C. Returns all rows from the second table regardless of matches.

D. Combines rows even if there is no matching value in either table.

Solution

1. Step 1: Understand INNER JOIN purpose

   INNER JOIN returns rows only when there is a match in both tables based on the join condition.

2. Step 2: Compare options with definition

   Combines rows from two tables where matching values exist in both tables. correctly describes this behavior. Options A, C, and D describe other join types or incorrect behavior.

3. Final Answer:

   Combines rows from two tables where matching values exist in both tables. -> Option A

4. Quick Check:

   INNER JOIN = matching rows only [OK]

Hint: INNER JOIN returns only rows with matching keys in both tables [OK]

Common Mistakes:

• Confusing INNER JOIN with LEFT or RIGHT JOIN
• Thinking INNER JOIN returns unmatched rows
• Assuming INNER JOIN returns all rows from one table

2. Which of the following is the correct syntax for an INNER JOIN in MySQL?

easy

A. SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

B. SELECT * FROM table1 INNER JOIN table2 WHERE table1.id = table2.id;

C. SELECT * FROM table1 INNER JOIN table2 USING table1.id;

D. SELECT * FROM table1 INNER JOIN table2 ON table1.id == table2.id;

Solution

1. Step 1: Review INNER JOIN syntax

   The correct syntax uses INNER JOIN with ON and a single equals sign (=) for comparison.

2. Step 2: Analyze each option

   SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id; uses correct syntax. SELECT * FROM table1 INNER JOIN table2 WHERE table1.id = table2.id; uses WHERE instead of ON, which is incorrect for JOIN conditions. SELECT * FROM table1 INNER JOIN table2 USING table1.id; uses USING with table prefix which is invalid; USING requires unqualified column names. SELECT * FROM table1 INNER JOIN table2 ON table1.id == table2.id; uses double equals (==) which is invalid in SQL.

3. Final Answer:

   SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id; -> Option A

4. Quick Check:

   INNER JOIN syntax uses ON with = [OK]

Hint: Use ON with single = for INNER JOIN conditions [OK]

Common Mistakes:

• Using WHERE instead of ON for join condition
• Using double equals (==) instead of single equals (=)
• Confusing USING syntax with ON syntax

3. Given two tables:
Employees(id, name, dept_id)
Departments(dept_id, dept_name)
What will this query return?

SELECT Employees.name, Departments.dept_name FROM Employees INNER JOIN Departments ON Employees.dept_id = Departments.dept_id;

medium

A. All departments with employees, even if no employees exist.

B. All employees, even if they have no department.

C. An error because dept_id is ambiguous.

D. All employees with their department names, only if department exists.

Solution

1. Step 1: Understand INNER JOIN effect on these tables

   The INNER JOIN returns rows where Employees.dept_id matches Departments.dept_id, so only employees with existing departments appear.

2. Step 2: Analyze query output

   The query selects employee names and their department names only for matching dept_id pairs. Employees without a department are excluded.

3. Final Answer:

   All employees with their department names, only if department exists. -> Option D

4. Quick Check:

   INNER JOIN filters to matching dept_id rows [OK]

Hint: INNER JOIN shows only matching rows from both tables [OK]

Common Mistakes:

• Thinking all employees appear even without departments
• Confusing INNER JOIN with LEFT JOIN
• Assuming ambiguous column error without aliasing

4. Identify the error in this query:

SELECT e.name, d.dept_name FROM Employees e INNER JOIN Departments d ON e.dept_id = d.id;

medium

A. Missing WHERE clause for join condition.

B. Column d.id does not exist; should be d.dept_id.

C. Alias 'e' is not allowed in INNER JOIN.

D. Using INNER JOIN without SELECT * causes error.

Solution

1. Step 1: Check join condition columns

   The join condition uses d.id, but Departments table has dept_id column, not id.

2. Step 2: Identify correct column name

   It should be d.dept_id to match Employees.dept_id. Other options are incorrect because aliasing is allowed, WHERE is not needed for JOIN, and SELECT * is not mandatory.

3. Final Answer:

   Column d.id does not exist; should be d.dept_id. -> Option B

4. Quick Check:

   Join columns must exist and match [OK]

Hint: Check column names in JOIN condition carefully [OK]

Common Mistakes:

• Using wrong column names in ON clause
• Thinking WHERE is required for JOIN
• Believing aliases are not allowed

5. You have two tables:
Orders(order_id, customer_id, amount)
Customers(customer_id, name)
You want to find customers who placed orders over 100 dollars. Which query correctly uses INNER JOIN to get customer names and order amounts over 100?

hard

A. SELECT Customers.name, Orders.amount FROM Orders INNER JOIN Customers ON Orders.customer_id = Customers.customer_id WHERE Orders.amount < 100;

B. SELECT Customers.name, Orders.amount FROM Customers LEFT JOIN Orders ON Customers.customer_id = Orders.customer_id WHERE Orders.amount > 100;

C. SELECT Customers.name, Orders.amount FROM Customers INNER JOIN Orders ON Customers.customer_id = Orders.customer_id WHERE Orders.amount > 100;

D. SELECT Customers.name, Orders.amount FROM Orders INNER JOIN Customers ON Orders.customer_id = Customers.customer_id WHERE Orders.amount >= 100;

Solution

1. Step 1: Understand the requirement

   We want customers with orders over 100 dollars, so filter Orders.amount > 100.

2. Step 2: Check INNER JOIN usage and filter

   SELECT Customers.name, Orders.amount FROM Customers INNER JOIN Orders ON Customers.customer_id = Orders.customer_id WHERE Orders.amount > 100; correctly joins Customers and Orders on customer_id and filters orders with amount > 100. SELECT Customers.name, Orders.amount FROM Customers LEFT JOIN Orders ON Customers.customer_id = Orders.customer_id WHERE Orders.amount > 100; uses LEFT JOIN instead of INNER JOIN. SELECT Customers.name, Orders.amount FROM Orders INNER JOIN Customers ON Orders.customer_id = Customers.customer_id WHERE Orders.amount >= 100; uses >= 100 which includes 100, not strictly over 100. SELECT Customers.name, Orders.amount FROM Orders INNER JOIN Customers ON Orders.customer_id = Customers.customer_id WHERE Orders.amount < 100; filters for amount < 100, opposite of requirement.

3. Final Answer:

   SELECT Customers.name, Orders.amount FROM Customers INNER JOIN Orders ON Customers.customer_id = Orders.customer_id WHERE Orders.amount > 100; -> Option C

4. Quick Check:

   INNER JOIN + filter amount > 100 = SELECT Customers.name, Orders.amount FROM Customers INNER JOIN Orders ON Customers.customer_id = Orders.customer_id WHERE Orders.amount > 100; [OK]

Hint: Use INNER JOIN with WHERE to filter matching rows [OK]

Common Mistakes:

• Using LEFT JOIN instead of INNER JOIN
• Using wrong comparison operator in WHERE
• Filtering on wrong table or column