Python MySQL - Subqueries (E-Commerce Platform)

What You'll Learn Here

Subqueries in WHERE — filtering with nested queries
Subqueries in SELECT — calculated columns
Subqueries in FROM — derived tables
EXISTS and NOT EXISTS — checking existence
Correlated subqueries — queries that depend on outer query

What are Subqueries?

A subquery is a query nested inside another query. It's like asking a question within a question. Subqueries let you perform complex operations that would be difficult with a single query.

WHERE Clause

Filters using results from another query

SELECT Clause

Calculates values for each row

FROM Clause

Treats query results as a table

EXISTS

Checks if a subquery returns rows

Subqueries in E-Commerce

# ============================================================
# SUBQUERY EXAMPLES IN E-COMMERCE
# ============================================================

# 1. Find products above average price
SELECT product_name, price 
FROM products 
WHERE price > (SELECT AVG(price) FROM products);

# 2. Find customers with orders
SELECT first_name, last_name 
FROM customers 
WHERE customer_id IN (SELECT DISTINCT customer_id FROM orders);

# 3. Calculate each product's price vs average
SELECT product_name, price,
       (SELECT AVG(price) FROM products) AS avg_price,
       price - (SELECT AVG(price) FROM products) AS price_diff
FROM products;

# 4. Find customers who have never placed an order
SELECT first_name, last_name 
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

Key point: Subqueries let you answer complex questions by nesting queries inside queries.

Quick Check: What is a subquery? (Answer: A query nested inside another query)

Subqueries in WHERE

Filtering with Nested Queries

# ============================================================
# SUBQUERIES IN WHERE CLAUSE
# ============================================================

import mysql.connector

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

cursor = connection.cursor()

# ============================================================
# 1. ABOVE AVERAGE PRICE
# ============================================================

query = """
    SELECT product_name, price
    FROM products
    WHERE price > (SELECT AVG(price) FROM products)
"""
cursor.execute(query)
products = cursor.fetchall()

print("Products above average price:")
for p in products:
    print(f"  {p[0]} - ${p[1]}")

# ============================================================
# 2. PRODUCTS IN SPECIFIC PRICE RANGE (Using IN)
# ============================================================

# Find products in the same category as expensive products
query = """
    SELECT product_name, category, price
    FROM products
    WHERE category IN (
        SELECT DISTINCT category 
        FROM products 
        WHERE price > 500
    )
"""
cursor.execute(query)
products = cursor.fetchall()

print("\nProducts in categories with expensive products:")
for p in products:
    print(f"  {p[0]} - {p[1]} - ${p[2]}")

# ============================================================
# 3. CUSTOMERS FROM CITIES WITH ORDERS
# ============================================================

query = """
    SELECT first_name, last_name, city
    FROM customers
    WHERE city IN (
        SELECT DISTINCT city 
        FROM customers c
        JOIN orders o ON c.customer_id = o.customer_id
    )
"""
cursor.execute(query)
customers = cursor.fetchall()

print("\nCustomers from cities with orders:")
for c in customers:
    print(f"  {c[0]} {c[1]} - {c[2]}")

# ============================================================
# 4. USING > ALL (Greater than all values)
# ============================================================

# Find the most expensive product in each category
query = """
    SELECT product_name, category, price
    FROM products
    WHERE price >= ALL (
        SELECT price 
        FROM products p2 
        WHERE p2.category = products.category
    )
"""
cursor.execute(query)
products = cursor.fetchall()

print("\nMost expensive product in each category:")
for p in products:
    print(f"  {p[0]} - {p[1]} - ${p[2]}")

cursor.close()
connection.close()

WHERE subquery key points:

Comparison operators — >, <, =, >=, <= with subqueries
IN — matches any value from subquery
ALL/ANY — compares with all or any values
Returns single value — for comparison operators

