Python MySQL - JOIN Operations (E-Commerce Platform)

What You'll Learn Here

INNER JOIN — matching records from both tables
LEFT JOIN — all records from left table
RIGHT JOIN — all records from right table
Self-Join — joining a table with itself
Multiple JOINs — joining three or more tables

What are JOINs?

A JOIN is a way to combine data from two or more tables based on a related column. In an E-Commerce platform, you often need to combine data from products, orders, customers, and reviews.

INNER JOIN

Only matching records from both tables

LEFT JOIN

All records from left, matching from right

RIGHT JOIN

All records from right, matching from left

Self-Join

Join a table with itself

JOINs in E-Commerce

# ============================================================
# JOIN EXAMPLES IN E-COMMERCE
# ============================================================

# 1. Orders with customer names (INNER JOIN)
SELECT o.order_id, o.total_amount, c.first_name, c.last_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

# 2. All customers with their orders (LEFT JOIN)
SELECT c.first_name, c.last_name, o.order_id, o.total_amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

# 3. Order details with product names
SELECT o.order_id, p.product_name, oi.quantity, oi.unit_price
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id;

# 4. Self-Join: Employee manager relationships
SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;

Key point: JOINs let you combine related data from multiple tables in a single query.

Quick Check: What does a JOIN do? (Answer: Combines data from two or more tables based on a related column)

INNER JOIN

Matching Records from Both Tables

┌─────────────────────────────────────────────────────┐ │ INNER JOIN - Only Matching Records │ ├─────────────────────────────────────────────────────┤ │ │ │ customers orders │ │ ┌──────────────┐ ┌──────────────┐ │ │ │ id │ name │ │ id │ cust_id │ │ │ ├────┼─────────┤ ├────┼─────────┤ │ │ │ 1 │ Rahul │◄─────│ 1 │ 1 │ │ │ │ 2 │ Priya │◄─────│ 2 │ 1 │ │ │ │ 3 │ Amit │ │ 3 │ 2 │ │ │ └────┴─────────┘ └────┴─────────┘ │ │ │ │ Result: Rahul (2 orders), Priya (1 order) │ │ Amit has no orders → NOT included │ │ │ │ ✅ Only customers with orders appear │ └─────────────────────────────────────────────────────┘

# ============================================================
# INNER JOIN FROM PYTHON
# ============================================================

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="secret",
    database="ecommerce_db"
)

cursor = connection.cursor()

# ============================================================
# 1. ORDERS WITH CUSTOMER NAMES
# ============================================================

query = """
    SELECT o.order_id, o.order_date, o.total_amount, o.status,
           c.first_name, c.last_name, c.email
    FROM orders o
    INNER JOIN customers c ON o.customer_id = c.customer_id
"""
cursor.execute(query)
results = cursor.fetchall()

print("Orders with customer details:")
for r in results:
    print(f"  Order {r[0]} - ${r[2]} - {r[4]} {r[5]} - {r[3]}")

# ============================================================
# 2. ORDER ITEMS WITH PRODUCT NAMES
# ============================================================

query = """
    SELECT oi.order_id, p.product_name, oi.quantity, oi.unit_price,
           (oi.quantity * oi.unit_price) AS total
    FROM order_items oi
    INNER JOIN products p ON oi.product_id = p.product_id
"""
cursor.execute(query)
results = cursor.fetchall()

print("\nOrder items with product names:")
for r in results:
    print(f"  Order {r[0]} - {r[1]} x {r[2]} = ${r[4]}")

# ============================================================
# 3. REVIEWS WITH CUSTOMER AND PRODUCT NAMES
# ============================================================

query = """
    SELECT r.rating, r.comment, 
           c.first_name, c.last_name,
           p.product_name
    FROM reviews r
    INNER JOIN customers c ON r.customer_id = c.customer_id
    INNER JOIN products p ON r.product_id = p.product_id
"""
cursor.execute(query)
results = cursor.fetchall()

print("\nReviews with customer and product:")
for r in results:
    print(f"  {r[0]} stars - {r[1][:30]}... - {r[2]} {r[3]} - {r[4]}")

cursor.close()
connection.close()

INNER JOIN key points:

INNER JOIN — returns only matching rows from both tables
ON — specifies the matching condition
Most common JOIN — used for related data
Can join multiple tables — chain JOIN statements

