Correlated Subqueries And Common Table Expressions (CTEs)

Correlated subqueries and Common Table Expressions (CTEs) are advanced SQL techniques that enable you to write more readable and powerful queries. This post explains how correlated subqueries reference the outer query for row-by-row processing, how CTEs use the WITH clause to create named temporary result sets, and how recursive CTEs can traverse hierarchical data.

Subqueries

• A subquery is a query that is nested inside a SELECT, INSERT, UPDATE or DELETE statement.
• A subquery may occur anywhere an expression is allowed, such as:
  • A SELECT clause
  • A FROM clause
  • A WHERE clause
  • A HAVING clause
  • Etc.
• Subqueries must be enclosed in parenthesis
• The inner-most query is executed first
• All sub-queries follow the SQL query order of execution

Scalar Subqueries

• A scalar subquery expression is a subquery that returns exactly one column value from one row.
  • As default values for columns 
  • As hash expressions for clusters
  • In the RETURNING clause of DML statements
  • As the basis of a function-based index
  • In CHECK constraints
  • In WHEN conditions of CASE expressions
  • In GROUP BY and HAVING clauses
  • In START WITH and CONNECT BY clauses
  • In statements that are unrelated to queries, such as CREATE PROFILE

select * from emp

where sal >  

(select avg(sal) from emp);

Correlated Subqueries

• A correlated subquery contains a reference to a table in the outer query
• In a normal subquery the inner subqueries run first and execute once, they return values for the outer subquery
• In a correlated subquery, the subquery is executed once for each row in the outer query
• Correlated subqueries can be used when the subquery needs to return a different result for each row in the outer query
• Correlated subqueries can be used with logical operators(<,>,=...) and IN, ANY, ALL operators

select *

from eba_countries a

where a.intermediate_region_id = (select b.id from eba_country_intermediates b where a.intermediate_region_id = b.id);

Where Exists / Not Exists

• WHERE EXISTS is used to test the existence of any record in a subquery, and returns TRUE if the record exists
• WHERE NOT EXISTS is used to test the non-existence of any record in a subquery, returns TRUE if the record doesn't exist
• Usually used with correlated subqueries

select *

from eba_countries a

where exists (select b.id from eba_country_intermediates b where a.intermediate_region_id = b.id);

select *

from eba_countries a

where not exists (select b.id from eba_country_intermediates b where a.intermediate_region_id = b.id);

CTE

• A CTE(Common Table Expression) is a temporary result set that you can reference in your SELECT, INSERT, UPDATE or DELETE statement
• Because it is stored in the temporary tablespace it is returned from the temporary table rather than the base tables making it more efficient in some situations such as when the CTE is being used more than once

WITH CTE_NAME (COLUMN(S)) AS (CTE SELECT_STATEMENT) 

SELECT COLUMN(S) FROM CTE_NAME;

with cte_emp (ename, empno, sal, deptno)

as

(select ename, empno, sal, deptno from emp where sal>2000)

select * from cte_emp;

with cte_emp (ename, empno, sal, deptno)

as

(select ename, empno, sal, deptno from emp where sal>2000),

cte_dept(deptno)

as 

(select deptno from dept where deptno in (10,20))

select a.* from cte_emp a join cte_dept b on a.deptno = b.deptno;

Recursive CTE

• A recursive CTE has one subquery that refers to the CTE itself
• Recursive CTEs enable you to process hierarchical data and is an alternative to Hierarchical Queries

CTE Evaluation

CTE Considerations

• You must be careful to ensure that your recursive CTE does not end up in an infinite loop

WITH CTE_EMP (EMPNO, ENAME, MGR)

AS (

SELECT EMPNO, ENAME, MGR FROM EMP WHERE ENAME IN (‘BLAKE’,’CLARK’)

UNION ALL

SELECT EMP.EMPNO, EMP.ENAME, EMP.MGR FROM CTE_EMP JOIN EMP ON CTE_EMP.EMP = EMP.EMP

)

SELECT * FROM CTE_EMP

WITH CTE_EMP (N)

AS (

SELECT N FROM SCALAR_TABLE

UNION ALL

SELECT N+1 FROM CTE_EMP

)

SELECT * FROM CTE_EMP

Frequently Asked Questions

What is a Common Table Expression (CTE) in SQL?

A CTE is a named temporary result set defined with the WITH clause before the main SELECT statement; it improves query readability by breaking complex queries into named, reusable parts and can be referenced multiple times in the same query.

What is the difference between a subquery and a correlated subquery?

A regular subquery executes once and its result is used by the outer query; a correlated subquery references columns from the outer query and is re-executed for each row processed by the outer query, making it powerful but potentially slower for large datasets.