Subqueries, also known as inner queries or nested queries, are used within a SQL statement to perform complex operations.
Types of Subqueries:
Scalar Subqueries: Return a single value.
Use case: To get the number of orders for each customer.
SELECT name, (SELECT COUNT(*) FROM orders WHERE customer_id = customers.id) AS order_count
FROM customers;Column Subqueries: Return a single column of values.
Use case: To find all customers who have placed orders.
SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders);Row Subqueries: Return a single row of values.
Use case: To find the product with the minimum price in each category.
SELECT * FROM products WHERE (price, category) = (SELECT MIN(price), category FROM products);Table Subqueries: Return a table of values.
Use case: To join a summarized sales table with customer details.
SELECT name, total_sales FROM customers
JOIN (SELECT customer_id, SUM(amount) AS total_sales FROM orders GROUP BY customer_id) AS sales ON customers.id = sales.customer_id;Nested Subqueries: Nested subqueries are used inside another subquery.
Use case: To find customers who have placed orders with amounts above the average order amount.
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE amount > (SELECT AVG(amount) FROM orders));Correlated subqueries: This are queries that reference columns from the outer query.
Use case: To find customers who have placed orders greater than $100.
SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.amount > 100);
By mastering SQL subqueries, you’ll be able to handle more complex queries and extract meaningful insights from your data. For more detailed examples and use cases, read our full guide and elevate your SQL proficiency!
For more on SQL, read on SQL basic syntax, data constraints, aggregate functions and more.