Quick Check: What does INNER JOIN return? (Answer: Only rows that have matches in both tables)

LEFT JOIN

All Records from Left Table

┌─────────────────────────────────────────────────────┐ │ LEFT JOIN - All Left, Matching Right │ ├─────────────────────────────────────────────────────┤ │ │ │ customers orders │ │ ┌──────────────┐ ┌──────────────┐ │ │ │ id │ name │ │ id │ cust_id │ │ │ ├────┼─────────┤ ├────┼─────────┤ │ │ │ 1 │ Rahul │◄─────│ 1 │ 1 │ │ │ │ 2 │ Priya │◄─────│ 2 │ 1 │ │ │ │ 3 │ Amit │ │ 3 │ 2 │ │ │ └────┴─────────┘ └────┴─────────┘ │ │ │ │ Result: Rahul (2 orders), Priya (1 order) │ │ Amit → NULL (no orders) │ │ │ │ ✅ ALL customers appear │ └─────────────────────────────────────────────────────┘

# ============================================================
# LEFT JOIN FROM PYTHON
# ============================================================

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="secret",
    database="ecommerce_db"
)

cursor = connection.cursor()

# ============================================================
# 1. ALL CUSTOMERS WITH THEIR ORDERS
# ============================================================

query = """
    SELECT c.customer_id, c.first_name, c.last_name,
           o.order_id, o.order_date, o.total_amount, o.status
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
"""
cursor.execute(query)
results = cursor.fetchall()

print("All customers with orders (NULL if no order):")
for r in results:
    order = f"Order {r[3]} - ${r[5]} - {r[6]}" if r[3] else "No orders"
    print(f"  {r[1]} {r[2]} - {order}")

# ============================================================
# 2. CUSTOMERS WITHOUT ORDERS
# ============================================================

query = """
    SELECT c.first_name, c.last_name, c.email
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    WHERE o.order_id IS NULL
"""
cursor.execute(query)
results = cursor.fetchall()

print("\nCustomers with NO orders:")
for r in results:
    print(f"  {r[0]} {r[1]} - {r[2]}")

# ============================================================
# 3. ALL PRODUCTS WITH THEIR REVIEWS
# ============================================================

query = """
    SELECT p.product_id, p.product_name, p.price,
           r.review_id, r.rating, r.comment
    FROM products p
    LEFT JOIN reviews r ON p.product_id = r.product_id
"""
cursor.execute(query)
results = cursor.fetchall()

print("\nAll products with reviews (NULL if no review):")
for r in results:
    review = f"Review {r[3]} - Rating: {r[4]}" if r[3] else "No reviews"
    print(f"  {r[1]} - ${r[2]} - {review}")

cursor.close()
connection.close()

LEFT JOIN key points:

LEFT JOIN — returns all rows from the left table
NULL for no match — right table columns are NULL
Useful for finding missing data — customers without orders
IS NULL check — find rows with no match

Quick Check: What does LEFT JOIN return? (Answer: All rows from the left table, plus matching rows from the right table)

RIGHT JOIN

All Records from Right Table

┌─────────────────────────────────────────────────────┐ │ RIGHT JOIN - All Right, Matching Left │ ├─────────────────────────────────────────────────────┤ │ │ │ customers orders │ │ ┌──────────────┐ ┌──────────────┐ │ │ │ id │ name │ │ id │ cust_id │ │ │ ├────┼─────────┤ ├────┼─────────┤ │ │ │ 1 │ Rahul │───► │ 1 │ 1 │ │ │ │ 2 │ Priya │───► │ 2 │ 1 │ │ │ │ 3 │ Amit │ │ 3 │ 2 │ │ │ └────┴─────────┘ └────┴─────────┘ │ │ │ │ Result: Order 1 → Rahul, Order 2 → Rahul │ │ Order 3 → Priya │ │ ✅ ALL orders appear even without customer │ └─────────────────────────────────────────────────────┘

# ============================================================
# RIGHT JOIN FROM PYTHON
# ============================================================

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="secret",
    database="ecommerce_db"
)

cursor = connection.cursor()

# ============================================================
# RIGHT JOIN EXAMPLE
# ============================================================

# Note: RIGHT JOIN is less common. LEFT JOIN is usually preferred.
# RIGHT JOIN can be rewritten as LEFT JOIN by swapping tables.

