Joins - SQL 50 Study Guide 🔗

Master table relationships and data combination through 9 carefully selected join problems from LeetCode’s SQL 50.

Table of Contents

1. Replace Employee ID With The Unique Identifier
2. Product Sales Analysis I
3. Customer Who Visited but Did Not Make Any Transactions
4. Rising Temperature
5. Average Time of Process per Machine
6. Employee Bonus
7. Students and Examinations
8. Managers with at Least 5 Direct Reports
9. Confirmation Rate

1. Replace Employee ID With The Unique Identifier (LC 1378)

🔗 LeetCode Link: Replace Employee ID With The Unique Identifier - LeetCode #1378

📊 Schema First (Active Analysis)

Before writing any SQL, spend 2-3 minutes analyzing the table structure:

Schema Analysis Questions:

1. What is the relationship between Employees and EmployeeUNI tables?
2. Which employees might not have unique identifiers?
3. What type of join preserves all employee records?

Take time to understand the data model before continuing…

💡 Query Construction (Guided Learning)

Step 1: Understanding the Join Requirement

Guided Question: What happens to employees who don’t have a unique_id in the EmployeeUNI table?

Step 2: Choosing the Right Join Type

Guided Question: Which join type ensures all employees appear in the result, even without unique identifiers?

Step 3: Handling NULL Values

Guided Question: How should NULL unique_id values be presented in the result?

Solutions

Approach 1: Basic LEFT JOIN

-- Show all employees with their unique identifiers
SELECT 
    eu.unique_id,
    e.name
FROM Employees e
LEFT JOIN EmployeeUNI eu ON e.id = eu.id;

Explanation:

• LEFT JOIN preserves all records from Employees table
• ON e.id = eu.id matches employees with their unique identifiers
• Employees without unique_id will have NULL in unique_id column
• Result shows unique_id first, then name as typically requested

Approach 2: With Explicit NULL Handling

-- Show all employees with explicit NULL handling
SELECT 
    COALESCE(eu.unique_id, NULL) AS unique_id,
    e.name
FROM Employees e
LEFT JOIN EmployeeUNI eu ON e.id = eu.id
ORDER BY e.name;

Key Differences:

• COALESCE() makes NULL handling explicit (though redundant here)
• ORDER BY e.name provides consistent result ordering
• Same logical result as Approach 1

🎯 Query Practice & Self-Assessment

Implementation Challenge

Try writing the query from memory:

Step-by-step checklist:

• Start with FROM clause identifying main table (Employees)
• Add LEFT JOIN to preserve all employee records
• Use correct ON condition to match employee IDs
• SELECT unique_id first, then name
• Verify that employees without unique_id show NULL

Schema Variations

Practice adapting your solution:

• What if you only wanted employees WITH unique identifiers?
• How would you count employees without unique identifiers?
• What if multiple employees could share the same unique_id?

Reflection Questions

After solving, think about:

1. Join Logic: Why LEFT JOIN instead of INNER JOIN?
2. Data Integrity: What does it mean when an employee has no unique_id?
3. Alternative Approaches: Could you solve this with EXISTS/NOT EXISTS?
4. Pattern Recognition: When else would you use LEFT JOIN?

Confidence Rating

Rate your confidence (1-5) on:

• Understanding LEFT JOIN mechanics: ___/5
• Choosing appropriate join types: ___/5
• Handling NULL values in joins: ___/5
• Reading join requirements from problem description: ___/5

2. Product Sales Analysis I (LC 1068)

🔗 LeetCode Link: Product Sales Analysis I - LeetCode #1068

📊 Schema First (Active Analysis)

Schema Analysis Questions:

1. How are sales transactions related to product information?
2. What information from each table is needed in the result?
3. Would any sales exist without corresponding products?

💡 Query Construction (Guided Learning)

Step 1: Identifying Required Information

Guided Question: What columns do we need from each table to show sales with product names?

Step 2: Choosing Join Strategy

Guided Question: Should we use INNER JOIN or LEFT JOIN for sales and products?

Solutions

Approach 1: Basic INNER JOIN

-- Get sales information with product names
SELECT 
    p.product_name,
    s.year,
    s.price
FROM Sales s
INNER JOIN Product p ON s.product_id = p.product_id;

Explanation:

• INNER JOIN connects sales with product information
• Only sales with valid products appear in results
• Result shows product_name, year, and price as typically requested

