sqlintermediate10 minutes

Refactor SQL Query for Improved Performance Using Index-Friendly Joins

You are given a SQL query that fetches customer orders with detailed product information, but the current query is slow and inefficient. Refactor the query to optimize performance while keeping the same result set.

Challenge prompt

The provided query joins three tables: customers, orders, and products to show customer_name, order_id, product_name, and order_date. However, it uses nested subqueries and non-sargable where clauses, leading to poor performance. Refactor the given query to improve readability and optimize execution without changing the output. Explain your improvements.

Guidance

• Replace subqueries used in the FROM or WHERE clauses with explicit JOIN statements.
• Eliminate non-sargable expressions in WHERE clauses to leverage indexing effectively.
• Use table aliases consistently for readability and shorter code.
• Preserve the same output columns and filtering conditions.

Hints

• Look for subqueries that can be flattened into JOINs to improve query planner choices.
• Avoid using functions or calculations directly on indexed columns in WHERE conditions.
• Check the join types carefully to maintain the logical data relationship.

Starter code

SELECT c.customer_name, o.order_id, p.product_name, o.order_date
FROM customers c
WHERE c.customer_id IN (
  SELECT o.customer_id
  FROM orders o
  WHERE o.order_id IN (
    SELECT order_id
    FROM order_items oi
    WHERE oi.product_id IN (
      SELECT product_id
      FROM products p
      WHERE p.category = 'Electronics'
    )
  )
)

Expected output

A list of customer names with their order IDs, product names for products in 'Electronics' category, and the order dates, matching the original logic but returned in a more efficient and readable way.

Core concepts

SQL JOIN optimizationQuery refactoringIndex utilization

Challenge a Friend

Send this duel to someone else and see if they can solve it.