query = """
    SELECT o.order_id, o.total_amount, o.status,
           c.first_name, c.last_name
    FROM customers c
    RIGHT JOIN orders o ON c.customer_id = o.customer_id
"""
cursor.execute(query)
results = cursor.fetchall()

print("All orders with customer names:")
for r in results:
    name = f"{r[3]} {r[4]}" if r[3] else "Unknown customer"
    print(f"  Order {r[0]} - ${r[1]} - {name}")

# ============================================================
# RIGHT JOIN AS LEFT JOIN (Swapped)
# ============================================================

# The above RIGHT JOIN can be rewritten as:
query = """
    SELECT o.order_id, o.total_amount, o.status,
           c.first_name, c.last_name
    FROM orders o
    LEFT JOIN customers c ON o.customer_id = c.customer_id
"""
cursor.execute(query)
results = cursor.fetchall()

print("\nSame result using LEFT JOIN:")
for r in results:
    name = f"{r[3]} {r[4]}" if r[3] else "Unknown customer"
    print(f"  Order {r[0]} - ${r[1]} - {name}")

cursor.close()
connection.close()

RIGHT JOIN key points:

RIGHT JOIN — returns all rows from the right table
Less common — LEFT JOIN is usually preferred
Can be rewritten — as LEFT JOIN by swapping tables
NULL for no match — left table columns are NULL

Quick Check: How can you rewrite a RIGHT JOIN as a LEFT JOIN? (Answer: Swap the table order)

Self-Join

Joining a Table with Itself

┌─────────────────────────────────────────────────────┐ │ Self-Join - Table Joins Itself │ ├─────────────────────────────────────────────────────┤ │ │ │ employees │ │ ┌──────────────┬─────────────────────┐ │ │ │ id │ name │ manager_id │ │ │ ├────┼─────────┼─────────────────────┤ │ │ │ 1 │ John │ NULL (CEO) │ │ │ │ 2 │ Jane │ 1 │ │ │ │ 3 │ Bob │ 1 │ │ │ │ 4 │ Alice │ 2 │ │ │ │ 5 │ Charlie │ 2 │ │ │ └────┴─────────┴─────────────────────┘ │ │ │ │ Self-Join: │ │ SELECT e1.name AS employee, e2.name AS manager │ │ FROM employees e1 │ │ LEFT JOIN employees e2 ON e1.manager_id = e2.id │ │ │ │ Result: John → NULL, Jane → John, Bob → John │ │ Alice → Jane, Charlie → Jane │ └─────────────────────────────────────────────────────┘

# ============================================================
# SELF-JOIN FROM PYTHON
# ============================================================

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="secret",
    database="ecommerce_db"
)

cursor = connection.cursor()

# ============================================================
# SELF-JOIN EXAMPLE: Product Recommendations
# ============================================================

# Products table with recommendation relationships
# product_id, product_name, category, recommended_product_id

query = """
    SELECT p1.product_name AS product,
           p2.product_name AS recommended_product
    FROM products p1
    LEFT JOIN products p2 ON p1.recommended_product_id = p2.product_id
"""
cursor.execute(query)
results = cursor.fetchall()

print("Product recommendations:")
for r in results:
    rec = r[1] if r[1] else "No recommendation"
    print(f"  {r[0]} → {rec}")

# ============================================================
# FIND PRODUCTS IN SAME CATEGORY
# ============================================================

query = """
    SELECT p1.product_name AS product1,
           p2.product_name AS product2,
           p1.category
    FROM products p1
    INNER JOIN products p2 ON p1.category = p2.category
    WHERE p1.product_id < p2.product_id
    ORDER BY p1.category
"""
cursor.execute(query)
results = cursor.fetchall()

print("\nProducts in same category:")
for r in results:
    print(f"  {r[0]} & {r[1]} - {r[2]}")

cursor.close()
connection.close()

Self-Join key points:

Self-Join — joins a table with itself
Use aliases — give different names to the same table
Useful for hierarchies — employee-manager relationships
Common use cases — categories, recommendations, org charts

Quick Check: What is a self-join? (Answer: Joining a table with itself)

Multiple JOINs

Joining Three or More Tables

# ============================================================
# MULTIPLE JOINs FROM PYTHON
# ============================================================

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="secret",
    database="ecommerce_db"
)

cursor = connection.cursor()

# ============================================================
# 1. COMPLETE ORDER DETAILS
# ============================================================

