SQL Subquery

In SQL, a subquery is a query that is nested inside another query, and is used to retrieve data that will be used in the main query. The result of the subquery is used as a condition or a value in the main query.

Subqueries can be used in various parts of a SQL statement, such as in the SELECT, FROM, WHERE, and HAVING clauses. The syntax for a subquery varies depending on the context in which it is used.

Here is an example of a subquery used in the WHERE clause:

SELECT product_name, unit_price
FROM products
WHERE unit_price > (SELECT AVG(unit_price) FROM products);

In this example, the subquery (SELECT AVG(unit_price) FROM products) calculates the average unit price of all products in the "products" table. The main query retrieves the names and unit prices of all products whose unit price is higher than the average unit price.

Subqueries can also be used in the SELECT clause to retrieve a single value or a set of values, as shown in the following example:

SELECT product_name, unit_price, (SELECT category_name FROM categories WHERE category_id = products.category_id) AS category
FROM products;

In this example, the subquery (SELECT category_name FROM categories WHERE category_id = products.category_id) retrieves the name of the category to which each product belongs. The main query retrieves the names, unit prices, and categories of all products in the "products" table.

Subqueries can be powerful tools for retrieving complex data and performing calculations. However, they can also slow down the performance of a query, so it is important to use them judiciously and optimize them when possible.