Subqueries in SQL

CodingDatabases One can create temporary queries which return results that are then utilized in a larger query using two primary methods:

1. WITH - create a Common Table Expression (CTE) that can be referred to in the main query. Can be used recursively by using an INNER JOIN on the table of interest with the recursive CTE and the UNION ALL clause to join all recursive subqueries
2. General subquery where the return result is used in a SELECT statement, conditional, WHERE clause, or even to retrieve data from the main query (correlated subquery)

A generic CTE might look like:

WITH cte AS (
	SELECT column
	FROM table
)
SELECT cte.column
FROM cte

and a recursive CTE:

WITH recursive_cte AS (
	SELECT column
	FROM table
	WHERE column = @column

	UNION ALL

	SELECT t.column
	FROM table AS t
	INNER JOIN recursive_cte AS r
		ON t.column = r.column
)

SELECT *
FROM recursive_cte;

Defining a CTE is useful when reusing the same subquery multiple times.

Some generic subqueries might look like:

SELECT 
	column,
	(SELECT MAX(column) FROM table) AS subquery_result
FROM table
WHERE column > (
	SELECT MAX(column) FROM table
)
OR column IN (
	SELECT column FROM table WHERE condition
);

and a correlated subquery:

SELECT 
	column
FROM table AS table1
WHERE column = (
	SELECT MAX(column) 
	FROM table AS table2
	WHERE table1.column = table2.column
);

where the above can be viewed as a subquery that provides details about each returned value in the main query.