query = """
    SELECT o.order_id, o.order_date, o.total_amount, o.status,
           c.first_name, c.last_name, c.email,
           p.product_name, oi.quantity, oi.unit_price,
           (oi.quantity * oi.unit_price) AS item_total
    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_id = 1
"""
cursor.execute(query)
results = cursor.fetchall()

print("Complete order details:")
for r in results:
    print(f"  Order {r[0]} - {r[4]} {r[5]} - {r[1]}")
    print(f"    {r[7]} x {r[8]} = ${r[10]}")
    print(f"    Total: ${r[2]}")

# ============================================================
# 2. CUSTOMER PURCHASE HISTORY WITH PRODUCTS
# ============================================================

query = """
    SELECT c.first_name, c.last_name,
           o.order_date,
           p.product_name,
           oi.quantity,
           oi.unit_price
    FROM customers c
    INNER JOIN orders o ON c.customer_id = o.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 c.customer_id = 1
    ORDER BY o.order_date DESC
"""
cursor.execute(query)
results = cursor.fetchall()

print("\nCustomer purchase history:")
for r in results:
    print(f"  {r[0]} {r[1]} - {r[2]}")
    print(f"    {r[3]} x {r[4]} = ${r[5] * r[4]}")

# ============================================================
# 3. PRODUCT PERFORMANCE SUMMARY
# ============================================================

query = """
    SELECT p.product_name, p.category, p.price,
           COALESCE(SUM(oi.quantity), 0) AS total_sold,
           COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS revenue,
           COALESCE(AVG(r.rating), 0) AS avg_rating,
           COUNT(DISTINCT r.review_id) AS review_count
    FROM products p
    LEFT JOIN order_items oi ON p.product_id = oi.product_id
    LEFT JOIN reviews r ON p.product_id = r.product_id
    GROUP BY p.product_id
    ORDER BY revenue DESC
"""
cursor.execute(query)
results = cursor.fetchall()

print("\nProduct performance summary:")
for r in results:
    print(f"  {r[0]} - ${r[2]} | Sold: {r[3]} | Revenue: ${r[4]:.2f} | Rating: {r[5]:.1f} ({r[6]} reviews)")

cursor.close()
connection.close()

Multiple JOINs key points:

Chain JOINs — add multiple JOIN statements
Different JOIN types — can mix INNER, LEFT, RIGHT
Use aliases — for readability with many tables
Order matters — for LEFT/RIGHT JOINs

Quick Check: How do you join three tables? (Answer: Chain JOIN statements with ON conditions)

E-Commerce Python Examples

Complete E-Commerce JOIN Examples

# ============================================================
# COMPLETE E-COMMERCE JOIN EXAMPLES
# ============================================================

import mysql.connector

