🚀 Advanced Joins & Relations

Advanced join techniques unlock powerful data analysis capabilities. From self-joins to correlated subqueries, these patterns solve complex real-world problems.

🔄 Self-Joins

A self-join compares a table to itself. This is useful for hierarchical data or finding relationships within the same table.

Example 1: Employee Hierarchy

import sqlite3

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

# Find employees and their managers
cursor.execute("""
    SELECT
        e.employee_id,
        e.name as employee_name,
        m.name as manager_name,
        e.salary,
        m.salary as manager_salary
    FROM employees e
    LEFT JOIN employees m
    ON e.manager_id = m.employee_id
    ORDER BY COALESCE(m.name, 'No Manager'), e.name
""")

org_structure = cursor.fetchall()
print("Employee-Manager Relationships:")
for emp_id, emp, mgr, salary, mgr_salary in org_structure:
    mgr_text = mgr or "CEO (No Manager)"
    print(f"  {emp} (${salary}) -> {mgr_text}")

conn.close()

Example 2: Finding Related Items

import sqlite3

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

# Find products frequently bought together
cursor.execute("""
    SELECT
        p1.product_id as product_1_id,
        p1.product_name,
        p2.product_id as product_2_id,
        p2.product_name,
        COUNT(*) as times_bought_together
    FROM order_items oi1
    INNER JOIN order_items oi2
    ON oi1.order_id = oi2.order_id
    AND oi1.product_id < oi2.product_id
    INNER JOIN products p1
    ON oi1.product_id = p1.product_id
    INNER JOIN products p2
    ON oi2.product_id = p2.product_id
    GROUP BY oi1.product_id, oi2.product_id
    HAVING COUNT(*) > 10
    ORDER BY times_bought_together DESC
    LIMIT 10
""")

product_pairs = cursor.fetchall()
print("Products Frequently Bought Together:")
for p1_id, p1_name, p2_id, p2_name, count in product_pairs:
    print(f"  {p1_name} + {p2_name}: {count} times")

conn.close()

🔀 Multiple Joins

Combine more than two tables to get comprehensive results.

Example 3: Chain of JOINs

import sqlite3

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

# Get complete course enrollment information
cursor.execute("""
    SELECT
        s.student_id,
        s.name as student_name,
        s.major,
        c.course_id,
        c.title as course_title,
        i.name as instructor_name,
        d.name as department_name,
        e.grade,
        e.enrollment_date
    FROM students s
    INNER JOIN enrollments e
    ON s.student_id = e.student_id
    INNER JOIN courses c
    ON e.course_id = c.course_id
    INNER JOIN instructors i
    ON c.instructor_id = i.instructor_id
    INNER JOIN departments d
    ON c.department_id = d.department_id
    WHERE s.major = 'Computer Science'
    ORDER BY s.name, c.title
""")

enrollments = cursor.fetchall()
print("CS Major Enrollments:")
for sid, sname, major, cid, ctitle, iname, dept, grade, date in enrollments:
    print(f"  {sname}: {ctitle} (taught by {iname}) - Grade: {grade}")

conn.close()

➡️ RIGHT JOIN and CROSS JOIN

While SQLite doesn't have explicit RIGHT JOIN, you can simulate it. CROSS JOIN creates a Cartesian product.

Example 4: Simulating RIGHT JOIN

import sqlite3

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

# Find all products, including those never sold
# (simulating RIGHT JOIN by reversing the direction)
cursor.execute("""
    SELECT
        p.product_id,
        p.product_name,
        COUNT(oi.order_item_id) as times_sold,
        SUM(oi.quantity) as total_units_sold
    FROM products p
    LEFT JOIN order_items oi
    ON p.product_id = oi.product_id
    GROUP BY p.product_id, p.product_name
    ORDER BY times_sold DESC
""")

product_sales = cursor.fetchall()
print("All Products with Sales Data:")
for product_id, name, times, units in product_sales:
    times_text = times if times > 0 else "Never"
    print(f"  {name}: {times_text} sales, {units or 0} units")

conn.close()

Example 5: CROSS JOIN for Combinations

import sqlite3

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

# Create all possible color-size combinations for a product
cursor.execute("""
    SELECT
        c.color,
        s.size,
        p.product_name,
        COALESCE(i.quantity, 0) as available
    FROM colors c
    CROSS JOIN sizes s
    CROSS JOIN products p
    LEFT JOIN inventory i
    ON c.color_id = i.color_id
    AND s.size_id = i.size_id
    AND p.product_id = i.product_id
    WHERE p.product_name = 'T-Shirt'
    ORDER BY c.color, s.size
""")

variants = cursor.fetchall()
print("T-Shirt Variants:")
for color, size, product, qty in variants:
    availability = f"{qty} in stock" if qty > 0 else "Out of stock"
    print(f"  {color} - {size}: {availability}")

conn.close()

🔍 Correlated Subqueries in Joins Context

Combine joins with correlated subqueries for advanced filtering.

Example 6: Complex Correlated Filtering

import sqlite3

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

