SQL Query Explainer Skill

📝 Query Summary

This query finds active (non-discontinued) products that are running low on stock in a specific warehouse, showing each product's name, category, and price.

🔍 Step-by-Step Breakdown

Data Retrieval: Pulls

product_name and

price from the

products table (aliased p), along with

category_name from the

categories table (aliased c).
*

Relationships: Uses an

INNER JOIN to match each product to its category via p.category_id = c.id. Only products with a matching category row will appear — if a product had a category_id that doesn't exist in

categories, it would be silently excluded.

• Filtering & Grouping: Two filtering mechanisms are at work:
  • A

subquery against the

inventory table finds all product_ids where stock_level < 10

and warehouse_id = 4, then the outer query keeps only products whose p.id appears in that list (low stock in warehouse 4).
* An additional AND p.is_discontinued = 0 excludes discontinued products.
* There is no GROUP BY or HAVING — this is a row-level filter, not an aggregation.
*

Sorting & Limits: None specified — results will return in whatever order the database engine chooses (typically related to physical storage or join order), which isn't guaranteed to be consistent.

⚡ Performance Notes & Bottlenecks

Uncorrelated subquery with IN: The WHERE p.id IN (SELECT product_id FROM inventory ...) pattern can be inefficient on some optimizers, especially on large

inventory tables, since the subquery may need to build and scan a full result set of matching product_ids before the outer filter runs.
*

Missing/unknown indexes: Performance hinges on whether there are indexes on

inventory(warehouse_id, stock_level),

inventory(product_id),

products(category_id), and

products(id). Without a composite index on (warehouse_id, stock_level), the subquery likely triggers a full table scan of

inventory.
*

No result ordering: Not a performance issue per se, but worth flagging since consumers of this query may assume a stable order that isn't guaranteed.
*

Boolean/flag column scan: is_discontinued = 0 on a low-cardinality column is rarely selective — filtering on it alone won't use an index effectively (though it's cheap to evaluate, so this is a minor note, not a real bottleneck).

🛠️ Suggested Optimizations

Rewrite the subquery as a JOIN: Replace the IN (SELECT ...) with an INNER JOIN (or EXISTS) against

inventory, filtered on warehouse_id = 4 AND stock_level < 10. Most modern optimizers handle this well already, but an explicit join can make the execution plan more predictable and easier to tune:
sql SELECT p.product_name, c.category_name, p.price FROM products p INNER JOIN categories c ON p.category_id = c.id INNER JOIN inventory i ON i.product_id = p.id WHERE i.stock_level < 10 AND i.warehouse_id = 4 AND p.is_discontinued = 0;
*

Add a composite index on inventory(warehouse_id, stock_level, product_id) to let the engine seek directly to the relevant rows rather than scanning the whole table.
*

Ensure supporting indexes exist on products.category_id (for the join to categories) and products.id/categories.id (likely already primary keys, but worth confirming).

• Add an explicit ORDER BY (e.g., by product_name) if consumers of this result expect consistent ordering.

Consider a covering index on

inventory including product_id alongside warehouse_id and stock_level if this query runs frequently, to avoid extra lookups back to the base table.