Joining Tables

Real databases have multiple related tables. JOIN combines them based on matching columns.

Say you have users and orders tables:

cursor.execute("""
    SELECT users.name, orders.total
    FROM users
    JOIN orders ON users.id = orders.user_id
""")

This finds matching rows where users.id equals orders.user_id and returns columns from both tables.

Join types:

• JOIN (or INNER JOIN) - only matching rows
• LEFT JOIN - all rows from left table, matching from right
• RIGHT JOIN - all rows from right table, matching from left

Left join example (includes users with no orders):

cursor.execute("""
    SELECT users.name, orders.total
    FROM users
    LEFT JOIN orders ON users.id = orders.user_id
""")

Joins are essential for querying normalized databases.

I cover joins in depth in my SQL with Python course.