# Find customers who bought expensive books AND spent more than average for their category
cursor.execute("""
    SELECT
        c.customer_id,
        c.name,
        COUNT(DISTINCT o.order_id) as order_count,
        SUM(o.total_amount) as total_spent,
        AVG(b.price) as avg_book_price
    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
    WHERE b.price > (
        SELECT AVG(price) FROM books
    )
    AND o.total_amount > (
        SELECT AVG(total_amount)
        FROM orders
        WHERE customer_id = c.customer_id
    )
    GROUP BY c.customer_id, c.name
    HAVING COUNT(DISTINCT o.order_id) > 2
""")

premium_customers = cursor.fetchall()
print("Premium Customers (Expensive Books, Above-Average Spending):")
for cid, name, orders, spent, avg_price in premium_customers:
    print(f"  {name}: {orders} orders, ${spent:.2f} total, ${avg_price:.2f} avg book price")

conn.close()

⚡ Join Performance Optimization

Example 7: Analyzing Join Performance

import sqlite3
import time

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

# Enable query profiling
cursor.execute("PRAGMA query_only = ON")

# Inefficient query (multiple LEFT JOINs without proper filtering)
start = time.time()
cursor.execute("""
    SELECT
        c.customer_id,
        c.name,
        COUNT(o.order_id) as order_count
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    LEFT JOIN order_items oi ON o.order_id = oi.order_id
    LEFT JOIN products p ON oi.product_id = p.product_id
    GROUP BY c.customer_id, c.name
""")
results1 = cursor.fetchall()
time1 = time.time() - start

# Optimized query (use INNER JOIN where possible, filter early)
start = time.time()
cursor.execute("""
    SELECT
        c.customer_id,
        c.name,
        COUNT(DISTINCT o.order_id) as order_count
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    WHERE c.created_at > date('now', '-1 year')
    GROUP BY c.customer_id, c.name
""")
results2 = cursor.fetchall()
time2 = time.time() - start

print(f"Complex query time: {time1:.4f}s")
print(f"Optimized query time: {time2:.4f}s")
print(f"Improvement: {(time1/time2 - 1)*100:.1f}%")

# View query execution plan
cursor.execute("""
    EXPLAIN QUERY PLAN
    SELECT c.name, COUNT(o.order_id)
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id
""")

print("\nQuery Execution Plan:")
for step in cursor.fetchall():
    print(f"  {step}")

conn.close()

🔗 Complex Real-World Example

Example 8: Sales Analytics Dashboard Query

import sqlite3

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

# Comprehensive sales analysis with multiple joins
cursor.execute("""
    WITH customer_metrics AS (
        SELECT
            c.customer_id,
            c.name,
            c.country,
            COUNT(DISTINCT o.order_id) as total_orders,
            SUM(o.total_amount) as lifetime_value
        FROM customers c
        LEFT JOIN orders o ON c.customer_id = o.customer_id
        GROUP BY c.customer_id, c.name, c.country
    ),
    product_rankings AS (
        SELECT
            p.product_id,
            p.product_name,
            p.category,
            SUM(oi.quantity) as total_units_sold,
            SUM(oi.quantity * p.price) as revenue,
            ROW_NUMBER() OVER (PARTITION BY p.category ORDER BY SUM(oi.quantity) DESC) as rank_in_category
        FROM products p
        INNER JOIN order_items oi ON p.product_id = oi.product_id
        INNER JOIN orders o ON oi.order_id = o.order_id
        GROUP BY p.product_id, p.product_name, p.category
    )
    SELECT
        cm.name as customer_name,
        cm.country,
        cm.total_orders,
        cm.lifetime_value,
        pr.product_name,
        pr.revenue
    FROM customer_metrics cm
    LEFT JOIN (
        SELECT DISTINCT customer_id, product_id FROM order_items
        WHERE order_id IN (
            SELECT order_id FROM orders o
            WHERE o.customer_id IN (SELECT customer_id FROM customer_metrics WHERE lifetime_value > 5000)
        )
    ) po ON cm.customer_id = po.customer_id
    LEFT JOIN product_rankings pr ON po.product_id = pr.product_id
    WHERE cm.lifetime_value > 1000
    ORDER BY cm.lifetime_value DESC
    LIMIT 20
""")

results = cursor.fetchall()
print("Top Customer Analytics:")
for customer, country, orders, ltv, product, revenue in results:
    print(f"  {customer} ({country}): {orders} orders, ${ltv:.2f} lifetime value")

conn.close()

🔑 Key Takeaways

• **Self-joins** compare a table to itself for hierarchical or relational data

• **Multiple joins** connect many tables to create comprehensive result sets

• **CROSS JOIN** creates Cartesian products for combinations

• **Correlated subqueries** with joins enable sophisticated filtering

• **Join order matters** for performance - INNER JOINs are usually faster than LEFT JOINs

• **Use DISTINCT** when joins create duplicate rows

• **EXPLAIN QUERY PLAN** helps optimize complex queries

• **CTEs** can simplify complex multi-join queries for readability

📚 Further Learning

Master advanced database patterns:

• [Advanced Querying](/courses/python/database_basics/advanced/querying_data) - CTEs and window functions

• [Performance Optimization](/courses/python/database_basics/advanced/database_performance) - Query profiling and index design

• [Challenges & Quizzes](/#) - Test complex join and relationship patterns