🔗 Joins & Relations

Real databases contain multiple related tables. Joins are how you combine data from these tables to get complete information. Understanding relationships is fundamental to good database design.

📊 Understanding Relationships

Before diving into joins, let's understand the three types of relationships:

1. One-to-One (1:1): One record in Table A relates to one record in Table B

• Example: One user has one profile

2. One-to-Many (1:N): One record in Table A relates to many records in Table B

• Example: One author has many books

3. Many-to-Many (N:N): Many records in Table A relate to many records in Table B

• Example: Students have many courses, courses have many students (requires a junction table)

🔀 INNER JOIN

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

Syntax:

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

Example 1: Basic INNER JOIN

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# Get books with their author names
cursor.execute("""
    SELECT
        books.book_id,
        books.title,
        authors.name as author_name,
        books.year
    FROM books
    INNER JOIN authors
    ON books.author_id = authors.author_id
    ORDER BY authors.name, books.title
""")

books_with_authors = cursor.fetchall()
print("Books and Authors:")
for book_id, title, author, year in books_with_authors:
    print(f"  {title} by {author} ({year})")

conn.close()

Example 2: JOIN with Multiple Conditions

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# Get orders with customer and book information
cursor.execute("""
    SELECT
        orders.order_id,
        customers.name as customer_name,
        books.title,
        orders.quantity,
        orders.order_date
    FROM orders
    INNER JOIN customers
    ON orders.customer_id = customers.customer_id
    INNER JOIN books
    ON orders.book_id = books.book_id
    WHERE orders.order_date >= '2024-01-01'
    ORDER BY orders.order_date DESC
""")

recent_orders = cursor.fetchall()
print("Recent Orders:")
for order_id, customer, book, qty, date in recent_orders:
    print(f"  {date}: {customer} ordered {qty} copy(ies) of '{book}'")

conn.close()

⬅️ LEFT JOIN (LEFT OUTER JOIN)

LEFT JOIN returns all rows from the left table, plus matching rows from the right table. Non-matching right table rows contain NULL.

Syntax:

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

Example 3: LEFT JOIN to Find Unmatched Records

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# Find authors with no books (using LEFT JOIN and checking for NULL)
cursor.execute("""
    SELECT
        authors.author_id,
        authors.name,
        COUNT(books.book_id) as book_count
    FROM authors
    LEFT JOIN books
    ON authors.author_id = books.author_id
    GROUP BY authors.author_id, authors.name
    HAVING COUNT(books.book_id) = 0
""")

authors_no_books = cursor.fetchall()
print("Authors with no books:")
for author_id, name, count in authors_no_books:
    print(f"  {name} (ID: {author_id})")

conn.close()

Example 4: LEFT JOIN for Complete Dataset

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# Get all customers with their order counts
# (some customers may have no orders)
cursor.execute("""
    SELECT
        customers.customer_id,
        customers.name,
        customers.email,
        COUNT(orders.order_id) as order_count,
        COALESCE(SUM(orders.total_amount), 0) as total_spent
    FROM customers
    LEFT JOIN orders
    ON customers.customer_id = orders.customer_id
    GROUP BY customers.customer_id, customers.name, customers.email
    ORDER BY total_spent DESC
""")

customer_stats = cursor.fetchall()
print("Customer Statistics:")
for cid, name, email, orders, spent in customer_stats:
    print(f"  {name}: {orders} orders, ${spent:.2f} spent")

conn.close()

🔗 Real-World Example: Linking Tables

Example 5: Complete E-Commerce Query

import sqlite3

conn = sqlite3.connect('ecommerce.db')
cursor = conn.cursor()

# Complex query joining customers, orders, and products
cursor.execute("""
    SELECT
        o.order_id,
        c.name as customer_name,
        c.email,
        p.product_name,
        oi.quantity,
        p.price,
        (oi.quantity * p.price) as line_total,
        o.order_date
    FROM orders o
    INNER JOIN customers c
    ON o.customer_id = c.customer_id
    INNER JOIN order_items oi
    ON o.order_id = oi.order_id
    INNER JOIN products p
    ON oi.product_id = p.product_id
    WHERE o.order_date >= date('now', '-30 days')
    ORDER BY o.order_date DESC, c.name
""")

recent_sales = cursor.fetchall()
print("Sales from Last 30 Days:")
for order_id, customer, email, product, qty, price, total, date in recent_sales:
    print(f"  Order {order_id} ({date}): {customer}")
    print(f"    {qty}x {product} @ ${price:.2f} = ${total:.2f}")

conn.close()

📋 Many-to-Many Relationships

Many-to-many relationships require a junction table (bridge table).

Example 6: Many-to-Many with Junction Table

import sqlite3

conn = sqlite3.connect('school.db')
cursor = conn.cursor()

# Tables structure:
# students (student_id, name)
# courses (course_id, title)
# enrollments (student_id, course_id) <- junction table

# Find which courses a student is enrolled in
student_id = 5
cursor.execute("""
    SELECT
        c.course_id,
        c.title,
        c.instructor,
        e.enrollment_date
    FROM courses c
    INNER JOIN enrollments e
    ON c.course_id = e.course_id
    WHERE e.student_id = ?
    ORDER BY c.title
""", (student_id,))

student_courses = cursor.fetchall()
print(f"Courses for Student {student_id}:")
for course_id, title, instructor, date in student_courses:
    print(f"  {title} (taught by {instructor})")

# Find all students in a course
course_id = 101
cursor.execute("""
    SELECT
        s.student_id,
        s.name,
        e.enrollment_date
    FROM students s
    INNER JOIN enrollments e
    ON s.student_id = e.student_id
    WHERE e.course_id = ?
    ORDER BY s.name
""", (course_id,))

course_students = cursor.fetchall()
print(f"\nStudents in Course {course_id}:")
for student_id, name, date in course_students:
    print(f"  {name} (enrolled {date})")

conn.close()

🎯 Aliasing for Clarity

Use aliases to make complex queries readable.

Example 7: Query Aliases

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# Find books by same author purchased by same customer
cursor.execute("""
    SELECT
        c.name as customer_name,
        a.name as author_name,
        COUNT(DISTINCT b.book_id) as books_purchased
    FROM customers c
    INNER JOIN orders o
    ON c.customer_id = o.customer_id
    INNER JOIN books b
    ON o.book_id = b.book_id
    INNER JOIN authors a
    ON b.author_id = a.author_id
    GROUP BY c.customer_id, a.author_id
    HAVING COUNT(DISTINCT b.book_id) > 1
    ORDER BY author_name, customer_name
""")

fan_data = cursor.fetchall()
print("Fans who bought multiple books by same author:")
for customer, author, count in fan_data:
    print(f"  {customer} bought {count} books by {author}")

conn.close()

🔑 Key Takeaways

• **Relationships** connect data across multiple tables (1:1, 1:N, N:N)

• **INNER JOIN** returns only matching rows from both tables

• **LEFT JOIN** returns all rows from the left table, with NULLs for non-matches

• **Junction tables** enable many-to-many relationships

• **Aliases** improve readability of complex queries

• **Foreign keys** maintain referential integrity

• Always specify the join condition with the ON clause

• Use meaningful table aliases for complex multi-table queries

📚 Further Learning

Ready for more advanced relationship patterns?

• [Advanced Joins & Relations](/courses/python/database_basics/advanced/joins_and_relations) - Multiple joins, self-joins, and performance optimization

• [Updating & Deleting](/courses/python/database_basics/beginner/updating_deleting) - Maintaining data integrity across relationships

• [Challenges & Quizzes](/#) - Practice building queries with multiple table joins