SQL subqueries are known as nested or inner queries because they are nested within another query. They are used to retrieve data from one or more tables based on a condition that involves data from another table in the same or a different database. Subqueries are held within parentheses in various parts of a SQL statement, such as the SELECT, FROM, WHERE, and HAVING clauses.
Some common subqueries and syntax examples are:
- Scalar subqueries return a single value and are often used in the SELECT clause to retrieve a calculated value.
SELECT name, (SELECT COUNT(*) FROM orders WHERE customer_id = customers.id) AS order_count FROM customers;
The “SELECT” statement selects two columns from the customers table: name and the subquery. The subquery is enclosed in parentheses and determines the count of all rows in the orders table where the customer_id column matches the id column in the customers table. The AS keyword renames the subquery result as order_count.
- Single-row subqueries return a single row of data and are usually used in the WHERE clause to filter the main query results.
SELECT * FROM products WHERE price = (SELECT MAX(price) FROM products);
This query retrieves all products with the highest price, defined by a single-row subquery that returns the maximum price from the same table.
- Multi-row subqueries return multiple data rows used in the FROM clause to create a derived table in the main query.
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE state = 'California');
This query returns all orders placed by customers who live in California, which is determined by a multi-row subquery that returns a list of customer IDs from the customers table.
- Correlated subqueries refer to a column from the outer query and often appear with the WHERE clause.
SELECT order_id, order_date, customer_id, (SELECT COUNT(*) FROM order_items WHERE order_items.order_id = orders.order_id) AS num_items FROM orders;
Here’s a breakdown of how the query above works:
- The SELECT statement selects four columns from the orders table: order_id, order_date, customer_id, and the subquery result as num_items.
- The subquery selects the count of all rows in the order_items table where the order_id column in the order_items table matches the order_id column in the orders table.
- The AS keyword renames the result of the subquery as num_items.