class ECommerceJoins:
    """E-Commerce JOIN operations in Python"""
    
    def __init__(self, db_config):
        self.db_config = db_config
        self.connection = None
        self.cursor = None
    
    def connect(self):
        try:
            self.connection = mysql.connector.connect(**self.db_config)
            self.cursor = self.connection.cursor()
            return True
        except Exception as e:
            print(f"Connection failed: {e}")
            return False
    
    # ============================================================
    # ORDER REPORTS
    # ============================================================
    
    def get_order_with_customer(self, order_id):
        """Get order with customer details"""
        query = """
            SELECT o.order_id, o.order_date, o.total_amount, o.status,
                   c.first_name, c.last_name, c.email, c.phone
            FROM orders o
            INNER JOIN customers c ON o.customer_id = c.customer_id
            WHERE o.order_id = %s
        """
        self.cursor.execute(query, (order_id,))
        return self.cursor.fetchone()
    
    def get_order_with_items(self, order_id):
        """Get order with all items and products"""
        query = """
            SELECT o.order_id, o.order_date, o.total_amount,
                   p.product_name, oi.quantity, oi.unit_price,
                   (oi.quantity * oi.unit_price) AS item_total
            FROM orders o
            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_id = %s
        """
        self.cursor.execute(query, (order_id,))
        return self.cursor.fetchall()
    
    # ============================================================
    # CUSTOMER REPORTS
    # ============================================================
    
    def get_customer_summary(self, customer_id):
        """Get complete customer summary"""
        query = """
            SELECT c.customer_id, c.first_name, c.last_name, c.email,
                   COUNT(DISTINCT o.order_id) AS order_count,
                   COALESCE(SUM(o.total_amount), 0) AS total_spent,
                   COALESCE(AVG(r.rating), 0) AS avg_rating
            FROM customers c
            LEFT JOIN orders o ON c.customer_id = o.customer_id
            LEFT JOIN reviews r ON c.customer_id = r.customer_id
            WHERE c.customer_id = %s
            GROUP BY c.customer_id
        """
        self.cursor.execute(query, (customer_id,))
        return self.cursor.fetchone()
    
    def get_customers_with_reviews(self):
        """Get customers who have written reviews"""
        query = """
            SELECT DISTINCT c.first_name, c.last_name, c.email,
                   COUNT(r.review_id) AS review_count
            FROM customers c
            INNER JOIN reviews r ON c.customer_id = r.customer_id
            GROUP BY c.customer_id
            ORDER BY review_count DESC
        """
        self.cursor.execute(query)
        return self.cursor.fetchall()
    
    # ============================================================
    # PRODUCT REPORTS
    # ============================================================
    
    def get_product_performance(self, product_id):
        """Get complete product performance"""
        query = """
            SELECT p.product_id, p.product_name, p.category, p.price,
                   COALESCE(SUM(oi.quantity), 0) AS total_sold,
                   COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS revenue,
                   COALESCE(AVG(r.rating), 0) AS avg_rating,
                   COUNT(r.review_id) AS review_count
            FROM products p
            LEFT JOIN order_items oi ON p.product_id = oi.product_id
            LEFT JOIN reviews r ON p.product_id = r.product_id
            WHERE p.product_id = %s
            GROUP BY p.product_id
        """
        self.cursor.execute(query, (product_id,))
        return self.cursor.fetchone()
    
    def get_products_with_reviews(self):
        """Get products with reviews"""
        query = """
            SELECT p.product_name, p.price, p.category,
                   AVG(r.rating) AS avg_rating,
                   COUNT(r.review_id) AS review_count
            FROM products p
            INNER JOIN reviews r ON p.product_id = r.product_id
            GROUP BY p.product_id
            HAVING review_count > 0
            ORDER BY avg_rating DESC
        """
        self.cursor.execute(query)
        return self.cursor.fetchall()
    
    # ============================================================
    # ANALYTICS REPORTS
    # ============================================================
    
    def get_category_sales(self):
        """Get sales by category"""
        query = """
            SELECT p.category,
                   COUNT(DISTINCT o.order_id) AS order_count,
                   SUM(oi.quantity) AS units_sold,
                   SUM(oi.quantity * oi.unit_price) AS revenue
            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.category
            ORDER BY revenue DESC
        """
        self.cursor.execute(query)
        return self.cursor.fetchall()
    
    def get_monthly_sales(self):
        """Get monthly sales summary"""
        query = """
            SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS month,
                   COUNT(o.order_id) AS order_count,
                   SUM(o.total_amount) AS revenue,
                   COUNT(DISTINCT o.customer_id) AS unique_customers
            FROM orders o
            GROUP BY DATE_FORMAT(o.order_date, '%Y-%m')
            ORDER BY month DESC
        """
        self.cursor.execute(query)
        return self.cursor.fetchall()
    
    def display_results(self, results, title="Results"):
        """Display results"""
        if not results:
            print(f"\n{title}: No results")
            return
        print(f"\n{title}: {len(results)} rows")
        print("-" * 60)
        if isinstance(results, tuple):
            # Single row
            print(f"  {results}")
        else:
            # Multiple rows
            for row in results:
                print(f"  {row}")
    
    def close(self):
        if self.cursor:
            self.cursor.close()
        if self.connection:
            self.connection.close()

# ============================================================
# DEMONSTRATION
# ============================================================

db_config = {
    "host": "localhost",
    "user": "root",
    "password": "secret",
    "database": "ecommerce_db"
}

ecom = ECommerceJoins(db_config)