Approach 2: With Additional Sales Details

-- Include more comprehensive sales information
SELECT 
    s.sale_id,
    p.product_name,
    s.year,
    s.quantity,
    s.price,
    (s.quantity * s.price) AS total_amount
FROM Sales s
INNER JOIN Product p ON s.product_id = p.product_id
ORDER BY s.year, p.product_name;

🎯 Query Practice & Self-Assessment

Implementation Challenge

Step-by-step checklist:

• Identify the linking column between tables (product_id)
• Use INNER JOIN for sales with valid products
• SELECT required columns from both tables
• Consider whether calculated columns add value
• Add sorting for better result presentation

3. Customer Who Visited but Did Not Make Any Transactions (LC 1581)

🔗 LeetCode Link: Customer Who Visited but Did Not Make Any Transactions - LeetCode #1581

📊 Schema First (Active Analysis)

Schema Analysis Questions:

1. How do visits relate to transactions?
2. What constitutes a visit without a transaction?
3. How do you identify missing relationships between tables?

💡 Query Construction (Guided Learning)

Step 1: Understanding “No Transactions”

Guided Question: How do you find visits that don’t have corresponding transactions?

Step 2: Counting Visit Frequency

Guided Question: The problem asks for count of visits per customer - what does this mean?

Solutions

Approach 1: LEFT JOIN with NULL Check

-- Find customers who visited but never made transactions
SELECT 
    v.customer_id,
    COUNT(v.visit_id) AS count_no_trans
FROM Visits v
LEFT JOIN Transactions t ON v.visit_id = t.visit_id
WHERE t.visit_id IS NULL
GROUP BY v.customer_id;

Explanation:

• LEFT JOIN connects all visits with their transactions (if any)
• WHERE t.visit_id IS NULL filters to visits without transactions
• GROUP BY customer_id aggregates by customer
• COUNT(v.visit_id) counts visits per customer with no transactions

Approach 2: Using NOT EXISTS

-- Alternative approach using NOT EXISTS
SELECT 
    customer_id,
    COUNT(*) AS count_no_trans
FROM Visits v
WHERE NOT EXISTS (
    SELECT 1 
    FROM Transactions t 
    WHERE t.visit_id = v.visit_id
)
GROUP BY customer_id;

Trade-offs:

• LEFT JOIN: More explicit, easier to extend with transaction details
• NOT EXISTS: More direct logic, potentially better performance

🎯 Query Practice & Self-Assessment

Implementation Challenge

Step-by-step checklist:

• Use LEFT JOIN or NOT EXISTS to find missing relationships
• Filter to visits without transactions
• Group by customer_id for aggregation
• Count visits per customer appropriately
• Verify results make business sense

4. Rising Temperature (LC 197)

🔗 LeetCode Link: Rising Temperature - LeetCode #197

📊 Schema First (Active Analysis)

Schema Analysis Questions:

1. How do you compare today’s temperature with yesterday’s?
2. What constitutes a “rising” temperature?
3. How do you handle date arithmetic in joins?

💡 Query Construction (Guided Learning)

Step 1: Self-Join Strategy

Guided Question: How do you compare a row with the previous day’s row in the same table?

Step 2: Date Arithmetic

Guided Question: What’s the best way to identify consecutive days?

Solutions

Approach 1: Self-Join with DATE_ADD

-- Find days with higher temperature than previous day
SELECT w1.id
FROM Weather w1
INNER JOIN Weather w2 
    ON w1.recordDate = DATE_ADD(w2.recordDate, INTERVAL 1 DAY)
WHERE w1.temperature > w2.temperature;

Explanation:

• Self-join creates pairs of consecutive days
• DATE_ADD(w2.recordDate, INTERVAL 1 DAY) finds next day
• w1.temperature > w2.temperature filters for rising temperature
• Returns ID of days with higher temperature than previous day

Approach 2: Using DATEDIFF

-- Alternative using DATEDIFF function
SELECT w1.id
FROM Weather w1
INNER JOIN Weather w2 
    ON DATEDIFF(w1.recordDate, w2.recordDate) = 1
WHERE w1.temperature > w2.temperature;

🎯 Query Practice & Self-Assessment

Implementation Challenge

Step-by-step checklist:

• Set up self-join with appropriate aliases
• Create join condition for consecutive dates
• Add WHERE clause for temperature comparison
• Select the correct ID column
• Test with sample data to verify logic

