A subquery is a query within another query, used to retrieve data needed for further analysis within a parent query. There are several types of subqueries in SQL, each with its own specific use cases:
1. Scalar Subquery: A scalar subquery is a subquery that returns a single value, which is used in parent query like any other column. Scalar subqueries are typically used for calculations within a SELECT statement or as a parameter for a function within a WHERE clause.
Example:
SELECT customer_name,
(SELECT AVG(transaction_amount) FROM transactions WHERE customer_id = customers.id) AS average_transaction_amount
FROM customers;2. Single Row Subquery: Single row subqueries return only a single row and are typically used to check for the existence of data or retrieve a single value to be compared with a value in the parent query.
Example:
SELECT customer_name
FROM customers
WHERE customer_id = (SELECT customer_id FROM transactions WHERE transaction_id = 12345);3. Multiple Row Subquery: Multiple row subqueries return multiple rows, and are used to retrieve data from one or more tables based on a condition in the parent query.
Example:
SELECT customer_name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM transactions WHERE transaction_amount > 1000);4. Correlated Subquery: A correlated subquery is a subquery that references a column from a table in the parent query. Correlated subqueries are typically used to filter data based on conditions in another table or to perform calculations based on data in related tables.
Example:
SELECT customer_name,
(SELECT COUNT(*) FROM transactions WHERE transactions.customer_id = customers.customer_id) AS transaction_count
FROM customers;Overall, subqueries are a powerful tool in SQL that allow for complex data retrieval and manipulation, and understanding the different types of subqueries and their use cases can significantly improve query performance and accuracy.