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
WHERE Expression 
) AS SQLSUB
GROUP BY SQLSUB.XXX

Example  nested SELECT in WHERE Clause

SELECT 
Column1, Column2, Column3, ...
  FROM SpiraTestEntities.R_SampleView AS CustomName
  WHERE 
! EXISTS
  (SELECT 
    SampleColumn FROM  SpiraTestEntities.R_XXX
    INNER JOIN SpiraTestEntities.R_Sample AS XYZ ON Expression)
Expression (Optional)

Example  nested SELECT in JOIN Clause

SELECT
 Column1, Column2, Column3 
FROM
  SpiraTestEntities.R_SampleView AS XXX
JOIN
  (
    SELECT COUNT(0) as ColumnY,
    ColumnZ
    FROM SpiraTestEntities.R_ABC AS ABC
	JOIN Expression
    WHERE Expression
	GROUP BY Expression
 ) AS SampleName on Expression

In Entity SQL, nested queries must always be enclosed in parentheses:

( SELECT … FROM … )
UNION ALL
​​​​​​​( 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().

SELECT (
ANYELEMENT(SELECT VALUE ....)
)

As an example of using select value clause​​​​​​​ shown below:

select ... 
from SpiraTestEntities.R_TestCases as TC
join ...
where ....
... in (select value
ColumnName from SpiraTestEntities.R_... as R 
where ...)

 

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