SQL Joins Practice Exercises: 15 Real Queries & Answers
Master SQL joins with 15 real business practice exercises. Solve INNER, LEFT, RIGHT, FULL OUTER, CROSS, and SELF JOINs with schemas and expected outputs.
In relational database systems, raw data is distributed across dozens of normalized tables to eliminate redundancy and maintain referential integrity. As an analyst or analytics engineer, your primary responsibility is assembling these decoupled tables back into unified, high-integrity analytical views.
Textbooks often introduce SQL joins using overlapping two-circle Venn diagrams. While Venn diagrams provide a basic geometric intuition for set overlap, they fail to explain true relational database operations. Real relational joins operate on Cartesian coordinate pairs, handle Cartesian fan-out, evaluate null-propagation flags, and require strict join predicate positioning.
This practice guide delivers 15 production-grade SQL join exercises across five progressively challenging tiers:
- Level 1: INNER JOIN: Exact relational matching, multi-table order histories, and subscription billing runs.
- Level 2: LEFT JOIN & Anti-Joins: Preserving base grains, detecting unengaged customers, and isolating dormant catalog inventory.
- Level 3: RIGHT & FULL OUTER JOIN: Bidirectional ledger auditing, physical warehouse reconciliation, and catalog gap detection.
- Level 4: SELF JOIN & Hierarchical Data: Managerial organizational trees, peer compensation variances, and consecutive activity detection.
- Level 5: Advanced Multi-Table & Non-Equi Joins: Fan-out mitigation with Common Table Expressions (CTEs), promo code date-range matching, and Cartesian calendar spines.
For broader interview coverage, explore our companion compilation of 25 Real Business SQL Practice Questions or master fundamental querying in Topfolio SQL Basics Course. Every course on Topfolio is 100% free to learn, with an optional ₹99 verified certificate to validate your skills.
Visual Relational Architecture
The exercises in this guide query the enterprise schema below. Pay close attention to primary keys (PK), foreign keys (FK), and relationship cardinalities.
+----------------------------------------------------------------------------------------------------+
| RELATIONAL JOIN ARCHITECTURE |
+----------------------------------------------------------------------------------------------------+
[ suppliers ] [ products ]
+-------------------------+ +-------------------------+
| supplier_id (PK) |<----------------| supplier_id (FK) |
| supplier_name VARCHAR | | product_id (PK) |
| contact_email VARCHAR | | product_name VARCHAR |
| country CHAR(2) | | category VARCHAR |
+-------------------------+ | price NUMERIC |
| stock_quantity INT |
+-------------------------+
^
| (Foreign Key)
[ customers ] [ orders ] |
+--------------------+ +--------------------+ |
| customer_id (PK) |<----| customer_id (FK) | |
| first_name VARCHAR | | order_id (PK) | |
| last_name VARCHAR | | order_date DATE | |
| email VARCHAR | | status VARCHAR | |
| city VARCHAR | | total_amount NUM | |
| state CHAR(2) | +--------------------+ |
+--------------------+ ^ |
| |
[ order_items ] |
+---------------------+ |
| order_item_id (PK) | |
| order_id (FK)-------+ |
| product_id (FK)-------------+
| quantity INT |
| unit_price NUMERIC |
+---------------------+
[ employees ] (Self-Referencing Manager Hierarchy)
+--------------------------------------------------------------------+
| employee_id (PK) | first_name | last_name | department | salary |
| manager_id (FK) ---------------------------------------+ (Self-FK) |
+--------------------------------------------------------|-----------+
v
Critical Cardinality Rules:
customerstoorders(1:N): One customer places zero, one, or many orders.orderstoorder_items(1:N): One order contains one or more line items.productstoorder_items(1:N): A product appears in zero or multiple order items.employeestoemployees(1:N Self-Join): One manager supervises multiple employees; employees have at most one manager.
SQL Join Operators Mechanics & NULL Behaviors
Before solving exercises, examine how PostgreSQL handles unmatched rows, NULL propagation, and join filters across each join type:
| Feature / Criteria |
|---|
Part 1: Relational Foundations & Join Mechanics
Understanding joins requires looking at how relational engines process predicates during execution. When two tables join, PostgreSQL logically builds an intermediate virtual table.
The ON Clause vs The WHERE Clause Filter Trap
The single most common bug in intermediate SQL interviews involves putting filter predicates on the right table inside the WHERE clause instead of the ON clause:
-- WRONG: Inadvertently turns a LEFT JOIN into an INNER JOIN!
SELECT c.customer_id, c.first_name, o.order_id, o.status
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'completed';Why this fails: A customer who has never ordered generates a row where o.status IS NULL. The WHERE o.status = 'completed' filter evaluates NULL = 'completed', which yields FALSE in three-valued SQL logic. As a result, all customers with zero orders are silently eliminated!
-- CORRECT: Preserves all customers while filtering joined orders
SELECT c.customer_id, c.first_name, o.order_id, o.status
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
AND o.status = 'completed';In the correct version, if a customer has no completed orders, the customer row is still preserved with NULL in the order fields. For a deeper study of logical execution order, review our guide to SQL Order of Execution.
Part 2: 15 Comprehensive SQL Join Exercises
Each exercise includes sample input data, the business problem statement, the PostgreSQL query, an explanation of the join mechanics, and the expected output table.
Exercise 1: Customer Order History & Fulfillment Status (INNER JOIN)
Business Scenario: The customer support portal requires an order lookup tool. For all orders with a status of 'completed' or 'shipped', retrieve the customer's full name, email address, order ID, order date, and total dollar amount.
Input Tables:
customers (sample rows):
| customer_id | first_name | last_name | city | state | |
|---|---|---|---|---|---|
| 101 | Elena | Rostova | elena.r@example.com | San Francisco | CA |
| 102 | Marcus | Vance | marcus.v@example.com | Austin | TX |
| 103 | Chloe | Bennett | chloe.b@example.com | Seattle | WA |
orders (sample rows):
| order_id | customer_id | order_date | status | total_amount |
|---|---|---|---|---|
| 5001 | 101 | 2024-03-15 | completed | 340.50 |
| 5002 | 102 | 2024-03-16 | pending | 120.00 |
| 5003 | 101 | 2024-04-02 | shipped | 89.99 |
| 5004 | 104 | 2024-04-10 | completed | 510.00 |
PostgreSQL Solution Query:
SELECT
c.first_name || ' ' || c.last_name AS customer_name,
c.email,
o.order_id,
o.order_date,
o.status,
o.total_amount
FROM customers AS c
INNER JOIN orders AS o ON c.customer_id = o.customer_id
WHERE o.status IN ('completed', 'shipped')
ORDER BY o.order_date DESC;Logic Breakdown:
c.customer_id = o.customer_idmatches customers to their specific orders.- Customer 102 placed order
5002, but its status is'pending', so it is excluded by theWHEREclause. - Order
5004belongs to customer104(not in our customer table snippet); anINNER JOINdiscards any order lacking a valid matching customer record.
Expected Output Table:
| customer_name | order_id | order_date | status | total_amount | |
|---|---|---|---|---|---|
| Elena Rostova | elena.r@example.com | 5003 | 2024-04-02 | shipped | 89.99 |
| Elena Rostova | elena.r@example.com | 5001 | 2024-03-15 | completed | 340.50 |
Exercise 2: Supplier Catalog & Product Sourcing (INNER JOIN)
Business Scenario: Procurement needs to renegotiate vendor contracts. Generate a list of all active products whose unit price exceeds $100.00, showing product name, category, unit price, supplier company name, and supplier contact email.
Input Tables:
suppliers (sample rows):
| supplier_id | supplier_name | contact_email | country |
|---|---|---|---|
| 10 | Apex Tech Components | sales@apextech.com | US |
| 20 | Nordic Sound Labs | contact@nordicsound.se | SE |
| 30 | Vertex Furnishings | supply@vertex.com | US |
products (sample rows):
| product_id | product_name | supplier_id | category | price | stock_quantity |
|---|---|---|---|---|---|
| 1 | 4K Ultra Monitor | 10 | Displays | 420.00 | 45 |
| 2 | Noise-Cancel Headset | 20 | Audio | 189.50 | 120 |
| 3 | Basic Mouse Pad | 10 | Accessories | 14.99 | 300 |
| 4 | Standing Desk Pro | 30 | Furniture | 550.00 | 18 |
PostgreSQL Solution Query:
SELECT
p.product_id,
p.product_name,
p.category,
p.price,
s.supplier_name,
s.contact_email
FROM products AS p
INNER JOIN suppliers AS s ON p.supplier_id = s.supplier_id
WHERE p.price > 100.00
ORDER BY p.price DESC;Logic Breakdown:
- Joins the child table
productsto parent tablesuppliersonp.supplier_id = s.supplier_id. - The
Basic Mouse Padmatches supplier10, but its price ($14.99) fails thep.price > 100.00filter. - Returns only high-value catalog items linked with verified supplier contact information.
Expected Output Table:
| product_id | product_name | category | price | supplier_name | contact_email |
|---|---|---|---|---|---|
| 4 | Standing Desk Pro | Furniture | 550.00 | Vertex Furnishings | supply@vertex.com |
| 1 | 4K Ultra Monitor | Displays | 420.00 | Apex Tech Components | sales@apextech.com |
| 2 | Noise-Cancel Headset | Audio | 189.50 | Nordic Sound Labs | contact@nordicsound.se |
Exercise 3: Active Subscription Billing Run (Multi-Table INNER JOIN)
Business Scenario: SaaS Finance needs to audit the monthly recurring billing batch. Join user accounts, subscription records, and plan tiers to calculate the monthly invoice amount for all users whose subscription status is 'active'.
Input Tables:
users (sample rows):
| user_id | user_name | billing_email |
|---|---|---|
| 801 | Sarah Connor | sconnor@cyberdyne.org |
| 802 | Kyle Reese | kreese@resistance.net |
| 803 | John Connor | leader@future.org |
subscriptions (sample rows):
| sub_id | user_id | plan_id | status | start_date |
|---|---|---|---|---|
| 9001 | 801 | 1 | active | 2024-01-01 |
| 9002 | 802 | 2 | cancelled | 2023-11-15 |
| 9003 | 803 | 3 | active | 2024-02-20 |
plans (sample rows):
| plan_id | plan_name | monthly_fee | seat_limit |
|---|---|---|---|
| 1 | Starter | 29.00 | 2 |
| 2 | Professional | 79.00 | 10 |
| 3 | Enterprise | 249.00 | 50 |
PostgreSQL Solution Query:
SELECT
u.user_id,
u.user_name,
u.billing_email,
p.plan_name,
p.monthly_fee
FROM users AS u
INNER JOIN subscriptions AS s ON u.user_id = s.user_id
INNER JOIN plans AS p ON s.plan_id = p.plan_id
WHERE s.status = 'active'
ORDER BY p.monthly_fee DESC;Logic Breakdown:
- Two sequential
INNER JOINoperations:userslinks tosubscriptionsonuser_id, andsubscriptionslinks toplansonplan_id. - User
802has a subscription record, but its status is'cancelled', so it is excluded by theWHEREclause. - Only active accounts with valid plan configurations are output.
Expected Output Table:
| user_id | user_name | billing_email | plan_name | monthly_fee |
|---|---|---|---|---|
| 803 | John Connor | leader@future.org | Enterprise | 249.00 |
| 801 | Sarah Connor | sconnor@cyberdyne.org | Starter | 29.00 |
Exercise 4: Unengaged Customer Detection (LEFT Anti-Join)
Business Scenario: Lifecycle marketing is preparing a re-engagement email series. Identify all registered customers who have never placed an order. Return their customer ID, full name, email, and registration date.
Input Tables:
customers (sample rows):
| customer_id | first_name | last_name | signup_date | |
|---|---|---|---|---|
| 201 | Arthur | Dent | adent@galaxy.co.uk | 2023-05-12 |
| 202 | Ford | Prefect | fprefect@betelgeuse.com | 2023-06-18 |
| 203 | Tricia | McMillan | tricia@earth.org | 2023-08-01 |
| 204 | Marvin | Android | marvin@sirius.com | 2023-09-14 |
orders (sample rows):
| order_id | customer_id | order_date | total_amount |
|---|---|---|---|
| 7001 | 201 | 2023-06-01 | 42.00 |
| 7002 | 203 | 2023-08-15 | 118.50 |
| 7003 | 201 | 2023-11-20 | 65.00 |
PostgreSQL Solution Query:
SELECT
c.customer_id,
c.first_name || ' ' || c.last_name AS customer_name,
c.email,
c.signup_date
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL
ORDER BY c.signup_date ASC;Logic Breakdown:
- The
LEFT JOINpreserves every customer in the output. For customers without matching orders (Ford PrefectandMarvin Android), the engine creates a virtual row where all columns fromordersare set toNULL. - The predicate
WHERE o.order_id IS NULLfilters out customers who have placed orders, leaving only the unengaged cohort. - This is the standard relational anti-join pattern.
Expected Output Table:
| customer_id | customer_name | signup_date | |
|---|---|---|---|
| 202 | Ford Prefect | fprefect@betelgeuse.com | 2023-06-18 |
| 204 | Marvin Android | marvin@sirius.com | 2023-09-14 |
Exercise 5: Dormant Catalog Inventory / Products Never Ordered (LEFT Anti-Join)
Business Scenario: Warehouse inventory managers want to identify dormant stock. Find all products in the catalog that have never been purchased in any order line item. Display the product ID, product name, category, unit price, and current stock count.
Input Tables:
products (sample rows):
| product_id | product_name | category | price | stock_quantity |
|---|---|---|---|---|
| 501 | Thunderbolt Docking Station | Hardware | 249.00 | 45 |
| 502 | Cat6 Ethernet Cable 10ft | Accessories | 12.50 | 400 |
| 503 | Retro Mechanical Keycaps | Accessories | 45.00 | 80 |
| 504 | Ergonomic Footrest | Furniture | 65.00 | 25 |
order_items (sample rows):
| order_item_id | order_id | product_id | quantity | unit_price |
|---|---|---|---|---|
| 1 | 8001 | 501 | 1 | 249.00 |
| 2 | 8002 | 502 | 3 | 12.50 |
| 3 | 8003 | 501 | 2 | 249.00 |
PostgreSQL Solution Query:
SELECT
p.product_id,
p.product_name,
p.category,
p.price,
p.stock_quantity
FROM products AS p
LEFT JOIN order_items AS oi ON p.product_id = oi.product_id
WHERE oi.order_item_id IS NULL
ORDER BY p.stock_quantity DESC;Logic Breakdown:
- Joins
productstoorder_itemsusingLEFT JOIN. - Products
501and502have matching line items, sooi.order_item_idcontains numeric IDs. - Products
503and504have zero sales history, yieldingNULLforoi.order_item_id. - The
WHERE oi.order_item_id IS NULLclause extracts the dormant inventory items.
Expected Output Table:
| product_id | product_name | category | price | stock_quantity |
|---|---|---|---|---|
| 503 | Retro Mechanical Keycaps | Accessories | 45.00 | 80 |
| 504 | Ergonomic Footrest | Furniture | 65.00 | 25 |
Exercise 6: Churn Detection with ON-Clause Temporal Predicates (LEFT JOIN)
Business Scenario: Retention managers need a list of active enterprise accounts that have logged zero user events over the past 30 days (from cutoff date 2024-04-30).
Input Tables:
accounts (sample rows):
| account_id | company_name | plan_type |
|---|---|---|
| 1 | Acme Logistics | Enterprise |
| 2 | Globex Industrial | Enterprise |
| 3 | Initech Software | Enterprise |
usage_events (sample rows):
| event_id | account_id | event_name | event_date |
|---|---|---|---|
| 901 | 1 | export_report | 2024-04-18 |
| 902 | 2 | api_sync | 2024-02-10 |
| 903 | 1 | dashboard_view | 2024-04-29 |
PostgreSQL Solution Query:
SELECT
a.account_id,
a.company_name,
a.plan_type
FROM accounts AS a
LEFT JOIN usage_events AS e
ON a.account_id = e.account_id
AND e.event_date >= '2024-04-01'
AND e.event_date <= '2024-04-30'
WHERE a.plan_type = 'Enterprise'
AND e.event_id IS NULL
ORDER BY a.account_id ASC;Logic Breakdown:
- The date range filter is intentionally placed inside the
ONclause. This ensures accounts with events outside the 30-day window (like Globex, whose event was in February) evaluate toNULLfor the join without getting filtered out of the left table. - Account
1has events inside April, soe.event_idis not null. - Accounts
2and3have no April events; theire.event_idis null, correctly flagging them as inactive.
Expected Output Table:
| account_id | company_name | plan_type |
|---|---|---|
| 2 | Globex Industrial | Enterprise |
| 3 | Initech Software | Enterprise |
Practice SQL Joins in Your Live Browser Sandbox
Run queries against live PostgreSQL databases with automated verification on Topfolio. Free to learn, optional ₹99 verified certificate.
Start Free SQL CourseExercise 7: Warehouse Inventory vs Central Catalog Audit (FULL OUTER JOIN)
Business Scenario: A supply chain team is reconciling physical warehouse scanner counts against the central ERP product catalog. Identify all discrepancies: products present in the catalog but missing from warehouse scans, and uncataloged barcode items found in the warehouse.
Input Tables:
central_catalog (sample rows):
| sku | product_title | catalog_price |
|---|---|---|
| SKU-100 | Bluetooth Earbuds | 49.99 |
| SKU-200 | Mechanical Keyboard | 119.00 |
| SKU-300 | 1080p Webcam | 65.00 |
warehouse_inventory (sample rows):
| sku | physical_count | bay_location |
|---|---|---|
| SKU-100 | 350 | A-12 |
| SKU-200 | 110 | B-04 |
| SKU-999 | 15 | Z-99 |
PostgreSQL Solution Query:
SELECT
COALESCE(c.sku, w.sku) AS resolved_sku,
c.product_title,
c.catalog_price,
w.physical_count,
w.bay_location,
CASE
WHEN c.sku IS NOT NULL AND w.sku IS NOT NULL THEN 'Matched / Verified'
WHEN c.sku IS NOT NULL AND w.sku IS NULL THEN 'Missing in Warehouse'
WHEN c.sku IS NULL AND w.sku IS NOT NULL THEN 'Uncataloged Inventory'
END AS audit_status
FROM central_catalog AS c
FULL OUTER JOIN warehouse_inventory AS w ON c.sku = w.sku
ORDER BY resolved_sku ASC;Logic Breakdown:
- A
FULL OUTER JOINpreserves all rows from both tables. COALESCE(c.sku, w.sku)returns the valid SKU regardless of which side matched.- The
CASE WHENstatement uses NULL checks to categorize each item into one of three audit buckets: verified, missing stock, or untracked stock.
Expected Output Table:
| resolved_sku | product_title | catalog_price | physical_count | bay_location | audit_status |
|---|---|---|---|---|---|
| SKU-100 | Bluetooth Earbuds | 49.99 | 350 | A-12 | Matched / Verified |
| SKU-200 | Mechanical Keyboard | 119.00 | 110 | B-04 | Matched / Verified |
| SKU-300 | 1080p Webcam | 65.00 | NULL | NULL | Missing in Warehouse |
| SKU-999 | NULL | NULL | 15 | Z-99 | Uncataloged Inventory |
Exercise 8: Omnichannel Transaction Ledger Reconciliation (FULL OUTER JOIN)
Business Scenario: Financial audit requires reconciling payment processor credit card authorizations against bank settlement deposits. Find all unmatched transaction references (authorized charges with no bank deposit, or bank deposits with no matching processor authorization).
Input Tables:
gateway_transactions (sample rows):
| ref_num | auth_amount | auth_time |
|---|---|---|
| TXN-101 | 150.00 | 2024-04-01 10:14:00 |
| TXN-102 | 42.50 | 2024-04-01 11:30:00 |
| TXN-103 | 310.00 | 2024-04-01 14:22:00 |
bank_settlements (sample rows):
| ref_num | settled_amount | deposit_date |
|---|---|---|
| TXN-101 | 150.00 | 2024-04-02 |
| TXN-103 | 310.00 | 2024-04-02 |
| TXN-888 | 95.00 | 2024-04-02 |
PostgreSQL Solution Query:
SELECT
COALESCE(g.ref_num, b.ref_num) AS reconciliation_reference,
g.auth_amount,
b.settled_amount,
CASE
WHEN g.ref_num IS NULL THEN 'Unmatched Bank Deposit'
WHEN b.ref_num IS NULL THEN 'Unsettled Gateway Charge'
END AS discrepancy_reason
FROM gateway_transactions AS g
FULL OUTER JOIN bank_settlements AS b ON g.ref_num = b.ref_num
WHERE g.ref_num IS NULL OR b.ref_num IS NULL
ORDER BY reconciliation_reference ASC;Logic Breakdown:
- The
FULL OUTER JOINpairs matching transaction references. - The
WHERE g.ref_num IS NULL OR b.ref_num IS NULLclause strips away matched transactions (TXN-101andTXN-103), isolating only the unbalanced ledger exceptions.
Expected Output Table:
| reconciliation_reference | auth_amount | settled_amount | discrepancy_reason |
|---|---|---|---|
| TXN-102 | 42.50 | NULL | Unsettled Gateway Charge |
| TXN-888 | NULL | 95.00 | Unmatched Bank Deposit |
Exercise 9: Regional Sales Territories vs Active Sales Reps (RIGHT JOIN)
Business Scenario: Sales leadership is restructuring geographic regions. List all designated sales territories along with the name of the assigned sales representative. Include any territory that currently has no assigned rep.
Input Tables:
sales_reps (sample rows):
| rep_id | rep_name | territory_id |
|---|---|---|
| 1 | Alice Johnson | 10 |
| 2 | Bob Martinez | 20 |
| 3 | Carol Danvers | 10 |
territories (sample rows):
| territory_id | territory_name | target_quota |
|---|---|---|
| 10 | Pacific Northwest | 500000 |
| 20 | Southwest Desert | 350000 |
| 30 | Great Lakes | 450000 |
PostgreSQL Solution Query:
SELECT
t.territory_id,
t.territory_name,
t.target_quota,
COALESCE(r.rep_name, 'Unassigned Territory') AS assigned_rep
FROM sales_reps AS r
RIGHT JOIN territories AS t ON r.territory_id = t.territory_id
ORDER BY t.territory_id ASC;Logic Breakdown:
- The
RIGHT JOINguarantees that every record interritoriesis preserved in the output, even if no sales rep is assigned to that territory ID. - Territory
30(Great Lakes) has no matching row insales_reps, sor.rep_nameevaluates toNULL. COALESCEprovides a clean label for unassigned regions.- Senior Engineering Tip: Most data teams refactor
RIGHT JOINqueries toLEFT JOIN(FROM territories t LEFT JOIN sales_reps r ...) because reading queries from left to right improves code maintainability.
Expected Output Table:
| territory_id | territory_name | target_quota | assigned_rep |
|---|---|---|---|
| 10 | Pacific Northwest | 500000 | Alice Johnson |
| 10 | Pacific Northwest | 500000 | Carol Danvers |
| 20 | Southwest Desert | 350000 | Bob Martinez |
| 30 | Great Lakes | 450000 | Unassigned Territory |
Exercise 10: Complete Management Reporting Hierarchy (SELF JOIN)
Business Scenario: HR requires a company directory mapping every employee to their direct supervisor. The top executive has no manager (manager_id IS NULL) and should display 'Executive Leadership'.
Input Table:
employees (sample rows):
| employee_id | first_name | last_name | department | manager_id |
|---|---|---|---|---|
| 1 | Bruce | Wayne | Executive | NULL |
| 2 | Lucius | Fox | Engineering | 1 |
| 3 | Dick | Grayson | Operations | 1 |
| 4 | Tim | Drake | Engineering | 2 |
| 5 | Barbara | Gordon | Analytics | 2 |
PostgreSQL Solution Query:
SELECT
e.employee_id,
e.first_name || ' ' || e.last_name AS employee_name,
e.department,
COALESCE(m.first_name || ' ' || m.last_name, 'Executive Leadership') AS manager_name
FROM employees AS e
LEFT JOIN employees AS m ON e.manager_id = m.employee_id
ORDER BY e.employee_id ASC;Logic Breakdown:
- A self-join queries the same table twice using two distinct table aliases:
erepresents the individual employee, andmrepresents their respective manager. - The join condition
e.manager_id = m.employee_idlinks the subordinate's foreign key to the supervisor's primary key. - A
LEFT JOINpreserves the CEO (Bruce Wayne), whosemanager_idis null.
Expected Output Table:
| employee_id | employee_name | department | manager_name |
|---|---|---|---|
| 1 | Bruce Wayne | Executive | Executive Leadership |
| 2 | Lucius Fox | Engineering | Bruce Wayne |
| 3 | Dick Grayson | Operations | Bruce Wayne |
| 4 | Tim Drake | Engineering | Lucius Fox |
| 5 | Barbara Gordon | Analytics | Lucius Fox |
Exercise 11: Peer Salary Discrepancies within Departments (SELF JOIN)
Business Scenario: Compensation analysts are auditing salary equity. Find all instances where an employee earns strictly less than a colleague in the same department who was hired in the exact same calendar year.
Input Table:
employees (sample rows):
| employee_id | first_name | department | salary | hire_date |
|---|---|---|---|---|
| 11 | Alice | Engineering | 95000.00 | 2023-03-15 |
| 12 | Bob | Engineering | 110000.00 | 2023-08-01 |
| 13 | Charlie | Engineering | 85000.00 | 2022-01-10 |
| 14 | Diane | Analytics | 92000.00 | 2023-05-20 |
| 15 | Evan | Analytics | 98000.00 | 2023-11-12 |
PostgreSQL Solution Query:
SELECT
e.employee_id,
e.first_name AS employee_name,
e.department,
e.salary AS employee_salary,
peer.first_name AS higher_earning_peer,
peer.salary AS peer_salary,
peer.salary - e.salary AS salary_difference
FROM employees AS e
INNER JOIN employees AS peer
ON e.department = peer.department
AND EXTRACT(YEAR FROM e.hire_date) = EXTRACT(YEAR FROM peer.hire_date)
AND e.salary < peer.salary
ORDER BY e.department, salary_difference DESC;Logic Breakdown:
- Joins
employeesto itself using multiple predicates: matching department, matching hire year viaEXTRACT(YEAR FROM ...), and a non-equi conditione.salary < peer.salary. - Alice and Bob were both hired in 2023 in Engineering; because Alice earns less than Bob, a row is generated showing the $15,000 difference.
- Charlie was hired in 2022, so he has no peers from his cohort in this dataset.
Expected Output Table:
| employee_id | employee_name | department | employee_salary | higher_earning_peer | peer_salary | salary_difference |
|---|---|---|---|---|---|---|
| 11 | Alice | Engineering | 95000.00 | Bob | 110000.00 | 15000.00 |
| 14 | Diane | Analytics | 92000.00 | Evan | 98000.00 | 6000.00 |
Exercise 12: Consecutive Day User Activity Detection (SELF JOIN)
Business Scenario: Gaming analytics tracks user retention streaks. Identify all users who logged into the platform on two consecutive calendar days, without relying on window functions.
Input Table:
user_logins (sample rows):
| login_id | user_id | login_date |
|---|---|---|
| 1 | 501 | 2024-04-10 |
| 2 | 501 | 2024-04-11 |
| 3 | 501 | 2024-04-15 |
| 4 | 502 | 2024-04-10 |
| 5 | 502 | 2024-04-12 |
PostgreSQL Solution Query:
SELECT DISTINCT
curr.user_id,
curr.login_date AS first_day,
next_day.login_date AS consecutive_day
FROM user_logins AS curr
INNER JOIN user_logins AS next_day
ON curr.user_id = next_day.user_id
AND next_day.login_date = curr.login_date + INTERVAL '1 day'
ORDER BY curr.user_id, first_day ASC;Logic Breakdown:
- Joins
user_loginsto itself on matchinguser_id. - The temporal join predicate
next_day.login_date = curr.login_date + INTERVAL '1 day'tests for records on the following calendar day. - User
501logged in on both April 10 and 11, satisfying the join condition. User502logged in on April 10 and 12 (a two-day gap), so no row is returned.
Expected Output Table:
| user_id | first_day | consecutive_day |
|---|---|---|
| 501 | 2024-04-10 | 2024-04-11 |
Exercise 13: Multi-Table Revenue Aggregation without Fan-Out (Advanced CTE Join)
Business Scenario: Executive reporting requires customer order counts alongside their total units purchased. Avoid the join fan-out trap that inflates parent order totals when joining child line-item records.
Input Tables:
customers (sample rows):
| customer_id | customer_name |
|---|---|
| 1001 | Rachel Green |
| 1002 | Ross Geller |
orders (sample rows):
| order_id | customer_id | order_total |
|---|---|---|
| 201 | 1001 | 100.00 |
| 202 | 1001 | 150.00 |
| 203 | 1002 | 80.00 |
order_items (sample rows):
| item_id | order_id | quantity |
|---|---|---|
| 1 | 201 | 2 |
| 2 | 201 | 3 |
| 3 | 202 | 1 |
| 4 | 203 | 4 |
PostgreSQL Solution Query:
WITH customer_orders AS (
SELECT
customer_id,
COUNT(order_id) AS total_orders,
SUM(order_total) AS true_order_revenue
FROM orders
GROUP BY customer_id
),
customer_items AS (
SELECT
o.customer_id,
SUM(oi.quantity) AS total_units_purchased
FROM orders AS o
INNER JOIN order_items AS oi ON o.order_id = oi.order_id
GROUP BY o.customer_id
)
SELECT
c.customer_id,
c.customer_name,
COALESCE(co.total_orders, 0) AS total_orders,
COALESCE(co.true_order_revenue, 0.00) AS true_order_revenue,
COALESCE(ci.total_units_purchased, 0) AS total_units_purchased
FROM customers AS c
LEFT JOIN customer_orders AS co ON c.customer_id = co.customer_id
LEFT JOIN customer_items AS ci ON c.customer_id = ci.customer_id
ORDER BY true_order_revenue DESC;Logic Breakdown:
- In naive queries that join
ordersdirectly toorder_items, order201is duplicated across its two child rows, multiplyingorder_totalfrom $100.00 to $200.00! - Pre-aggregating orders and line items into separate Common Table Expressions (CTEs) before joining them back to the customer preserves data grains and prevents inflation.
- For a deep dive into this interview pattern, read our dedicated guide on SQL JOIN Fan-Out: Fixing Duplicate Rows & Broken SUM.
Expected Output Table:
| customer_id | customer_name | total_orders | true_order_revenue | total_units_purchased |
|---|---|---|---|---|
| 1001 | Rachel Green | 2 | 250.00 | 6 |
| 1002 | Ross Geller | 1 | 80.00 | 4 |
Exercise 14: Promotional Pricing Discount Range Matching (Non-Equi BETWEEN Join)
Business Scenario: The marketing operations team launched several targeted discount campaigns. Re-evaluate customer purchases by joining orders to the promotions table to find the applicable discount percentage based on order date and product category.
Input Tables:
orders (sample rows):
| order_id | customer_id | category | order_date | gross_amount |
|---|---|---|---|---|
| 1 | 701 | Audio | 2024-03-15 | 200.00 |
| 2 | 702 | Audio | 2024-04-10 | 150.00 |
| 3 | 703 | Displays | 2024-03-20 | 400.00 |
promotions (sample rows):
| promo_id | promo_name | category | start_date | end_date | discount_pct |
|---|---|---|---|---|---|
| P-1 | Spring Audio Fest | Audio | 2024-03-01 | 2024-03-31 | 15.00 |
| P-2 | Spring Display Blowout | Displays | 2024-03-15 | 2024-03-25 | 10.00 |
PostgreSQL Solution Query:
SELECT
o.order_id,
o.category,
o.order_date,
o.gross_amount,
COALESCE(p.promo_name, 'No Promotion Applied') AS promotion_applied,
COALESCE(p.discount_pct, 0.00) AS discount_percentage,
ROUND(
o.gross_amount * (1.0 - COALESCE(p.discount_pct, 0.00) / 100.0),
2
) AS net_discounted_amount
FROM orders AS o
LEFT JOIN promotions AS p
ON o.category = p.category
AND o.order_date BETWEEN p.start_date AND p.end_date
ORDER BY o.order_id ASC;Logic Breakdown:
- This is a non-equi join: the join condition uses
BETWEEN p.start_date AND p.end_datealongside category equality. - Order
1falls within the Spring Audio Fest window (March 15) and receives a 15% discount. - Order
2is in the Audio category, but was placed in April (after the promo ended), so it receives no discount. COALESCEhandles orders without matching promotions by falling back to 0% discount.
Expected Output Table:
| order_id | category | order_date | gross_amount | promotion_applied | discount_percentage | net_discounted_amount |
|---|---|---|---|---|---|---|
| 1 | Audio | 2024-03-15 | 200.00 | Spring Audio Fest | 15.00 | 170.00 |
| 2 | Audio | 2024-04-10 | 150.00 | No Promotion Applied | 0.00 | 150.00 |
| 3 | Displays | 2024-03-20 | 400.00 | Spring Display Blowout | 10.00 | 360.00 |
Exercise 15: Zero-Filled Sales Calendar Spine (CROSS JOIN + LEFT JOIN)
Business Scenario: Executive dashboards require a daily sales trend for the launch week of April 1–4, 2024. If a category had zero sales on a given day, it must display $0.00 rather than disappearing from the report.
Input Tables:
calendar_days (generated via PostgreSQL generate_series):
| report_date |
|---|
| 2024-04-01 |
| 2024-04-02 |
| 2024-04-03 |
| 2024-04-04 |
categories (sample rows):
| category_name |
|---|
| Electronics |
| Books |
orders (sample rows):
| order_id | category | order_date | amount |
|---|---|---|---|
| 10 | Electronics | 2024-04-01 | 500.00 |
| 11 | Electronics | 2024-04-03 | 250.00 |
| 12 | Books | 2024-04-01 | 40.00 |
PostgreSQL Solution Query:
WITH date_spine AS (
SELECT generate_series(
'2024-04-01'::DATE,
'2024-04-04'::DATE,
INTERVAL '1 day'
)::DATE AS report_date
),
category_list AS (
SELECT DISTINCT category AS category_name FROM orders
),
reporting_matrix AS (
-- CROSS JOIN builds every date × category combination
SELECT
d.report_date,
c.category_name
FROM date_spine AS d
CROSS JOIN category_list AS c
)
SELECT
m.report_date,
m.category_name,
COALESCE(SUM(o.amount), 0.00) AS daily_revenue
FROM reporting_matrix AS m
LEFT JOIN orders AS o
ON m.report_date = o.order_date
AND m.category_name = o.category
GROUP BY m.report_date, m.category_name
ORDER BY m.category_name, m.report_date ASC;Logic Breakdown:
- Real-world transactional tables omit rows for days with no activity. Joining raw orders directly would skip those dates entirely.
- A
CROSS JOINbetween a date spine and a category list generates a baseline grid containing every combination (4 days × 2 categories = 8 rows). - A
LEFT JOINfrom this reporting matrix toordersattaches transaction totals where they exist. COALESCE(SUM(o.amount), 0.00)replacesNULLvalues with$0.00for zero-sales days, producing a complete time series ready for visualization.
Expected Output Table:
| report_date | category_name | daily_revenue |
|---|---|---|
| 2024-04-01 | Books | 40.00 |
| 2024-04-02 | Books | 0.00 |
| 2024-04-03 | Books | 0.00 |
| 2024-04-04 | Books | 0.00 |
| 2024-04-01 | Electronics | 500.00 |
| 2024-04-02 | Electronics | 0.00 |
| 2024-04-03 | Electronics | 250.00 |
| 2024-04-04 | Electronics | 0.00 |
4 Common SQL Join Interview Traps
During technical interviews, hiring teams evaluate whether you understand edge cases and performance trade-offs:
- The Cartesian Explosion (Missing Join Condition): Omitting the
ONclause or joining on non-unique keys without sufficient constraints causes a full Cartesian product ($N \times M$ rows), exhausting database memory. - Accidental Inner Joins via WHERE Predicates: As covered in Part 1, filtering nullable right-table columns in the
WHEREclause strips out unmatched left rows, defeating the purpose of aLEFT JOIN. - Double-Counting Aggregate Fan-Out: Summing parent amounts across a one-to-many join multiplies values by the number of matching child rows. Always pre-aggregate in CTEs first.
- Three-Valued Logic in Anti-Joins: When using
NOT IN (SELECT id FROM ...)with subqueries, if the subquery returns a singleNULL, the entire predicate evaluates toUNKNOWNand returns zero records! UseNOT EXISTSorLEFT JOIN ... WHERE right.id IS NULLinstead.
Master Advanced SQL Joins with Topfolio
True SQL proficiency comes from diagnosing query errors, reviewing query plans, and practicing joins against live relational schemas.
Continue your journey with Topfolio's guided curricula:
- SQL Basics Course: Master table creation, filtering, aggregations, and joins.
- Advanced SQL Analytics Course: Master window functions, CTE recursion, and query plan optimization.
- Free SQL Course Sandbox: Solve live challenges directly in your browser with automated grading.
Every lesson and practice exercise on Topfolio is 100% free to learn. You can also earn an optional ₹99 verified certificate to showcase your technical SQL capabilities to hiring managers on LinkedIn.
Practice SQL Joins in Your Live Browser Sandbox
Run queries against live PostgreSQL databases with automated verification on Topfolio. Free to learn, optional ₹99 verified certificate.
Start Free SQL CourseFrequently Asked Questions
What is the practical difference between an INNER JOIN and a LEFT JOIN?
An INNER JOIN returns only records where the join key matches in both tables, discarding non-matching rows. A LEFT JOIN returns all records from the left table regardless of matches, populating right-table columns with NULL when no matching key exists.
Why does a LEFT JOIN return duplicate rows when joining two tables?
A LEFT JOIN produces duplicate rows when the right table contains multiple matching records for a single key from the left table (a one-to-many relationship). This is known as join fan-out and can be resolved by pre-aggregating child records before joining.
How do anti-joins work in SQL to find unmatched records?
An anti-join uses a LEFT JOIN combined with a WHERE clause filtering on a non-nullable column from the right table (e.g., WHERE right_table.id IS NULL). This eliminates all matched records and returns only unmatched rows from the left table.
Why should you avoid putting right-table filters in the WHERE clause of a LEFT JOIN?
Placing right-table filter predicates in the WHERE clause instead of the ON clause inadvertently converts the LEFT JOIN into an INNER JOIN, because NULL values generated for unmatched rows fail the WHERE evaluation and get filtered out.
When should you use a FULL OUTER JOIN instead of an INNER or LEFT JOIN?
Use a FULL OUTER JOIN when you need a bidirectional reconciliation between two datasets where records can exist in either table without a corresponding match in the other, such as balancing financial ledgers or auditing warehouse physical inventory against ERP records.
How can I practice SQL joins on real databases for free?
Topfolio provides an interactive browser-based SQL sandbox where you can practice real-world join queries against PostgreSQL databases with instant automated validation. All learning exercises are 100% free, with an optional verified certificate for ₹99.

Written by
Founder at Topfolio with 6+ years in data & analytics across JPMC, Ultrahuman, and high-growth startups. Sat on hiring panels, reviewed 500+ resumes, and writes practical SQL & data guides.
Related Articles
SQL Practice Questions: 25 Real Business Queries & Answers
Practice 25 real-world SQL queries with solutions, schema diagrams, and expected outputs. Master joins, window functions, and aggregations for interviews.
SQL Interview Questions for Data Analyst (2026 Guide)
Master 2026 SQL interview questions for data analysts. Real queries, window functions, joins, common traps, and runnable code solutions.
Data Analyst Interview Questions 2026: Complete Preparation Guide
30+ real data analyst interview questions with schemas, solutions & pitfalls — SQL OAs vs live technical rounds, Python, modern data stack, product cases & behavioral.