Part 1: Understanding SQL JOINs | SQL Challenges & Capstone
SQL Joins & Advanced Group Filtering Practice · Part 1 · Lecture Notes
Understanding SQL JOINs
How to combine related business tables using shared key columns — the foundation of multi-table analysis and relational reporting.
Part 1 of 520% Complete
JOIN…ON
Table.Column Notation
Shared Keys
Multi-Table Analysis
8 Practice Questions
Learning Objectives
Understand why relational databases store data across multiple tables and how JOINs reconnect them
Use the JOIN…ON structure to combine two tables using a shared key column
Apply table.column notation to reference columns from specific tables and avoid ambiguity
Identify the shared keys in the course database: user_id, product_id, and order_id
Click any section header to open or close it
JOINS combine related tables using shared columns. Relational databases deliberately split business data across multiple tables — customers in one table, their transactions in another, their orders in a third. JOINS are the mechanism that brings this data back together for analysis.
In industry, analysts use JOINS to connect customer and transaction data, analyze orders and products together, combine subscriptions with user profiles, build multi-table operational reports, and create the linked dashboards that show the full customer journey in one view.
The Basic Structure
SELECT names the columns you want from both tables. FROM names the left (first) table. JOIN names the right (second) table. ON defines the matching condition — which column in the left table equals which column in the right table.
Basic JOIN Syntax
SELECT columns
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;
When joining tables, column names must be prefixed with the table they come from — table.column — to avoid ambiguity. If both tables have a column named user_id, the database needs to know which one you mean. The dot notation users.user_id and transactions.user_id makes this unambiguous.
Always Use Table.Column Notation in JOINs
In single-table queries, writing just column_name is fine. In JOIN queries, always write table_name.column_name for every column in SELECT. This eliminates ambiguity and makes the query immediately readable — a reader can see at a glance which column comes from which table.
Combining Users with Transactions
SELECT
users.first_name,
transactions.amount
FROM users
JOIN transactions
ON users.user_id = transactions.user_id;
The ON clause specifies the shared key — the column that connects the two tables. In this course database, user_id is the key that connects users to transactions, orders, subscriptions, support_tickets, sessions, and campaign_events. product_id connects products to order_items. order_id connects orders to order_items.
Primary Key and Foreign Key
The primary key uniquely identifies each row in its table (user_id in users). A foreign key in another table references that primary key (user_id in transactions). The ON clause links these: ON users.user_id = transactions.user_id.
Link Orders to Customers
SELECT
orders.order_id,
users.first_name
FROM orders
JOIN users
ON orders.user_id = users.user_id;
Link Products to Order Items
SELECT
products.product_name,
order_items.quantity
FROM products
JOIN order_items
ON products.product_id = order_items.product_id;
The same JOIN pattern applies to every table relationship in the database. Users connect to subscriptions via user_id. Users connect to campaign_events via user_id. The table names and column names change, but the structure — SELECT columns FROM left JOIN right ON left.key = right.key — is always the same.
JOIN Is the Most Important SQL Skill
In professional analytics environments, almost every meaningful query involves at least one JOIN. Single-table queries answer basic questions; JOINs answer business questions — because business questions almost always span more than one table.
Users With Subscription Fees
SELECT
users.first_name,
subscriptions.monthly_fee
FROM users
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.
SQL Answer
SELECT
users.first_name,
ROUND(transactions.amount, 2) AS amount
FROM users
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.
SQL Answer
SELECT
users.first_name,
orders.order_status
FROM users
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.
SQL Answer
SELECT
products.product_name,
order_items.quantity
FROM products
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.
SQL Answer
SELECT
users.first_name,
ROUND(subscriptions.monthly_fee, 2) AS monthly_fee
FROM users
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.
SQL Answer
SELECT
users.first_name,
support_tickets.ticket_status
FROM users
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.
SQL Answer
SELECT
users.first_name,
campaign_events.event_type
FROM users
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.
SQL Answer
SELECT
order_items.order_id,
products.product_name
FROM order_items
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.
SQL Answer
SELECT
users.first_name,
sessions.device_type
FROM users
JOIN sessions
ON users.user_id = sessions.user_id;
Knowledge Check
Five questions — select the best answer for each.
1. What does a JOIN do in SQL?
AIt duplicates rows in a table
BIt combines related tables using a shared column
CIt filters rows based on a condition
DIt calculates the total of two columns
Correct. JOIN merges rows from two tables by matching values in a shared column specified in the ON clause — connecting related business data stored in separate tables.
Not quite. JOIN combines related tables by matching values in a shared column. It doesn’t filter, duplicate, or calculate.
2. What does the ON clause do in a JOIN?
AIt names the output columns
BIt specifies the matching condition between the two tables
CIt filters the joined result
DIt limits the number of rows returned
Correct. ON defines the relationship — which column in the left table matches which column in the right table. Without ON, the database cannot determine how the tables relate.
Not quite. ON specifies the matching condition — the shared column values that connect rows from both tables.
3. Why must column names be prefixed with the table name (table.column) in JOIN queries?
ASQL requires this syntax for all queries
BTo tell the database which table each column comes from when the same column name exists in both tables
CTo make the query run faster
DTo allow GROUP BY to work correctly
Correct. When two tables both have a column called user_id, writing just user_id is ambiguous. users.user_id and transactions.user_id are unambiguous — each references the column from a specific table.
Not quite. Table.column notation removes ambiguity when the same column name exists in both joined tables.
4. In the query FROM users JOIN transactions ON users.user_id = transactions.user_id, what does users.user_id = transactions.user_id mean?
AThe two columns are added together
BOnly users with more transactions than user_id are returned
CRows are matched where the user_id value in users equals the user_id value in transactions
DThe user_id column is renamed to match between tables
Correct. The ON clause matches rows by value — a user row is joined to a transaction row when both share the same user_id value. This is how the customer-transaction relationship is expressed in SQL.
Not quite. ON users.user_id = transactions.user_id matches rows from both tables where the user_id values are equal.
5. A plain JOIN (without specifying INNER, LEFT, or RIGHT) behaves like which type of join by default?
ALEFT JOIN
BRIGHT JOIN
CINNER JOIN
DFULL JOIN
Correct. In MySQL (and most SQL databases), JOIN without a keyword defaults to INNER JOIN — returning only rows with matching values in both tables. Specifying INNER JOIN explicitly produces the same result.
Not quite. A plain JOIN defaults to INNER JOIN in MySQL — it returns only rows that have matching values in both tables.
Part 1 Summary
The key ideas from this lecture — keep these in mind as you move forward.
JOINs allow analysts to combine related business tables and analyze connected operational data across multiple datasets. Use JOIN…ON to connect tables via shared keys. Always use table.column notation. A plain JOIN defaults to INNER JOIN.