if ecom.connect():
    
    print("=" * 60)
    print("E-COMMERCE JOIN EXAMPLES")
    print("=" * 60)
    
    # 1. Order with customer
    print("\n1. ORDER WITH CUSTOMER DETAILS")
    result = ecom.get_order_with_customer(1)
    if result:
        print(f"  Order {result[0]} - ${result[2]} - {result[4]} {result[5]}")
    
    # 2. Order with items
    print("\n2. ORDER ITEMS")
    results = ecom.get_order_with_items(1)
    for r in results:
        print(f"  {r[3]} x {r[4]} = ${r[6]}")
    
    # 3. Customer summary
    print("\n3. CUSTOMER SUMMARY")
    result = ecom.get_customer_summary(1)
    if result:
        print(f"  {result[1]} {result[2]} - Orders: {result[4]} | Spent: ${result[5]}")
    
    # 4. Category sales
    print("\n4. CATEGORY SALES")
    results = ecom.get_category_sales()
    for r in results:
        print(f"  {r[0]}: {r[1]} orders, {r[2]} units, ${r[3]} revenue")
    
    # 5. Products with reviews
    print("\n5. PRODUCTS WITH REVIEWS")
    results = ecom.get_products_with_reviews()
    for r in results:
        print(f"  {r[0]} - ${r[1]} - Rating: {r[3]:.1f} ({r[4]} reviews)")
    
    ecom.close()

This example shows:

INNER JOIN for complete order details
LEFT JOIN for customer summaries
Multiple JOINs for complex reports
JOIN with aggregation for analytics
Real E-Commerce business queries

Quick Check: What is the difference between INNER JOIN and LEFT JOIN in E-Commerce? (Answer: INNER JOIN shows only customers with orders; LEFT JOIN shows all customers including those without orders)

Best Practices

JOIN Best Practices

# ============================================================
# JOIN BEST PRACTICES
# ============================================================

print("1. USE MEANINGFUL ALIASES")
print("   - Use short, clear aliases (c, o, p)")
print("   - Improves readability")

print("\n2. SPECIFY COLUMNS EXPLICITLY")
print("   - Avoid SELECT * in JOINs")
print("   - List only needed columns")

print("\n3. USE INDEXES ON JOIN COLUMNS")
print("   - Columns used in ON should be indexed")
print("   - Speeds up JOIN operations")

print("\n4. CHOOSE THE RIGHT JOIN TYPE")
print("   - INNER JOIN for matching data")
print("   - LEFT JOIN for all left table data")
print("   - RIGHT JOIN rarely needed")

print("\n5. TEST WITH EXPLAIN")
print("   - Check execution plans")
print("   - Identify performance issues")

print("\n6. AVOID TOO MANY JOINS")
print("   - Too many JOINs slow down queries")
print("   - Consider denormalization for performance")

print("\n7. USE PARAMETERIZED QUERIES")
print("   - Always use %s placeholders")
print("   - Protects against SQL injection")

print("\n8. TEST WITH SMALL DATASETS FIRST")
print("   - Verify results before running on production")
print("   - Use LIMIT during testing")

Summary of best practices:

Use meaningful aliases — for readability
Specify columns — avoid SELECT *
Use indexes — on JOIN columns
Choose right JOIN type — for your use case
Test with EXPLAIN — analyze performance

Quick Check: What is the most important practice for JOIN queries? (Answer: Use indexes on JOIN columns and avoid SELECT *)

Try It Yourself

Experiment with JOIN operations in the editor below.

Loading Pyodide... 0%

Python Code Editor

# Python MySQL JOINs - Practice

print("=" * 40)
print("JOIN OPERATIONS - PRACTICE")
print("=" * 40)

