Joins and Relationships

Introduction to Table Relationships

One of the core strengths of relational databases is their ability to model relationships between different entities. Instead of storing all data in one massive table, we organize related data across multiple tables and establish connections between them.

Types of Relationships

In relational database design, there are three main types of relationships:

• One-to-One (1:1): Each record in the first table corresponds to exactly one record in the second table, and vice versa. For example, a person and their passport.
• One-to-Many (1:N): Each record in the first table can have multiple related records in the second table, but each record in the second table is related to only one record in the first table. For example, a customer and their orders.
• Many-to-Many (N:M): Each record in the first table can have many related records in the second table, and vice versa. For example, students and courses. This requires a junction table (also called a bridge or linking table).

Understanding SQL JOINs

In SQL, JOIN operations allow you to combine rows from two or more tables based on a related column. This is how we retrieve data that spans multiple tables.

The Concept of Joining Tables

When joining tables, the database engine:

1. Examines rows from both tables
2. Matches them according to the join condition (usually equality between related columns)
3. Creates a new result set containing columns from both tables for matched rows

Types of SQL JOINs

INNER JOIN: Matching Rows Only

The INNER JOIN is the most common type of join. It returns only rows where there is a match in both tables based on the join condition.

SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;
          

INNER JOIN Example

-- Retrieve customer information along with their order details
SELECT 
    c.customer_id,
    c.name,
    c.email,
    o.order_id,
    o.order_date,
    o.amount
FROM 
    customers c
INNER JOIN 
    orders o ON c.customer_id = o.customer_id
ORDER BY 
    c.customer_id, o.order_date;
          

LEFT JOIN: Including All Rows from the Left Table

The LEFT JOIN (also called LEFT OUTER JOIN) returns all rows from the left table and matching rows from the right table. If there's no match, NULL values are returned for the right table's columns.

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;
          

LEFT JOIN Example

-- Retrieve all customers and their order details (if any)
SELECT 
    c.customer_id,
    c.name,
    c.email,
    o.order_id,
    o.order_date,
    o.amount
FROM 
    customers c
LEFT JOIN 
    orders o ON c.customer_id = o.customer_id
ORDER BY 
    c.customer_id, o.order_date;
          

Common Use Cases for LEFT JOIN

• Finding "orphaned" records (e.g., customers with no orders)
• Generating reports that must include all entries from a master table, even when related data is missing
• Computing aggregates over all records, counting zeros for missing relationships

-- Identify customers who haven't placed any orders
SELECT 
    c.customer_id,
    c.name,
    c.email
FROM 
    customers c
LEFT JOIN 
    orders o ON c.customer_id = o.customer_id
WHERE 
    o.order_id IS NULL;
          

RIGHT JOIN: Including All Rows from the Right Table

The RIGHT JOIN (also called RIGHT OUTER JOIN) is the mirror image of LEFT JOIN. It returns all rows from the right table and matching rows from the left table. If there's no match, NULL values are returned for the left table's columns.

SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;
          

-- Show all orders and their customer details (if available)
SELECT 
    o.order_id,
    o.order_date,
    o.amount,
    c.customer_id,
    c.name,
    c.email
FROM 
    customers c
RIGHT JOIN 
    orders o ON c.customer_id = o.customer_id
ORDER BY 
    o.order_id;
          

This example would include all orders, even if the customer information was missing (though in our sample data, all orders have associated customers).

FULL OUTER JOIN: Including All Rows from Both Tables

The FULL OUTER JOIN returns all rows from both tables, matching them where possible. If there's no match, NULL values are returned for the columns of the table without a match.

SELECT columns
FROM table1
FULL OUTER JOIN table2 ON table1.column = table2.column;
          

-- Retrieve all customers and all orders, matching where possible
SELECT 
    c.customer_id,
    c.name,
    o.order_id,
    o.order_date,
    o.amount
FROM 
    customers c
FULL OUTER JOIN 
    orders o ON c.customer_id = o.customer_id
ORDER BY 
    c.customer_id, o.order_id;
          

