SQL Subqueries
A subquery supplies data to another query. It can produce one value, a list, or a table-shaped result. Choose the form that matches the question, and check how many rows the surrounding expression expects.
Understand the idea
This scalar subquery calculates the average product price. The outer query compares each product with that one value. The subquery is not correlated because it does not refer to the outer product row. Other subqueries can depend on the current outer row, which is useful but may require more attention to the execution plan.
Where to try it
Run this example in the SQL playground. The lesson link loads the shop tables and sample rows before this query. Select Run when you are ready. Every run starts fresh, so your changes do not affect the website or a previous run.
SQLite sample data · MySQL / MariaDB sample data · See the sample tables
A worked example
SELECT name, price_cents
FROM products
WHERE price_cents > (SELECT AVG(price_cents) FROM products)
ORDER BY price_cents, product_id;
Read the result
The mean price is 850 cents. Canvas bag at 900 and Desk lamp at 1800 are above it. The average is calculated from the same four-product dataset used by the outer query.
Try one change
Find orders whose total is above the average paid-order total. Decide whether the outer query should also require paid status.