PHP Fundamentals

SQL: Joins & Relationships

13 min Lesson 35 of 45

Understanding Table Relationships

In relational databases, data is often spread across multiple tables. JOINs allow you to combine rows from two or more tables based on related columns.

Types of Relationships

1. One-to-Many (Most Common)

One record in Table A relates to multiple records in Table B.

Example: One user can have many orders

users table: one user
orders table: many orders with user_id foreign key

2. Many-to-Many

Multiple records in Table A relate to multiple records in Table B.

Example: Students and courses (one student takes many courses, one course has many students)

Requires a junction/pivot table: student_courses
Contains student_id and course_id

3. One-to-One (Rare)

One record in Table A relates to exactly one record in Table B.

Example: User and user_profile (extended user information)

Sample Database Schema

We'll use this e-commerce schema for examples:

users (id, username, email)
  |
  +-- orders (id, user_id, total_amount, created_at)
        |
        +-- order_items (id, order_id, product_id, quantity, price)
              |
              +-- products (id, name, category_id, price)
                    |
                    +-- categories (id, name)

INNER JOIN

INNER JOIN returns only the rows that have matching values in both tables.

Syntax

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

Example: Users and Their Orders

SELECT
    users.username,
    users.email,
    orders.id AS order_id,
    orders.total_amount,
    orders.created_at
FROM users
INNER JOIN orders ON users.id = orders.user_id;

Result: Only users who have placed orders are shown.

Using Table Aliases

SELECT
    u.username,
    o.id AS order_id,
    o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.total_amount > 100
ORDER BY o.created_at DESC;

Best Practice: Use table aliases (u, o) to make queries shorter and more readable, especially with multiple joins.

LEFT JOIN (LEFT OUTER JOIN)

LEFT JOIN returns ALL rows from the left table and matching rows from the right table. If no match, NULL values are returned for right table columns.

Example: All Users, Including Those Without Orders

SELECT
    u.username,
    u.email,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username, u.email
ORDER BY total_spent DESC;

Result: All users are shown, even those with 0 orders (order_count = 0).

COALESCE Function

COALESCE(value1, value2, ...) returns the first non-NULL value.

Useful with LEFT JOIN to replace NULL with a default value:

COALESCE(SUM(o.total_amount), 0)  -- Returns 0 if SUM is NULL

Finding Records With No Match

-- Find users who have NEVER placed an order
SELECT
    u.username,
    u.email
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

RIGHT JOIN (RIGHT OUTER JOIN)

RIGHT JOIN returns ALL rows from the right table and matching rows from the left table.

-- All orders with user information (even orphaned orders)
SELECT
    o.id AS order_id,
    o.total_amount,
    u.username
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

Note: RIGHT JOIN is rarely used. You can always rewrite it as a LEFT JOIN by switching table order:

-- These are equivalent:
SELECT * FROM users u RIGHT JOIN orders o ON u.id = o.user_id;
SELECT * FROM orders o LEFT JOIN users u ON u.id = o.user_id;

Multiple Joins

You can join more than two tables in a single query.

Example: Orders with User and Product Details

SELECT
    u.username,
    o.id AS order_id,
    o.created_at,
    p.name AS product_name,
    oi.quantity,
    oi.price AS item_price,
    (oi.quantity * oi.price) AS item_total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE u.id = 10
ORDER BY o.created_at DESC;

Example: Products with Category Names

SELECT
    p.name AS product_name,
    p.price,
    c.name AS category_name
FROM products p
INNER JOIN categories c ON p.category_id = c.id
WHERE p.price > 50
ORDER BY c.name, p.name;

Self Join

A table joined with itself, useful for hierarchical data.

Example: Employee Manager Relationship

-- Table: employees (id, name, manager_id)

SELECT
    e.name AS employee_name,
    m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Example: Related Products

-- Find products in the same category
SELECT
    p1.name AS product,
    p2.name AS related_product,
    p1.category_id
FROM products p1
INNER JOIN products p2
    ON p1.category_id = p2.category_id
    AND p1.id != p2.id  -- Don't match product with itself
WHERE p1.id = 5
LIMIT 5;

CROSS JOIN (Cartesian Product)

Returns all possible combinations of rows from both tables. Rarely used.

-- Every combination of size and color
SELECT
    sizes.name AS size,
    colors.name AS color
FROM sizes
CROSS JOIN colors;

Many-to-Many Relationships

Requires a junction table to connect two tables.

Example: Products and Tags

-- Tables:
-- products (id, name)
-- tags (id, name)
-- product_tags (product_id, tag_id)

-- Get all tags for a product
SELECT
    p.name AS product_name,
    t.name AS tag_name
FROM products p
INNER JOIN product_tags pt ON p.id = pt.product_id
INNER JOIN tags t ON pt.tag_id = t.id
WHERE p.id = 15;

Example: Students and Courses

-- Get all courses for a student
SELECT
    s.name AS student_name,
    c.name AS course_name,
    sc.grade
FROM students s
INNER JOIN student_courses sc ON s.id = sc.student_id
INNER JOIN courses c ON sc.course_id = c.id
WHERE s.id = 25;

Advanced Join Techniques

Joining on Multiple Conditions

SELECT
    u.username,
    o.id AS order_id,
    o.status
FROM users u
INNER JOIN orders o
    ON u.id = o.user_id
    AND o.status = 'completed'
    AND o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);

Using Subqueries in Joins

-- Join with a derived table
SELECT
    u.username,
    order_stats.order_count,
    order_stats.total_spent
