Sub Query - Nested or Inner Query

A subquery, also known as a nested or inner query, is a query within another SQL query. It is used to further manipulate the data returned by the main query, filtering results based on more complex conditions, or even modifying the data before it is returned.

Subqueries can return various types of data, like a single value, a row, a column, or a table, and they are often used in the WHERE and HAVING clauses of the SQL SELECT statement.

Here is an example of a subquery:

SELECT CustomerName 
FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders WHERE Amount > 500);

In this example, the subquery (inside the parentheses) selects the IDs of all customers from the Orders table who have an order amount greater than 500. The main query then selects the names of customers whose IDs are in that list. So the end result is a list of customers who have placed orders greater than 500.

Subqueries can be nested at various levels and can become quite complex. They can be correlated (where the subquery references a column from the outer query, meaning the subquery has to be re-run for each row of the outer query) or non-correlated (where the subquery can be run just once and the results used by the outer query).