# Simulated E-Commerce Data
class ECommerceDB:
    customers = [
        {"id": 1, "name": "Rahul Sharma", "email": "rahul@email.com"},
        {"id": 2, "name": "Priya Patel", "email": "priya@email.com"},
        {"id": 3, "name": "Amit Singh", "email": "amit@email.com"}
    ]
    
    orders = [
        {"id": 1, "customer_id": 1, "amount": 1099.98, "status": "completed"},
        {"id": 2, "customer_id": 1, "amount": 69.99, "status": "pending"},
        {"id": 3, "customer_id": 2, "amount": 179.99, "status": "completed"}
    ]
    
    products = [
        {"id": 1, "name": "iPhone 15 Pro", "price": 999.99},
        {"id": 2, "name": "MacBook Air", "price": 1099.99},
        {"id": 3, "name": "Nike Air Max", "price": 149.99}
    ]
    
    order_items = [
        {"order_id": 1, "product_id": 1, "quantity": 1, "price": 999.99},
        {"order_id": 1, "product_id": 3, "quantity": 1, "price": 69.99},
        {"order_id": 2, "product_id": 3, "quantity": 1, "price": 69.99},
        {"order_id": 3, "product_id": 3, "quantity": 1, "price": 149.99}
    ]
    
    @classmethod
    def inner_join_customers_orders(cls):
        """INNER JOIN: Customers with orders"""
        results = []
        for c in cls.customers:
            for o in cls.orders:
                if c["id"] == o["customer_id"]:
                    results.append({
                        "customer": c["name"],
                        "order_id": o["id"],
                        "amount": o["amount"],
                        "status": o["status"]
                    })
        return results
    
    @classmethod
    def left_join_customers_orders(cls):
        """LEFT JOIN: All customers with orders"""
        results = []
        for c in cls.customers:
            has_order = False
            for o in cls.orders:
                if c["id"] == o["customer_id"]:
                    results.append({
                        "customer": c["name"],
                        "order_id": o["id"],
                        "amount": o["amount"],
                        "status": o["status"]
                    })
                    has_order = True
            if not has_order:
                results.append({
                    "customer": c["name"],
                    "order_id": "No orders",
                    "amount": "N/A",
                    "status": "N/A"
                })
        return results
    
    @classmethod
    def join_orders_items_products(cls):
        """JOIN: Orders with items and products"""
        results = []
        for o in cls.orders:
            for oi in cls.order_items:
                if o["id"] == oi["order_id"]:
                    for p in cls.products:
                        if oi["product_id"] == p["id"]:
                            results.append({
                                "order_id": o["id"],
                                "customer_id": o["customer_id"],
                                "product": p["name"],
                                "quantity": oi["quantity"],
                                "price": oi["price"],
                                "total": oi["quantity"] * oi["price"]
                            })
        return results

print("\n1. INNER JOIN - Customers with Orders")
print("-" * 40)

results = ECommerceDB.inner_join_customers_orders()
for r in results:
    print(f"  {r['customer']} - Order {r['order_id']}: ${r['amount']} ({r['status']})")

print("\n2. LEFT JOIN - All Customers with Orders")
print("-" * 40)

results = ECommerceDB.left_join_customers_orders()
for r in results:
    print(f"  {r['customer']} - Order {r['order_id']}: ${r['amount']} ({r['status']})")

print("\n3. MULTIPLE JOIN - Orders with Items and Products")
print("-" * 40)

results = ECommerceDB.join_orders_items_products()
for r in results:
    print(f"  Order {r['order_id']}: {r['product']} x {r['quantity']} = ${r['total']}")

print("\n4. JOIN SUMMARY")
print("-" * 40)
print("""
┌─────────────────┬──────────────────────────────────────────────┐
│ JOIN Type       │ Use Case                                   │
├─────────────────┼──────────────────────────────────────────────┤
│ INNER JOIN      │ Only matching records                     │
│ LEFT JOIN       │ All left + matching right                 │
│ RIGHT JOIN      │ All right + matching left                 │
│ Self-Join       │ Table joins itself                        │
│ Multiple JOINs  │ Join three or more tables                 │
└─────────────────┴──────────────────────────────────────────────┘
""")

print("JOINs combine data from multiple tables!")

========================================
JOIN OPERATIONS - PRACTICE
========================================

1. INNER JOIN - Customers with Orders
----------------------------------------
Rahul Sharma - Order 1: $1099.98 (completed)
Rahul Sharma - Order 2: $69.99 (pending)
Priya Patel - Order 3: $179.99 (completed)

2. LEFT JOIN - All Customers with Orders
----------------------------------------
Rahul Sharma - Order 1: $1099.98 (completed)
Rahul Sharma - Order 2: $69.99 (pending)
Priya Patel - Order 3: $179.99 (completed)
Amit Singh - No orders: N/A (N/A)

3. MULTIPLE JOIN - Orders with Items and Products
----------------------------------------
Order 1: iPhone 15 Pro x 1 = $999.99
Order 1: Nike Air Max x 1 = $69.99
Order 2: Nike Air Max x 1 = $69.99
Order 3: Nike Air Max x 1 = $149.99

4. JOIN SUMMARY
----------------------------------------
┌─────────────────┬──────────────────────────────────────────────┐
│ JOIN Type │ Use Case │
├─────────────────┼──────────────────────────────────────────────┤
│ INNER JOIN │ Only matching records │
│ LEFT JOIN │ All left + matching right │
│ RIGHT JOIN │ All right + matching left │
│ Self-Join │ Table joins itself │
│ Multiple JOINs │ Join three or more tables │
└─────────────────┴──────────────────────────────────────────────┘

