Coginiti menu Coginiti menu


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 COUNT(*) FROM orders WHERE customer_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.
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.
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.
  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.