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);