Subqueries are queries nested inside other queries. Essential for complex data retrieval and comparisons.

Subquery in WHERE

SELECT * FROM products

WHERE price > (SELECT AVG(price) FROM products);

Subquery with IN

SELECT * FROM customers

WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000);

Subquery in SELECT

SELECT name,

(SELECT COUNT(*) FROM orders WHERE orders.customer_id = customers.id) as order_count

FROM customers;

Subquery in FROM (Derived Table)

SELECT category, avg_price

FROM (

SELECT category, AVG(price) as avg_price

FROM products

GROUP BY category

) as category_stats

WHERE avg_price > 50;

EXISTS Subquery

SELECT * FROM customers c

WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Correlated Subquery

SELECT * FROM products p

WHERE price > (SELECT AVG(price) FROM products WHERE category = p.category);

Generate Subqueries

Complex queries are easy with AI2sql.