SQL Joins & Advanced Group Filtering Practice · Part 2 · Lecture Notes
Mastering the INNER JOIN
How to retrieve only matching records from two related tables — the strictest join type that requires both sides to have a corresponding row.
Part 2 of 540% Complete
INNER JOIN
Matching Records Only
No NULLs
Excluded Non-Matches
8 Practice Questions
Learning Objectives
Use INNER JOIN to retrieve only records with matching values in both tables
Understand that INNER JOIN excludes rows without a match on either side
Recognize that INNER JOIN and plain JOIN produce identical results in MySQL
Apply INNER JOIN across the course database: users-transactions, users-orders, products-order_items, users-subscriptions
Click any section header to open or close it
INNER JOIN returns only records that have matching values in both tables. If a row in the left table has no match in the right table, it is excluded from the result. If a row in the right table has no match in the left table, it is also excluded. Only the intersection — the rows that exist in both — is returned.
In industry, analysts use INNER JOIN to connect customers with their transactions, analyze completed business relationships, retrieve linked operational records where both sides of the relationship must exist, and combine related datasets for performance reporting.
INNER JOIN Is the Default
A plain JOIN and an INNER JOIN produce identical results in MySQL. Writing INNER JOIN explicitly communicates your intent clearly — that you want only matching records — and is the professional standard in production SQL environments.
Basic INNER JOIN Syntax
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
INNER JOIN is the strictest join type. A user without any transactions does not appear. A transaction without a matching user does not appear. Only users who have transactions — and only those transactions — are included in the result. The output is always a subset of both tables.
What INNER JOIN Excludes
Users with no transactions: excluded. Transactions with no matching user_id in users: excluded. INNER JOIN enforces the requirement that both sides of the relationship must be present. Use LEFT JOIN when you want to include records that have no match.
Users With Matching Transactions
SELECT
users.first_name,
ROUND(transactions.amount, 2) AS amount
FROM users
INNER JOIN transactions
ON users.user_id = transactions.user_id;
INNER JOIN is ideal for joining tables where the relationship must exist. Products that have been ordered appear in order_items; joining products to order_items with INNER JOIN returns only products that have actually been ordered — excluding catalogue items with no sales history.
Shared Business Relationships
INNER JOIN is the right choice when you want to analyze established business relationships — customers who have placed orders, products that have been sold, users who have subscriptions. All three require both sides to exist.
Products With Order Quantities
SELECT
products.product_name,
order_items.quantity
FROM products
INNER JOIN order_items
ON products.product_id = order_items.product_id;
Users With Their Orders
SELECT
users.first_name,
orders.order_status
FROM users
INNER JOIN orders
ON users.user_id = orders.user_id;
Customer transaction analysis requires both a customer and a transaction to be present — making INNER JOIN the natural choice. Every row in the result represents a real business event: a named customer made a transaction of a specific amount. There are no NULL values in the output because unmatched rows are excluded.
No NULLs in INNER JOIN Results
Because INNER JOIN only returns matched rows, neither table contributes NULLs. If you see NULLs in a JOIN result, it is either a LEFT JOIN or RIGHT JOIN — not an INNER JOIN. NULL-free results are one way to verify an INNER JOIN is working correctly.
Customer Names With Subscription Fees
SELECT
users.first_name,
ROUND(subscriptions.monthly_fee, 2) AS monthly_fee
FROM users
INNER JOIN subscriptions
ON users.user_id = subscriptions.user_id;
Work through each question below. Run your query first, then use Check Answer. A wrong answer reveals the correct SQL with a Copy button; a correct result earns a congratulations message.
Practice 1Retrieve first_name and amount by joining users and transactions rounded to 2 decimal places
AI Prompt
Write a MySQL query to retrieve first_name and amount by joining users and transactions rounded to 2 decimal places using INNER JOIN.
SQL Answer
SELECT
users.first_name,
ROUND(transactions.amount, 2) AS amount
FROM users
INNER JOIN transactions
ON users.user_id = transactions.user_id;
Practice 2Retrieve first_name and order_status by joining users and orders
AI Prompt
Write a MySQL query to retrieve first_name and order_status by joining users and orders using INNER JOIN.
SQL Answer
SELECT
users.first_name,
orders.order_status
FROM users
INNER JOIN orders
ON users.user_id = orders.user_id;
Practice 3Retrieve product_name and quantity by joining products and order_items
AI Prompt
Write a MySQL query to retrieve product_name and quantity by joining products and order_items using INNER JOIN.
SQL Answer
SELECT
products.product_name,
order_items.quantity
FROM products
INNER JOIN order_items
ON products.product_id = order_items.product_id;
Practice 4Retrieve first_name and monthly_fee by joining users and subscriptions rounded to 2 decimal places
AI Prompt
Write a MySQL query to retrieve first_name and monthly_fee by joining users and subscriptions rounded to 2 decimal places using INNER JOIN.
SQL Answer
SELECT
users.first_name,
ROUND(subscriptions.monthly_fee, 2) AS monthly_fee
FROM users
INNER JOIN subscriptions
ON users.user_id = subscriptions.user_id;
Practice 5Retrieve first_name and ticket_status by joining users and support_tickets
AI Prompt
Write a MySQL query to retrieve first_name and ticket_status by joining users and support_tickets using INNER JOIN.
SQL Answer
SELECT
users.first_name,
support_tickets.ticket_status
FROM users
INNER JOIN support_tickets
ON users.user_id = support_tickets.user_id;
Practice 6Retrieve first_name and event_type by joining users and campaign_events
AI Prompt
Write a MySQL query to retrieve first_name and event_type by joining users and campaign_events using INNER JOIN.
SQL Answer
SELECT
users.first_name,
campaign_events.event_type
FROM users
INNER JOIN campaign_events
ON users.user_id = campaign_events.user_id;
Practice 7Retrieve order_id and product_name by joining order_items and products
AI Prompt
Write a MySQL query to retrieve order_id and product_name by joining order_items and products using INNER JOIN.
SQL Answer
SELECT
order_items.order_id,
products.product_name
FROM order_items
INNER JOIN products
ON order_items.product_id = products.product_id;
Practice 8Retrieve first_name and device_type by joining users and sessions
AI Prompt
Write a MySQL query to retrieve first_name and device_type by joining users and sessions using INNER JOIN.
SQL Answer
SELECT
users.first_name,
sessions.device_type
FROM users
INNER JOIN sessions
ON users.user_id = sessions.user_id;
Knowledge Check
Five questions — select the best answer for each.
1. What does INNER JOIN return?
AAll records from the left table, even without matches
BAll records from both tables, including unmatched ones
COnly records that have matching values in both tables
DOnly records that have no match in either table
Correct. INNER JOIN returns the intersection — rows where the ON condition is satisfied in both tables. Rows without a match in either table are excluded.
Not quite. INNER JOIN returns only rows with matching values in both tables — unmatched rows from either side are excluded.
2. A user has no transactions. What happens to that user in an INNER JOIN of users and transactions?
AThe user appears with NULL in the transaction columns
BThe user is excluded from the result
CThe user appears multiple times
DThe query returns an error
Correct. INNER JOIN requires a match in both tables. A user with no transactions has no matching row in the transactions table, so that user row is excluded entirely from the result.
Not quite. INNER JOIN excludes rows without a match. A user with no transactions is simply not included in the result.
3. What is the difference between JOIN and INNER JOIN in MySQL?
AJOIN returns all rows; INNER JOIN returns only matches
BThey produce identical results — JOIN defaults to INNER JOIN
CINNER JOIN is faster than JOIN
DJOIN works on two tables; INNER JOIN works on three or more
Correct. In MySQL, a plain JOIN defaults to INNER JOIN. Both produce identical output. Writing INNER JOIN explicitly makes the intent clearer to anyone reading the query.
Not quite. In MySQL, JOIN and INNER JOIN produce identical results. JOIN defaults to INNER JOIN behavior.
4. An analyst runs users INNER JOIN subscriptions ON users.user_id = subscriptions.user_id. A subscription record exists with a user_id that does not appear in the users table. What happens to that subscription?
AIt appears with NULL in the user columns
BIt is excluded because no matching user exists
CIt causes a foreign key error
DIt appears at the top of the results
Correct. INNER JOIN excludes rows from either table that have no match. A subscription without a matching user_id in users is excluded just as a user without subscriptions is excluded.
Not quite. INNER JOIN excludes unmatched rows from both sides. A subscription with no matching user is excluded from the result.
5. In an INNER JOIN result, can any column from either joined table ever contain a NULL value?
AYes — any column can be NULL in an INNER JOIN
BNo — INNER JOIN only returns rows where both sides matched, so no NULLs appear from the join itself
COnly the left table columns can be NULL
DOnly the right table columns can be NULL
Correct. Because INNER JOIN only returns rows where both tables have a matching value, neither side contributes NULLs from the join itself. If NULLs appear, they come from NULL values that were already in the original data.
Not quite. INNER JOIN excludes unmatched rows, so no NULLs result from the join itself. Any NULLs in the output come from original NULL data in the tables.
Part 2 Summary
The key ideas from this lecture — keep these in mind as you move forward.
INNER JOIN helps analysts retrieve matching business records from related tables and analyze connected operational data. Only rows with a match in both tables are returned. INNER JOIN and plain JOIN are identical in MySQL. No NULLs result from the join itself.
INNER JOINMatching RecordsIntersectionExcluded RowsNo NULLsShared RelationshipsLinked Data
