Subqueries

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:

1. The SELECT statement selects four columns from the orders table: order_id, order_date, customer_id, and the subquery result as num_items.
2. 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.
3. The AS keyword renames the result of the subquery as num_items.