Subqueries in SQL
Subqueries, also known as nested queries, are queries embedded within another query. They can be used to retrieve data from multiple tables, perform advanced filtering, or calculate aggregate functions.
Example Syntax
SELECT column_name
FROM table_name
WHERE column_name IN (SELECT column_name FROM another_table WHERE condition);
Types of Subqueries
- Single Row Subquery: Returns only one row and one column.
- Multiple Row Subquery: Returns multiple rows but only one column.
- Multiple Column Subquery: Returns multiple rows and columns.
- Correlated Subquery: Uses values from the outer query in the inner query.
Example
SELECT product_name
FROM products
WHERE category_id IN (SELECT category_id FROM categories WHERE category_name = 'Electronics');
This query retrieves product names from the products
table where the category is 'Electronics', using a subquery to find the corresponding category IDs.
Subqueries are a powerful feature in SQL that allow for more complex and flexible queries, enabling you to perform advanced data retrieval and manipulation tasks.