What is subquery in SQL?
A subquery is a query nested inside a larger query.
It may also called an inner query or inner select, while the statement containing a subquery is also called an outer query or outer select.
A subquery usually occur in :
- FROM clause
- WHERE clause
- JOIN clause
Example nested SELECT in FROM Clause
SELECT SQLSUB.XXX, SUM (SQLSUB.YYY) AS Total_Count
FROM (SELECT Expression AS XXX,
Expression AS YYY
FROM SpiraTestEntities.R_SampleView AS CustomColumn
) AS SQLSUB
GROUP BY SQLSUB.XXX
Example nested SELECT in WHERE Clause
Column1, Column2, Column3, ...
FROM SpiraTestEntities.R_SampleView AS CustomName
SampleColumn FROM SpiraTestEntities.R_XXX
INNER JOIN SpiraTestEntities.R_Sample AS XYZ ON Expression)
Example nested SELECT in JOIN Clause
Column1, Column2, Column3
SpiraTestEntities.R_SampleView AS XXX
SELECT COUNT(0) as ColumnY,
FROM SpiraTestEntities.R_ABC AS ABC
GROUP BY Expression
) AS SampleName on Expression
In Entity SQL, nested queries must always be enclosed in parentheses:
( SELECT … FROM … )
( SELECT … FROM … )
It's necessary to mention that Entity SQL provides the
select value clause to skip the implicit row construction - In order to return a scalar subquery it needs to use the function ANYELEMENT().
ANYELEMENT(SELECT VALUE ....)
As an example of using
select value clause shown below:
from SpiraTestEntities.R_TestCases as TC
... in (select value
ColumnName from SpiraTestEntities.R_... as R
Nested queries in the project clause might get translated into Cartesian product queries on the server.
This can cause the temporary table to get very large, which can affect server performance so use it carefully:
SELECT c, (SELECT c, (SELECT c FROM SpiraTestEntities.R_XXX AS c )
As Inner2 FROM SpiraTestEntities.R_YYY AS c )
As Inner1 FROM SpiraTestEntities.R_ZZZ AS c
It is possible to write a query that contains an ordering of nested queries.
However, the order of a nested query being ignored and requires ordering in outer query to get the result:
SELECT C2.FirstName, C2.LastName
FROM (SELECT C1.FirstName, C1.LastName
FROM Sample.Contact as C1
ORDER BY C1.LastName) as C2