SQL Joins

By the end of this lesson, you will be able to combine data from multiple tables using SQL JOINs to answer questions that require information from more than one source.

What it is

A SQL Join is a clause used in queries to combine rows from two or more tables based on a related column between them. Think of it as matching puzzle pieces: if Table A has customer IDs and Table B has order details with those same IDs, a join connects the specific orders to the specific customers.

The most common types are:

• INNER JOIN: Returns records that have matching values in both tables.
• LEFT JOIN: Returns all records from the left table, and the matched records from the right table (NULL if no match).
• RIGHT JOIN: The opposite of LEFT JOIN.
• FULL OUTER JOIN: Returns all records when there is a match in either left or right table.

Why it matters

• Data Normalization: Databases store data in separate tables to reduce redundancy. Joins allow you to reconstruct the full picture for analysis.
• Contextual Analysis: You can analyze sales performance by joining transaction data with product category data.
• Customer Insights: Combine demographic data with behavioral logs to segment users effectively.
• Referential Integrity Checks: Identify orphaned records (e.g., orders without valid customer IDs) using outer joins.

Syntax or steps

The basic structure requires specifying the tables, the type of join, and the condition linking them via the ON keyword.

SELECT columns
FROM table1
JOIN_TYPE table2
ON table1.common_column = table2.common_column;

Example

Imagine we have two tables: customers and orders. We want to list every customer's name alongside their total order amount. Note that some customers may not have placed any orders yet.

-- Table: customers (customer_id, name)
-- Table: orders (order_id, customer_id, amount)

SELECT 
    c.name AS customer_name,
    o.amount AS order_amount
FROM 
    customers c
LEFT JOIN 
    orders o ON c.customer_id = o.customer_id;

Explanation:

• SELECT c.name, o.amount: Specifies which columns to retrieve.
• FROM customers c: Identifies the primary table and assigns an alias c.
• LEFT JOIN orders o: Combines with the orders table (aliased as o). Using LEFT JOIN ensures customers with zero orders still appear in the result.
• ON c.customer_id = o.customer_id: Defines the relationship key. Rows are matched where these IDs are equal.

Common mistakes

• Cartesian Products: Forgetting the ON clause results in every row of Table A combining with every row of Table B, creating massive, incorrect datasets.
• Wrong Join Type: Using INNER JOIN when you need all records from one side (like listing all employees even if they haven't completed a project). Use LEFT JOIN instead.
• Ambiguous Column Names: If both tables have a column named id, referencing just id causes an error. Always use aliases like t1.id.
• Performance Issues: Joining on unindexed columns slows down queries significantly. Ensure foreign keys and join columns are indexed.

When to use it

Choose the join type based on whether you need strict matches or inclusive lists.

Practice

Guided Exercise: Write a query to select the product name from a products table and the quantity sold from a sales table, ensuring only products that have been sold at least once are shown.

Hint: Use INNER JOIN on product_id.

Challenge: Modify the previous query to show ALL products, including those with zero sales. What changes do you make?

Solution Hint: Change INNER JOIN to LEFT JOIN starting from the products table.

Quick check

Question: If Table A has 5 rows and Table B has 3 rows, and you perform an INNER JOIN where only 2 rows match, how many rows will the result set contain?

Answer: 2 rows. An inner join only returns matching pairs.

Summary

SQL Joins are essential for retrieving relational data stored across normalized tables. Mastering the difference between INNER and LEFT joins allows you to control exactly which records appear in your final dataset, preventing data loss or duplication during analysis.