5. Average Time of Process per Machine (LC 1661)

🔗 LeetCode Link: Average Time of Process per Machine - LeetCode #1661

📊 Schema First (Active Analysis)

💡 Query Construction (Guided Learning)

Step 1: Understanding Process Duration

Guided Question: How do you calculate the duration of each process?

Solutions

-- Calculate average processing time per machine
SELECT 
    s.machine_id,
    ROUND(AVG(e.timestamp - s.timestamp), 3) AS processing_time
FROM Activity s
INNER JOIN Activity e 
    ON s.machine_id = e.machine_id 
    AND s.process_id = e.process_id
    AND s.activity_type = 'start'
    AND e.activity_type = 'end'
GROUP BY s.machine_id;

Explanation:

• Self-join matches start and end records for same machine/process
• e.timestamp - s.timestamp calculates process duration
• AVG() gets average duration per machine
• ROUND(..., 3) formats to 3 decimal places

6. Employee Bonus (LC 577)

🔗 LeetCode Link: Employee Bonus - LeetCode #577

Solutions

-- Find employees with bonus less than 1000 or no bonus
SELECT 
    e.name,
    b.bonus
FROM Employee e
LEFT JOIN Bonus b ON e.empId = b.empId
WHERE b.bonus < 1000 OR b.bonus IS NULL;

7. Students and Examinations (LC 1280)

🔗 LeetCode Link: Students and Examinations - LeetCode #1280

Solutions

-- Show exam attendance for all student-subject combinations
SELECT 
    s.student_id,
    s.student_name,
    sub.subject_name,
    COUNT(e.subject_name) AS attended_exams
FROM Students s
CROSS JOIN Subjects sub
LEFT JOIN Examinations e 
    ON s.student_id = e.student_id 
    AND sub.subject_name = e.subject_name
GROUP BY s.student_id, s.student_name, sub.subject_name
ORDER BY s.student_id, sub.subject_name;

Key Concepts:

• CROSS JOIN creates all possible student-subject combinations
• LEFT JOIN adds actual exam attendance
• COUNT() handles both attended and non-attended exams

8. Managers with at Least 5 Direct Reports (LC 570)

🔗 LeetCode Link: Managers with at Least 5 Direct Reports - LeetCode #570

Solutions

-- Find managers with 5+ direct reports
SELECT m.name
FROM Employee e
INNER JOIN Employee m ON e.managerId = m.id
GROUP BY m.id, m.name
HAVING COUNT(e.id) >= 5;

Key Concepts:

• Self-join to connect employees with their managers
• GROUP BY manager to count reports
• HAVING filters groups (not individual rows)

9. Confirmation Rate (LC 1934)

🔗 LeetCode Link: Confirmation Rate - LeetCode #1934

Solutions

-- Calculate confirmation rate per user
SELECT 
    s.user_id,
    ROUND(
        COALESCE(
            SUM(CASE WHEN c.action = 'confirmed' THEN 1 ELSE 0 END) * 1.0 / COUNT(c.user_id),
            0
        ), 2
    ) AS confirmation_rate
FROM Signups s
LEFT JOIN Confirmations c ON s.user_id = c.user_id
GROUP BY s.user_id;

Key Concepts:

• LEFT JOIN includes users with no confirmation attempts
• CASE WHEN for conditional counting
• COALESCE handles users with no confirmations

📚 Key Concepts Summary

Join Types Mastered

• INNER JOIN - Only matching records from both tables
• LEFT JOIN - All records from left table + matches from right
• RIGHT JOIN - All records from right table + matches from left
• FULL OUTER JOIN - All records from both tables
• CROSS JOIN - Cartesian product of all combinations
• Self-Join - Table joined with itself

Join Patterns

1. Basic Relationship: table1 JOIN table2 ON table1.id = table2.foreign_id
2. Missing Records: LEFT JOIN ... WHERE right_table.id IS NULL
3. Self-Comparison: table1 t1 JOIN table1 t2 ON t1.date = t2.date + 1
4. Date Range: ON date BETWEEN start_date AND end_date

Best Practices

• Use table aliases for readability (e for Employee, m for Manager)
• Be explicit about join types (don’t rely on implicit INNER JOIN)
• Consider NULL handling in LEFT/RIGHT joins
• Use appropriate join types based on business requirements
• Test edge cases (missing relationships, duplicate keys)

Next Steps

Master advanced querying techniques in Subqueries Guide.