FROM users u
INNER JOIN (
    SELECT
        user_id,
        COUNT(*) AS order_count,
        SUM(total_amount) AS total_spent
    FROM orders
    GROUP BY user_id
) AS order_stats ON u.id = order_stats.user_id
WHERE order_stats.order_count > 5;

Practical Examples

Example 1: Complete Order Details

SELECT
    o.id AS order_id,
    o.created_at AS order_date,
    u.username,
    u.email,
    p.name AS product_name,
    oi.quantity,
    oi.price AS unit_price,
    (oi.quantity * oi.price) AS line_total,
    o.total_amount AS order_total
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.id = 100;

Example 2: Product Sales Report

SELECT
    p.name AS product_name,
    c.name AS category,
    COUNT(oi.id) AS times_ordered,
    SUM(oi.quantity) AS total_quantity_sold,
    SUM(oi.quantity * oi.price) AS total_revenue
FROM products p
INNER JOIN categories c ON p.category_id = c.id
LEFT JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name, c.name
ORDER BY total_revenue DESC
LIMIT 20;

Example 3: Customer Activity Report

SELECT
    u.username,
    u.email,
    u.created_at AS member_since,
    COUNT(DISTINCT o.id) AS total_orders,
    COUNT(DISTINCT oi.product_id) AS unique_products_bought,
    COALESCE(SUM(o.total_amount), 0) AS lifetime_value,
    MAX(o.created_at) AS last_order_date,
    DATEDIFF(NOW(), MAX(o.created_at)) AS days_since_last_order
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LEFT JOIN order_items oi ON o.id = oi.order_id
GROUP BY u.id, u.username, u.email, u.created_at
HAVING total_orders > 0
ORDER BY lifetime_value DESC;

Example 4: Category Performance

SELECT
    c.name AS category,
    COUNT(DISTINCT p.id) AS product_count,
    COUNT(DISTINCT o.id) AS order_count,
    SUM(oi.quantity) AS items_sold,
    SUM(oi.quantity * oi.price) AS total_revenue,
    AVG(oi.price) AS avg_item_price
FROM categories c
INNER JOIN products p ON c.id = p.category_id
LEFT JOIN order_items oi ON p.id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.id
GROUP BY c.id, c.name
ORDER BY total_revenue DESC;

Exercise: Join Practice

Using the e-commerce schema, write queries for:

List all products with their category names, showing products without categories as "Uncategorized"
Find users who have ordered products from the "Electronics" category
Show the top 5 best-selling products (by total quantity sold) with their category
List all orders placed in the last 30 days with customer username and order total
Find products that have never been ordered
Calculate average order value for each user who has placed more than 3 orders
Show products and their "frequently bought together" items (products ordered in the same orders)
Create a report showing monthly revenue per category for the last 6 months

Join Performance Tips

Optimizing Joins

Index foreign keys: Always add indexes on columns used in JOIN conditions
Use INNER JOIN when possible: Faster than OUTER JOINs
Filter early: Use WHERE before JOIN when possible
Avoid SELECT *: Specify only needed columns
Use EXPLAIN: Analyze query execution plan
Join order matters: Start with smallest table

-- Check join performance
EXPLAIN SELECT u.username, o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= '2024-01-01';

Common Join Patterns

Pattern 1: Counting Related Records

-- Count orders per user
SELECT
    u.username,
    COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

Pattern 2: Latest/First Record

-- Get each user's most recent order
SELECT
    u.username,
    o.id AS last_order_id,
    o.created_at AS last_order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.created_at = (
    SELECT MAX(o2.created_at)
    FROM orders o2
    WHERE o2.user_id = u.id
);

Pattern 3: Aggregating Across Joins

-- Total spent per category by each user
SELECT
    u.username,
    c.name AS category,
    SUM(oi.quantity * oi.price) AS category_spending
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
INNER JOIN categories c ON p.category_id = c.id
GROUP BY u.id, u.username, c.id, c.name
ORDER BY u.username, category_spending DESC;

Join Troubleshooting

Common Join Issues

Duplicate rows: Multiple matches create extra rows. Use DISTINCT or adjust JOIN conditions
Wrong JOIN type: INNER JOIN excludes non-matching rows, use LEFT JOIN if needed
Missing foreign key indexes: Slow performance on large tables
Ambiguous column names: Always prefix columns with table alias
Cartesian product: Forgetting JOIN condition creates all combinations

JOIN Types Summary

JOIN Type	Returns	Use Case
`INNER JOIN`	Only matching rows	When you need related data
`LEFT JOIN`	All left + matching right	Include all from left table
`RIGHT JOIN`	All right + matching left	Rarely used (use LEFT instead)
`CROSS JOIN`	All combinations	Generating test data
`SELF JOIN`	Table joined with itself	Hierarchical data

What's Next?

Congratulations! You've completed Module 7: MySQL Fundamentals. You now understand:

Database concepts and MySQL basics
Creating and managing databases and tables
Inserting, selecting, updating, and deleting data
Advanced queries with GROUP BY, HAVING, and subqueries
Joining multiple tables and managing relationships

In the next module, you'll learn how to connect PHP to MySQL databases, execute queries from PHP code, handle results, prevent SQL injection, and build complete database-driven web applications!

Practice Tip: The best way to master SQL is through practice. Create sample databases, write queries, experiment with different JOIN types, and analyze real-world data scenarios. Try building a complete e-commerce database with products, users, orders, and categories, then write complex queries to extract meaningful insights!