-- MySQL equivalent (since MySQL doesn't support FULL OUTER JOIN directly)
SELECT 
    c.customer_id,
    c.name,
    o.order_id,
    o.order_date,
    o.amount
FROM 
    customers c
LEFT JOIN 
    orders o ON c.customer_id = o.customer_id

UNION

SELECT 
    c.customer_id,
    c.name,
    o.order_id,
    o.order_date,
    o.amount
FROM 
    customers c
RIGHT JOIN 
    orders o ON c.customer_id = o.customer_id
WHERE 
    c.customer_id IS NULL

ORDER BY 
    customer_id, order_id;
          

CROSS JOIN: All Possible Combinations

A CROSS JOIN returns the Cartesian product of two tables - all possible combinations of rows. There is no matching condition.

SELECT columns
FROM table1
CROSS JOIN table2;

-- Equivalent syntax:
SELECT columns
FROM table1, table2;
          

CROSS JOIN Example

Let's create a simple products and sizes table to demonstrate CROSS JOIN:

-- Create all possible product and size combinations
SELECT 
    p.product_id,
    p.product_name,
    s.size_id,
    s.size_name
FROM 
    products p
CROSS JOIN 
    sizes s
ORDER BY 
    p.product_id, s.size_id;
          

Common Use Cases for CROSS JOIN

• Generating all possible combinations (e.g., product variations)
• Creating a calendar or date dimension table
• Applying a calculation to every combination of parameters

SELF JOIN: Joining a Table to Itself

A SELF JOIN is not a separate type of join, but rather a scenario where a table is joined to itself using a different alias. This is useful for querying hierarchical data or finding relationships within the same table.

Employee Hierarchy Example

Consider an employees table with a self-referencing manager relationship:

-- Show employees along with their manager's name
SELECT 
    e.employee_id,
    e.name AS employee_name,
    e.title AS employee_title,
    m.employee_id AS manager_id,
    m.name AS manager_name,
    m.title AS manager_title
FROM 
    employees e
LEFT JOIN 
    employees m ON e.manager_id = m.employee_id
ORDER BY 
    e.employee_id;
          

Other SELF JOIN Use Cases

• Finding pairs of records that match certain criteria (e.g., customers in the same city)
• Comparing records within the same table
• Traversing hierarchical data (e.g., organization charts, category trees)

Joining Multiple Tables

Real-world queries often require joining more than two tables to combine data from across the database schema.

Extended E-commerce Example

Let's expand our e-commerce example to include products and order details:

-- Retrieve complete order information
SELECT 
    c.customer_id,
    c.name AS customer_name,
    o.order_id,
    o.order_date,
    p.product_id,
    p.product_name,
    od.quantity,
    od.unit_price,
    (od.quantity * od.unit_price) AS line_total
FROM 
    customers c
INNER JOIN 
    orders o ON c.customer_id = o.customer_id
INNER JOIN 
    order_details od ON o.order_id = od.order_id
INNER JOIN 
    products p ON od.product_id = p.product_id
ORDER BY 
    o.order_id, p.product_id;
          

Advanced Join Techniques

Joining with Additional Conditions

You can add additional conditions to JOIN clauses beyond the matching columns.

-- Find large orders (over $100) and their customers
SELECT 
    c.name AS customer_name,
    o.order_id,
    o.amount
FROM 
    customers c
INNER JOIN 
    orders o ON c.customer_id = o.customer_id AND o.amount > 100
ORDER BY 
    o.amount DESC;
          

Using Non-Equality Joins

Joins aren't limited to equality conditions. You can use any comparison operator.

-- Match products to price categories
SELECT 
    p.product_id,
    p.product_name,
    p.list_price,
    pc.category_name
FROM 
    products p
INNER JOIN 
    price_categories pc ON p.list_price BETWEEN pc.min_price AND pc.max_price
ORDER BY 
    p.list_price;
          

Using Subqueries in Joins

You can use subqueries to create dynamic tables to join against.

-- Find customers who have spent above average
SELECT 
    c.customer_id,
    c.name,
    total_spent
FROM 
    customers c
INNER JOIN (
    SELECT 
        customer_id, 
        SUM(amount) AS total_spent
    FROM 
        orders
    GROUP BY 
        customer_id
) o ON c.customer_id = o.customer_id
WHERE 
    o.total_spent > (SELECT AVG(amount) FROM orders)
ORDER BY 
    o.total_spent DESC;
          

Common Join Problems and Solutions

Duplicate Rows

One of the most common issues with JOINs is unexpected duplicate rows in results.

-- Get customer order summary without duplicates
SELECT 
    c.customer_id,
    c.name,
    COUNT(o.order_id) AS order_count,
    SUM(o.amount) AS total_spent,
    MAX(o.order_date) AS most_recent_order
FROM 
    customers c
LEFT JOIN 
    orders o ON c.customer_id = o.customer_id
GROUP BY 
    c.customer_id, c.name
ORDER BY 
    total_spent DESC;
          

Missing Rows

Another common issue is unexpectedly missing rows after a JOIN.

Practical Activities

Activity 1: Basic Joins Practice

Using a sample database (like Northwind or Sakila), write SQL queries to:

1. List all customers and their orders using INNER JOIN
2. Show all customers, including those who haven't placed orders, using LEFT JOIN
3. Find all products that haven't been ordered using an appropriate join
4. Create a comprehensive order details report joining customers, orders, and products

Activity 2: Advanced Joins Challenge

Write SQL queries to solve these more complex scenarios:

1. Find pairs of customers from the same city using a SELF JOIN
2. List employees along with their manager's name and their manager's manager (two levels of hierarchy)
3. Create a report showing each product's sales by month and year
4. Find customers who have purchased all products in a specific category

Activity 3: Real-World Database Design

Design a database schema for a movie streaming service that includes:

1. Movies with details (title, release year, genre, duration, etc.)
2. Actors and their roles in movies
3. Directors and their movies
4. User accounts and viewing history
5. Ratings and reviews

Then write SQL JOIN queries to:

1. List all movies with their directors and lead actors
2. Show each user's viewing history with movie details
3. Find movies that are highly rated by users who enjoyed a specific movie

Key Takeaways

• JOINs are essential for working with relational data spread across multiple tables
• INNER JOIN returns only matching rows from both tables
• LEFT JOIN keeps all rows from the left table, adding NULLs where there's no match
• RIGHT JOIN is the mirror image of LEFT JOIN
• FULL OUTER JOIN returns all rows from both tables, matching where possible
• CROSS JOIN produces all possible combinations between tables
• SELF JOIN allows you to relate rows within the same table
• Multiple tables can be joined in a single query, following relationship paths
• Consider performance implications when joining large tables
• Choose the appropriate JOIN type based on whether you need to preserve unmatched rows

Next Steps

In our upcoming lectures, we'll explore more advanced SQL topics including subqueries, transactions, and database optimization. We'll also look at how to apply these SQL fundamentals when working with specific database management systems.