Quick Check: What does IN do with a subquery? (Answer: It matches if the value is in the subquery's result set)

Subqueries in SELECT

Calculated Columns with Subqueries

# ============================================================
# SUBQUERIES IN SELECT CLAUSE
# ============================================================

import mysql.connector

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

cursor = connection.cursor()

# ============================================================
# 1. CALCULATE AVERAGE PRICE PER ROW
# ============================================================

query = """
    SELECT product_name, price,
           (SELECT AVG(price) FROM products) AS avg_price,
           price - (SELECT AVG(price) FROM products) AS price_diff
    FROM products
"""
cursor.execute(query)
products = cursor.fetchall()

print("Products with price comparison:")
for p in products:
    print(f"  {p[0]} - ${p[1]} | Avg: ${p[2]:.2f} | Diff: ${p[3]:.2f}")

# ============================================================
# 2. COUNT ORDERS FOR EACH CUSTOMER
# ============================================================

query = """
    SELECT c.first_name, c.last_name,
           (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS order_count,
           (SELECT COALESCE(SUM(o.total_amount), 0) FROM orders o WHERE o.customer_id = c.customer_id) AS total_spent
    FROM customers c
"""
cursor.execute(query)
customers = cursor.fetchall()

print("\nCustomers with order statistics:")
for c in customers:
    print(f"  {c[0]} {c[1]} - Orders: {c[2]} | Total: ${c[3]}")

# ============================================================
# 3. PRODUCT REVIEW STATISTICS
# ============================================================

query = """
    SELECT p.product_name,
           (SELECT COUNT(*) FROM reviews r WHERE r.product_id = p.product_id) AS review_count,
           (SELECT COALESCE(AVG(r.rating), 0) FROM reviews r WHERE r.product_id = p.product_id) AS avg_rating
    FROM products p
"""
cursor.execute(query)
products = cursor.fetchall()

print("\nProduct review statistics:")
for p in products:
    print(f"  {p[0]} - Reviews: {p[1]} | Avg Rating: {p[2]:.1f}")

cursor.close()
connection.close()

SELECT subquery key points:

Must return a single value — one row, one column
Can use aggregate functions — COUNT, SUM, AVG
Useful for calculated columns — comparing to aggregates
Can be correlated — references outer query

Quick Check: What type of subquery can be used in SELECT clause? (Answer: One that returns a single value)

Subqueries in FROM

Derived Tables

# ============================================================
# SUBQUERIES IN FROM CLAUSE (Derived Tables)
# ============================================================

import mysql.connector

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

cursor = connection.cursor()

# ============================================================
# 1. AGGREGATE DATA IN SUBQUERY
# ============================================================

# Find categories with average price above overall average
query = """
    SELECT category, avg_price
    FROM (
        SELECT category, AVG(price) AS avg_price
        FROM products
        GROUP BY category
    ) AS category_stats
    WHERE avg_price > (SELECT AVG(price) FROM products)
"""
cursor.execute(query)
categories = cursor.fetchall()

print("Categories with above-average prices:")
for c in categories:
    print(f"  {c[0]} - Avg Price: ${c[1]:.2f}")

# ============================================================
# 2. TOP CUSTOMERS BY SPENDING
# ============================================================

query = """
    SELECT first_name, last_name, total_spent
    FROM (
        SELECT c.customer_id, c.first_name, c.last_name,
               COALESCE(SUM(o.total_amount), 0) AS total_spent
        FROM customers c
        LEFT JOIN orders o ON c.customer_id = o.customer_id
        GROUP BY c.customer_id
    ) AS customer_spending
    WHERE total_spent > 100
    ORDER BY total_spent DESC
"""
cursor.execute(query)
customers = cursor.fetchall()

print("\nTop customers (spent over $100):")
for c in customers:
    print(f"  {c[0]} {c[1]} - Total Spent: ${c[2]}")

# ============================================================
# 3. PRODUCT PERFORMANCE RANKING
# ============================================================

query = """
    SELECT product_name, total_sold, revenue,
           RANK() OVER (ORDER BY total_sold DESC) AS sales_rank
    FROM (
        SELECT p.product_name,
               COALESCE(SUM(oi.quantity), 0) AS total_sold,
               COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS revenue
        FROM products p
        LEFT JOIN order_items oi ON p.product_id = oi.product_id
        GROUP BY p.product_id
    ) AS product_sales
    ORDER BY sales_rank
"""
cursor.execute(query)
products = cursor.fetchall()

print("\nProduct sales ranking:")
for p in products:
    print(f"  #{p[3]} {p[0]} - {p[1]} sold - ${p[2]}")

cursor.close()
connection.close()

FROM subquery key points:

Derived table — subquery result treated as a table
Must have an alias — AS table_name
Can be used with JOIN — join derived tables with others
Useful for complex aggregations — pre-aggregate data

Quick Check: What is a derived table? (Answer: A subquery in the FROM clause treated as a table)

EXISTS and NOT EXISTS

Checking for Existence

# ============================================================
# EXISTS AND NOT EXISTS
# ============================================================

import mysql.connector

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

cursor = connection.cursor()

# ============================================================
# 1. CUSTOMERS WHO HAVE PLACED ORDERS
# ============================================================

query = """
    SELECT c.first_name, c.last_name, c.email
    FROM customers c
    WHERE EXISTS (
        SELECT 1 FROM orders o 
        WHERE o.customer_id = c.customer_id
    )
"""
cursor.execute(query)
customers = cursor.fetchall()

print("Customers with orders:")
for c in customers:
    print(f"  {c[0]} {c[1]} - {c[2]}")

# ============================================================
# 2. CUSTOMERS WHO HAVE NEVER PLACED ORDERS
# ============================================================

query = """
    SELECT c.first_name, c.last_name, c.email
    FROM customers c
    WHERE NOT EXISTS (
        SELECT 1 FROM orders o 
        WHERE o.customer_id = c.customer_id
    )
"""
cursor.execute(query)
customers = cursor.fetchall()

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

# ============================================================
# 3. PRODUCTS WITH REVIEWS
# ============================================================

query = """
    SELECT p.product_name, p.price
    FROM products p
    WHERE EXISTS (
        SELECT 1 FROM reviews r 
        WHERE r.product_id = p.product_id
    )
"""
cursor.execute(query)
products = cursor.fetchall()

print("\nProducts with reviews:")
for p in products:
    print(f"  {p[0]} - ${p[1]}")

# ============================================================
# 4. PRODUCTS WITHOUT REVIEWS (NEW PRODUCTS)
# ============================================================

query = """
    SELECT p.product_name, p.price
    FROM products p
    WHERE NOT EXISTS (
        SELECT 1 FROM reviews r 
        WHERE r.product_id = p.product_id
    )
"""
cursor.execute(query)
products = cursor.fetchall()

print("\nProducts with NO reviews:")
for p in products:
    print(f"  {p[0]} - ${p[1]}")

cursor.close()
connection.close()

EXISTS key points:

EXISTS — returns TRUE if subquery returns any rows
NOT EXISTS — returns TRUE if subquery returns no rows
SELECT 1 — common practice (doesn't matter what you select)
Correlated by default — often references outer query

Quick Check: What does EXISTS do? (Answer: It checks if a subquery returns any rows)

Correlated Subqueries

Queries That Depend on the Outer Query

# ============================================================
# CORRELATED SUBQUERIES
# ============================================================

import mysql.connector

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

cursor = connection.cursor()

# ============================================================
# 1. PRODUCTS ABOVE CATEGORY AVERAGE
# ============================================================

query = """
    SELECT p1.product_name, p1.category, p1.price,
           (SELECT AVG(p2.price) FROM products p2 WHERE p2.category = p1.category) AS category_avg
    FROM products p1
    WHERE p1.price > (
        SELECT AVG(p2.price) FROM products p2 WHERE p2.category = p1.category
    )
"""
cursor.execute(query)
products = cursor.fetchall()

print("Products above their category average:")
for p in products:
    print(f"  {p[0]} - {p[1]} - ${p[2]} | Category Avg: ${p[3]:.2f}")

# ============================================================
# 2. CUSTOMERS WITH HIGHEST ORDER FOR EACH
# ============================================================

query = """
    SELECT c.first_name, c.last_name, 
           (SELECT MAX(o.total_amount) FROM orders o WHERE o.customer_id = c.customer_id) AS max_order,
           (SELECT COUNT(o.order_id) FROM orders o WHERE o.customer_id = c.customer_id) AS order_count
    FROM customers c
    HAVING max_order IS NOT NULL
"""
cursor.execute(query)
customers = cursor.fetchall()

print("\nCustomers with their largest order:")
for c in customers:
    print(f"  {c[0]} {c[1]} - Max Order: ${c[2]} | Total Orders: {c[3]}")

# ============================================================
# 3. PRODUCTS WITH REVIEWS ABOVE AVERAGE
# ============================================================

query = """
    SELECT p.product_name, p.price,
           (SELECT COALESCE(AVG(r.rating), 0) FROM reviews r WHERE r.product_id = p.product_id) AS avg_rating
    FROM products p
    WHERE (SELECT COALESCE(AVG(r.rating), 0) FROM reviews r WHERE r.product_id = p.product_id) > 4
"""
cursor.execute(query)
products = cursor.fetchall()

print("\nProducts with average rating above 4:")
for p in products:
    print(f"  {p[0]} - ${p[1]} - Rating: {p[2]:.1f}")

cursor.close()
connection.close()

Correlated subquery key points:

References outer query — uses values from the outer query
Runs once per row — executed for each row of the outer query
Can be slower — because it runs multiple times
Useful for row-by-row comparisons — like category averages

Quick Check: What makes a subquery "correlated"? (Answer: It references columns from the outer query)

E-Commerce Python Examples

Complete E-Commerce Subquery Examples

# ============================================================
# COMPLETE E-COMMERCE SUBQUERY EXAMPLES
# ============================================================

import mysql.connector

class ECommerceSubqueries:
    """E-Commerce subquery examples 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
    
    # ============================================================
    # ANALYTICS WITH SUBQUERIES
    # ============================================================
    
    def get_above_avg_products(self):
        """Get products above average price"""
        query = """
            SELECT product_name, price
            FROM products
            WHERE price > (SELECT AVG(price) FROM products)
        """
        self.cursor.execute(query)
        return self.cursor.fetchall()
    
    def get_category_avg_comparison(self):
        """Compare each product to its category average"""
        query = """
            SELECT p.product_name, p.category, p.price,
                   (SELECT AVG(p2.price) FROM products p2 WHERE p2.category = p.category) AS category_avg,
                   p.price - (SELECT AVG(p2.price) FROM products p2 WHERE p2.category = p.category) AS diff
            FROM products p
        """
        self.cursor.execute(query)
        return self.cursor.fetchall()
    
    def get_customer_order_stats(self):
        """Get customer order statistics using subqueries"""
        query = """
            SELECT c.customer_id, c.first_name, c.last_name,
                   (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS order_count,
                   (SELECT COALESCE(SUM(o.total_amount), 0) FROM orders o WHERE o.customer_id = c.customer_id) AS total_spent,
                   (SELECT COALESCE(MAX(o.total_amount), 0) FROM orders o WHERE o.customer_id = c.customer_id) AS max_order
            FROM customers c
            ORDER BY total_spent DESC
        """
        self.cursor.execute(query)
        return self.cursor.fetchall()
    
    def get_top_products_by_category(self):
        """Get top product in each category"""
        query = """
            SELECT p1.product_name, p1.category, p1.price
            FROM products p1
            WHERE p1.price = (SELECT MAX(p2.price) FROM products p2 WHERE p2.category = p1.category)
            ORDER BY p1.category
        """
        self.cursor.execute(query)
        return self.cursor.fetchall()
    
    def get_customers_without_orders(self):
        """Get customers with no orders using NOT EXISTS"""
        query = """
            SELECT c.first_name, c.last_name, c.email
            FROM customers c
            WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)
        """
        self.cursor.execute(query)
        return self.cursor.fetchall()
    
    def get_products_with_high_ratings(self):
        """Get products with average rating above 4"""
        query = """
            SELECT p.product_name, p.price,
                   (SELECT COALESCE(AVG(r.rating), 0) FROM reviews r WHERE r.product_id = p.product_id) AS avg_rating
            FROM products p
            WHERE (SELECT COALESCE(AVG(r.rating), 0) FROM reviews r WHERE r.product_id = p.product_id) >= 4
            ORDER BY avg_rating DESC
        """
        self.cursor.execute(query)
        return self.cursor.fetchall()
    
    def get_category_sales_ranking(self):
        """Get category sales ranking using derived table"""
        query = """
            SELECT category, total_revenue, sales_rank
            FROM (
                SELECT p.category,
                       COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS total_revenue,
                       RANK() OVER (ORDER BY COALESCE(SUM(oi.quantity * oi.unit_price), 0) DESC) AS sales_rank
                FROM products p
                LEFT JOIN order_items oi ON p.product_id = oi.product_id
                GROUP BY p.category
            ) AS category_stats
            ORDER BY sales_rank
        """
        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)
        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"
}

store = ECommerceSubqueries(db_config)

if store.connect():
    
    print("=" * 60)
    print("E-COMMERCE SUBQUERY EXAMPLES")
    print("=" * 60)
    
    # 1. Products above average
    print("\n1. PRODUCTS ABOVE AVERAGE PRICE")
    results = store.get_above_avg_products()
    for r in results:
        print(f"  {r[0]} - ${r[1]}")
    
    # 2. Category comparison
    print("\n2. PRODUCT VS CATEGORY AVERAGE")
    results = store.get_category_avg_comparison()
    for r in results[:5]:
        print(f"  {r[0]} - {r[1]} - ${r[2]} | Avg: ${r[3]:.2f} | Diff: ${r[4]:.2f}")
    
    # 3. Customer stats
    print("\n3. CUSTOMER ORDER STATISTICS")
    results = store.get_customer_order_stats()
    for r in results:
        print(f"  {r[1]} {r[2]} - Orders: {r[3]} | Total: ${r[4]} | Max: ${r[5]}")
    
    # 4. Customers without orders
    print("\n4. CUSTOMERS WITHOUT ORDERS")
    results = store.get_customers_without_orders()
    for r in results:
        print(f"  {r[0]} {r[1]} - {r[2]}")
    
    # 5. Products with high ratings
    print("\n5. PRODUCTS WITH HIGH RATINGS")
    results = store.get_products_with_high_ratings()
    for r in results:
        print(f"  {r[0]} - ${r[1]} - Rating: {r[2]:.1f}")
    
    store.close()

This example shows:

Subqueries in WHERE, SELECT, and FROM
EXISTS and NOT EXISTS for existence checks
Correlated subqueries for row-by-row calculations
Derived tables for complex aggregations
Real E-Commerce analytics queries

Quick Check: What is the difference between IN and EXISTS? (Answer: IN checks if a value is in the result set; EXISTS checks if the result set has any rows)

Best Practices

Subquery Best Practices

# ============================================================
# SUBQUERY BEST PRACTICES
# ============================================================

print("1. USE EXISTS INSTEAD OF IN FOR LARGE RESULTS")
print("   - EXISTS stops at first match")
print("   - IN loads all results before comparing")
print("   - Example: EXISTS is faster for large datasets")

print("\n2. USE JOINS INSTEAD OF SUBQUERIES WHEN POSSIBLE")
print("   - Joins are often more efficient")
print("   - Subqueries can be slower for large datasets")
print("   - Test both approaches")

print("\n3. AVOID CORRELATED SUBQUERIES FOR LARGE DATASETS")
print("   - They run once per row")
print("   - Can be very slow for large tables")

print("\n4. USE ALIASES FOR CLARITY")
print("   - Always alias subqueries")
print("   - Use meaningful alias names")

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

print("\n6. USE LIMIT IN SUBQUERIES WHEN APPROPRIATE")
print("   - When you only need one value")
print("   - Can improve performance")

print("\n7. BE CAREFUL WITH NULLS")
print("   - Subqueries can return NULL")
print("   - Use COALESCE when needed")

print("\n8. PARAMETERIZE SUBQUERIES")
print("   - Use %s placeholders")
print("   - Protects against SQL injection")

Summary of best practices:

Use EXISTS over IN — for large datasets
Consider JOINs — often more efficient
Avoid correlated subqueries — for large tables
Use aliases — for readability
Test with EXPLAIN — analyze performance

Quick Check: When should you use EXISTS instead of IN? (Answer: For large datasets where you only need to check existence)

Try It Yourself

Experiment with subqueries in the editor below.

Loading Pyodide... 0%

Python Code Editor

# Python MySQL Subqueries - Practice

print("=" * 40)
print("SUBQUERIES - PRACTICE")
print("=" * 40)

# Simulated E-Commerce Data
class ECommerceDB:
    products = [
        {"id": 1, "name": "iPhone 15 Pro", "category": "Electronics", "price": 999.99},
        {"id": 2, "name": "MacBook Air", "category": "Electronics", "price": 1099.99},
        {"id": 3, "name": "Nike Air Max", "category": "Footwear", "price": 149.99},
        {"id": 4, "name": "Adidas Ultraboost", "category": "Footwear", "price": 179.99},
        {"id": 5, "name": "Levi's Jeans", "category": "Clothing", "price": 69.99},
        {"id": 6, "name": "Nike T-Shirt", "category": "Clothing", "price": 29.99}
    ]
    
    customers = [
        {"id": 1, "first_name": "Rahul", "last_name": "Sharma", "email": "rahul@email.com"},
        {"id": 2, "first_name": "Priya", "last_name": "Patel", "email": "priya@email.com"},
        {"id": 3, "first_name": "Amit", "last_name": "Singh", "email": "amit@email.com"}
    ]
    
    orders = [
        {"id": 1, "customer_id": 1, "total_amount": 1099.98},
        {"id": 2, "customer_id": 1, "total_amount": 69.99},
        {"id": 3, "customer_id": 2, "total_amount": 179.99}
    ]
    
    @classmethod
    def above_avg_products(cls):
        """Products above average price"""
        avg_price = sum(p["price"] for p in cls.products) / len(cls.products)
        return [p for p in cls.products if p["price"] > avg_price]
    
    @classmethod
    def category_avg_comparison(cls):
        """Compare each product to category average"""
        categories = {}
        for p in cls.products:
            if p["category"] not in categories:
                categories[p["category"]] = []
            categories[p["category"]].append(p["price"])
        
        category_avg = {cat: sum(prices)/len(prices) for cat, prices in categories.items()}
        
        results = []
        for p in cls.products:
            avg = category_avg[p["category"]]
            results.append({
                "name": p["name"],
                "category": p["category"],
                "price": p["price"],
                "category_avg": avg,
                "diff": p["price"] - avg
            })
        return results
    
    @classmethod
    def customers_with_orders(cls):
        """Customers who have placed orders (EXISTS)"""
        customer_ids = set(o["customer_id"] for o in cls.orders)
        return [c for c in cls.customers if c["id"] in customer_ids]
    
    @classmethod
    def customers_without_orders(cls):
        """Customers with no orders (NOT EXISTS)"""
        customer_ids = set(o["customer_id"] for o in cls.orders)
        return [c for c in cls.customers if c["id"] not in customer_ids]
    
    @classmethod
    def customer_stats(cls):
        """Customer order statistics"""
        stats = []
        for c in cls.customers:
            customer_orders = [o for o in cls.orders if o["customer_id"] == c["id"]]
            stats.append({
                "name": f"{c['first_name']} {c['last_name']}",
                "order_count": len(customer_orders),
                "total_spent": sum(o["total_amount"] for o in customer_orders)
            })
        return stats

print("\n1. PRODUCTS ABOVE AVERAGE PRICE")
print("-" * 40)

avg_price = sum(p["price"] for p in ECommerceDB.products) / len(ECommerceDB.products)
print(f"Average price: ${avg_price:.2f}")

results = ECommerceDB.above_avg_products()
for p in results:
    print(f"  {p['name']} - ${p['price']}")

print("\n2. PRODUCT VS CATEGORY AVERAGE")
print("-" * 40)

results = ECommerceDB.category_avg_comparison()
for r in results:
    print(f"  {r['name']} - {r['category']} - ${r['price']} | Avg: ${r['category_avg']:.2f} | Diff: ${r['diff']:.2f}")

print("\n3. CUSTOMERS WITH ORDERS (EXISTS)")
print("-" * 40)

results = ECommerceDB.customers_with_orders()
for c in results:
    print(f"  {c['first_name']} {c['last_name']} - {c['email']}")

print("\n4. CUSTOMERS WITHOUT ORDERS (NOT EXISTS)")
print("-" * 40)

results = ECommerceDB.customers_without_orders()
for c in results:
    print(f"  {c['first_name']} {c['last_name']} - {c['email']}")

print("\n5. CUSTOMER ORDER STATISTICS")
print("-" * 40)

results = ECommerceDB.customer_stats()
for r in results:
    print(f"  {r['name']} - Orders: {r['order_count']} | Total: ${r['total_spent']}")

print("\n6. SUBQUERY SUMMARY")
print("-" * 40)
print("""
┌─────────────────┬──────────────────────────────────────────────┐
│ Subquery Type   │ Use Case                                    │
├─────────────────┼──────────────────────────────────────────────┤
│ WHERE           │ Filter using another query's results       │
│ SELECT          │ Calculate values for each row              │
│ FROM            │ Treat query results as a table             │
│ EXISTS          │ Check if a subquery returns rows           │
│ Correlated      │ Reference outer query for row-by-row calc  │
└─────────────────┴──────────────────────────────────────────────┘
""")

print("Subqueries answer complex business questions!")

========================================
SUBQUERIES - PRACTICE
========================================

1. PRODUCTS ABOVE AVERAGE PRICE
----------------------------------------
Average price: $584.99
iPhone 15 Pro - $999.99
MacBook Air - $1099.99

2. PRODUCT VS CATEGORY AVERAGE
----------------------------------------
iPhone 15 Pro - Electronics - $999.99 | Avg: $1049.99 | Diff: $-50.00
MacBook Air - Electronics - $1099.99 | Avg: $1049.99 | Diff: $50.00
Nike Air Max - Footwear - $149.99 | Avg: $164.99 | Diff: $-15.00
Adidas Ultraboost - Footwear - $179.99 | Avg: $164.99 | Diff: $15.00
Levi's Jeans - Clothing - $69.99 | Avg: $49.99 | Diff: $20.00
Nike T-Shirt - Clothing - $29.99 | Avg: $49.99 | Diff: $-20.00

3. CUSTOMERS WITH ORDERS (EXISTS)
----------------------------------------
Rahul Sharma - rahul@email.com
Priya Patel - priya@email.com

4. CUSTOMERS WITHOUT ORDERS (NOT EXISTS)
----------------------------------------
Amit Singh - amit@email.com

5. CUSTOMER ORDER STATISTICS
----------------------------------------
Rahul Sharma - Orders: 2 | Total: $1169.97
Priya Patel - Orders: 1 | Total: $179.99
Amit Singh - Orders: 0 | Total: $0

6. SUBQUERY SUMMARY
----------------------------------------
┌─────────────────┬──────────────────────────────────────────────┐
│ Subquery Type │ Use Case │
├─────────────────┼──────────────────────────────────────────────┤
│ WHERE │ Filter using another query's results │
│ SELECT │ Calculate values for each row │
│ FROM │ Treat query results as a table │
│ EXISTS │ Check if a subquery returns rows │
│ Correlated │ Reference outer query for row-by-row calc │
└─────────────────┴──────────────────────────────────────────────┘

Subqueries answer complex business questions!

🏆

You've Got It!

You now understand subqueries in MySQL from Python. You can use subqueries in WHERE, SELECT, FROM, and with EXISTS for complex E-Commerce queries.

Quick Quiz

Test what you've learned:

1. What is a subquery?

A) A query that runs faster B) A query nested inside another query C) A query that uses JOIN D) A query with multiple columns

2. What does EXISTS check?

A) If a value matches B) If a subquery returns any rows C) If a value is NULL D) If a table exists

3. What is a correlated subquery?

A) A subquery that runs once B) A subquery that references the outer query C) A subquery with multiple tables D) A subquery with GROUP BY

4. When should you use EXISTS instead of IN?

A) Never, they are the same B) For large datasets when checking existence C) When you need to return values D) When using ORDER BY

5. What is a derived table?

A) A table created with CREATE TABLE B) A subquery in the FROM clause C) A table with derived columns D) A temporary table

Frequently Asked Questions

What is the difference between IN and EXISTS?

IN checks if a value matches any value in a result set. EXISTS checks if a subquery returns any rows. EXISTS is often faster for large datasets because it stops at the first match.

When should I use JOIN instead of a subquery?

Use JOIN when you need to combine data from multiple tables. Use subqueries when you need to filter based on aggregated values or check for existence. JOINs are often more efficient for large datasets.

What is a common interview question about subqueries?

Common questions include: "Explain the difference between IN and EXISTS," "What is a correlated subquery?" and "How do you find products above average price?"

Why are correlated subqueries slow?

Correlated subqueries run once for each row in the outer query. For large tables, this can be very slow. Try to rewrite as JOINs or use other approaches when possible.

Will this conflict with future pure MySQL tutorials?

No. This tutorial focuses on executing subqueries 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 subqueries, check out these related topics:

Previous: DML Statements Next: JOIN Operations →

Python

Python: Subqueries in MySQL

What are Subqueries?

WHERE Clause

SELECT Clause

FROM Clause

EXISTS

Subqueries in E-Commerce

Subqueries in WHERE

Filtering with Nested Queries

Subqueries in SELECT

Calculated Columns with Subqueries

Subqueries in FROM

Derived Tables

EXISTS and NOT EXISTS

Checking for Existence

Correlated Subqueries

Queries That Depend on the Outer Query

E-Commerce Python Examples

Complete E-Commerce Subquery Examples

Best Practices

Subquery Best Practices

Try It Yourself

You've Got It!

Quick Quiz

Frequently Asked Questions

What is the difference between IN and EXISTS?

When should I use JOIN instead of a subquery?

What is a common interview question about subqueries?

Why are correlated subqueries slow?

Will this conflict with future pure MySQL tutorials?

Where to Go From Here

JOIN Operations

Aggregation

Case Study

Python

Python: Subqueries in MySQL

What are Subqueries?

WHERE Clause

SELECT Clause

FROM Clause

EXISTS

Subqueries in E-Commerce

Subqueries in WHERE

Filtering with Nested Queries

Subqueries in SELECT

Calculated Columns with Subqueries

Subqueries in FROM

Derived Tables

EXISTS and NOT EXISTS

Checking for Existence

Correlated Subqueries

Queries That Depend on the Outer Query

E-Commerce Python Examples

Complete E-Commerce Subquery Examples

Best Practices

Subquery Best Practices

Try It Yourself

You've Got It!

Quick Quiz

Frequently Asked Questions

What is the difference between IN and EXISTS? ▼

When should I use JOIN instead of a subquery? ▼

What is a common interview question about subqueries? ▼

Why are correlated subqueries slow? ▼

Will this conflict with future pure MySQL tutorials? ▼

Where to Go From Here

JOIN Operations

Aggregation

Case Study

What is the difference between IN and EXISTS?

When should I use JOIN instead of a subquery?

What is a common interview question about subqueries?

Why are correlated subqueries slow?

Will this conflict with future pure MySQL tutorials?