JOINs combine data from multiple tables!

🏆

You've Got It!

You now understand JOIN operations in MySQL from Python. You can use INNER JOIN, LEFT JOIN, RIGHT JOIN, and self-joins for your E-Commerce platform.

Quick Quiz

Test what you've learned:

1. What does INNER JOIN return?

A) All rows from both tables B) Only matching rows from both tables C) All rows from left table D) All rows from right table

2. What does LEFT JOIN return?

A) All rows from left, matching from right B) Only matching rows C) All rows from right table D) All rows from both tables

3. What is a self-join?

A) Joining two different tables B) Joining a table with itself C) Joining three tables D) Joining without a condition

4. What is the difference between INNER JOIN and LEFT JOIN?

A) INNER JOIN only matches; LEFT JOIN all left B) They are the same C) LEFT JOIN only matches; INNER JOIN all left D) RIGHT JOIN is better

5. Why should you avoid SELECT * in JOINs?

A) It makes code look better B) It reduces data transfer and improves performance C) It's not supported D) It causes syntax errors

Frequently Asked Questions

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows that match in both tables. LEFT JOIN returns all rows from the left table and matching rows from the right table. Use INNER JOIN for matching data, LEFT JOIN when you need all left table data.

Can I join more than two tables?

Yes, you can join multiple tables by chaining JOIN statements. For example: FROM table1 JOIN table2 ON ... JOIN table3 ON .... Each JOIN must have the correct ON condition.

What is a common interview question about JOINs?

Common questions include: "Explain the difference between INNER JOIN and LEFT JOIN," "What is a self-join and when would you use it?" and "How do you join three tables?"

What is the difference between JOIN and UNION?

JOIN combines columns from different tables (horizontal). UNION combines rows from different tables (vertical). JOIN is for related data; UNION is for similar data.

Will this conflict with future pure MySQL tutorials?

No. This tutorial focuses on executing JOINs from Python with mysql-connector. Future MySQL tutorials will focus on SQL syntax and optimization with different titles and URLs.

Where to Go From Here

Now that you understand JOIN operations, check out these related topics:

Previous: Subqueries Next: Aggregation →

Python

Python: JOIN Operations in MySQL

What are JOINs?

INNER JOIN

LEFT JOIN

RIGHT JOIN

Self-Join

JOINs in E-Commerce

INNER JOIN

Matching Records from Both Tables

LEFT JOIN

All Records from Left Table

RIGHT JOIN

All Records from Right Table

Self-Join

Joining a Table with Itself

Multiple JOINs

Joining Three or More Tables

E-Commerce Python Examples

Complete E-Commerce JOIN Examples

Best Practices

JOIN Best Practices

Try It Yourself

You've Got It!

Quick Quiz

Frequently Asked Questions

What is the difference between INNER JOIN and LEFT JOIN?

Can I join more than two tables?

What is a common interview question about JOINs?

What is the difference between JOIN and UNION?

Will this conflict with future pure MySQL tutorials?

Where to Go From Here

Aggregation

Case Study

Practice Assignments

Python

Python: JOIN Operations in MySQL

What are JOINs?

INNER JOIN

LEFT JOIN

RIGHT JOIN

Self-Join

JOINs in E-Commerce

INNER JOIN

Matching Records from Both Tables

LEFT JOIN

All Records from Left Table

RIGHT JOIN

All Records from Right Table

Self-Join

Joining a Table with Itself

Multiple JOINs

Joining Three or More Tables

E-Commerce Python Examples

Complete E-Commerce JOIN Examples

Best Practices

JOIN Best Practices

Try It Yourself

You've Got It!

Quick Quiz

Frequently Asked Questions

What is the difference between INNER JOIN and LEFT JOIN? ▼

Can I join more than two tables? ▼

What is a common interview question about JOINs? ▼

What is the difference between JOIN and UNION? ▼

Will this conflict with future pure MySQL tutorials? ▼

Where to Go From Here

Aggregation

Case Study

Practice Assignments

What is the difference between INNER JOIN and LEFT JOIN?

Can I join more than two tables?

What is a common interview question about JOINs?

What is the difference between JOIN and UNION?

Will this conflict with future pure MySQL tutorials?