What is a Subquery
A SQL Subquery(also called an inner query or inner select) is a sql query that is nested inside a statement(SELECT, INSERT, UPDATE, or DELETE), or inside another subquery.
Subquery basics
The statements that include a SQL Subquery usually use logical operators(in, exists, all, any or some):
WHERE expression [NOT] IN (subquery)
WHERE expression comparison_operator [ANY | ALL | SOME] (subquery)
WHERE [NOT] EXISTS (subquery)
Rules for adding a subquery
A subquery in SQL Server database comes with the following restrictions that must be followed for a successful addition to the outer query.
Only one expression or column name can be added to the list of a subquery introduced with a comparison operator.
When the WHERE clause of a larger query consists of a column name, it shall be join-compatible with the subquery select list column.
The select list 0 of subqueries does not accommodate the kinds of text, image, and text data.
The subquery that an untainted comparison operator adds cannot have clauses of GROUP BY or HAVING. The reason is that they must give back a single value.
To those subqueries that include GROUP BY, the DISTINCT keyword cannot be added.
The clauses COMPUTE and INTO clauses cannot be defined.
Only when the top is defined ORDER BY can be defined.
When a view is created by using subquery, it cannot be updated.
Subquery examples
IN
The IN operator verify that the value in a specified column matches any value in a subquery or list.
select * from EMPLOYEES E
where E.ID IN (select s.SalesPersonID from Sales s);
ANY
The ANY operator compares a scalar value with a single-column set of values.
select * from Customers c
where c.ID = ANY (select s.CustomerID from Sales s);
SOME
The SOME operator is equivalent with ANY operator.
select * from Students s
where s.id =SOME (select l.Student_id from Library l);
EXISTS
The Transact-SQL EXISTS operator specifies a subquery to check for the existence of rows.
select * from Students s
where EXISTS (select * from Students_Math m where m.id=s.id);
NOT EXISTS
The Transact-SQL NOT EXISTS operator specifies a subquery to check for the not existence of rows.
select * from Students s
where NOT EXISTS (select * from Students_Math m where m.id=s.id);
Correlated Subquery
In SQL Server, a correlated subquery is a type of subquery that refers to a column from the outer query, and as a result, the subquery's results are dependent on the values in the outer query. This type of subquery is also known as a dependent subquery.
A correlated subquery is typically used to retrieve data from one or more related tables based on the data in the current row being processed in the outer query. It allows for more complex queries to be performed, and can be particularly useful when dealing with large datasets.