[go: up one dir, main page]
Scribd
Upload
98 views4 pages

Subqueries and CTEs

The document discusses the differences between subqueries and Common Table Expressions (CTEs) in SQL. While CTEs are praised for their readability and reusability, the document argues that subqueries can be more readable for simple queries as they maintain the flow of the main query. Ultimately, the choice between using a subquery or a CTE depends on the complexity of the SQL statement being constructed.

Uploaded by

prabha kasani
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
98 views4 pages

Subqueries and CTEs

The document discusses the differences between subqueries and Common Table Expressions (CTEs) in SQL. While CTEs are praised for their readability and reusability, the document argues that subqueries can be more readable for simple queries as they maintain the flow of the main query. Ultimately, the choice between using a subquery or a CTE depends on the complexity of the SQL statement being constructed.

Uploaded by

prabha kasani
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

An argument

for Subqueries
over CTEs
SQL (3 pages)
What is a Subquery?
is a nested query which immediately
returns a result-set for further use.

SELECT *
FROM (
SELECT *
FROM table t
WHERE type = ‘some_type’
) sub

What is a CTE?
A temporary result-set saved into memory
for the duration of the query.
WITH cte AS (
SELECT *
FROM table
WHERE type = ‘some_type’
)

SELECT *
FROM cte
Main reasons why CTEs
are considered to be
better
Overall CTEs have a better reputation than
subqueries and here are the main reasons:

1.) can be defined once and used multiple


times in the same query.

2.) separate complex pieces of SQL into


their own isolated area, which improves
readability.
Argument for a
subquery over a CTE
While CTEs are generally more powerful
and beneficial, they aren't better in every
situation.

For simple queries a subquery is more


readable then a CTE!

doesn’t break the flow of reading the


entire query as it definition is inline

SELECT <cols>
FROM table1 t1
WHERE id IN ( SELECT id FROM table2 ) sub

SELECT <cols>
FROM table1 t1
JOIN (
SELECT *
FROM table2 t2
WHERE type = ‘some_type’
) sub
ON t1.col = sub.col

You might also like