Subquery vs EXISTS vs CTE?
Practice this questionWANT TO UNDERSTAND THE CONCEPT FIRST?Learn Subquery vs EXISTS vs CTE?
Definition:
A subquery supplies a result inside another query, EXISTS checks whether a matching result exists, and a CTE gives a named query expression that can be referenced later. These are ways to structure SQL and express relationships. The optimizer and data determine performance, so syntax alone does not tell you which is fastest.
Key points:
-
A subquery is a query inside another query.
-
EXISTSchecks whether at least one matching row exists. -
A CTE names a query step; it improves readability but is not automatically faster.
-
Use EXISTS when only existence matters and you do not need columns from every matching child row.
Interview point:
- Avoid claiming CTEs always cache results or always improve performance; inspect the database version and plan.
Adapted from 05_SQL_POSTGRESQL.md · “Subquery vs EXISTS vs CTE?”. Code examples are study fragments. Difficulty and core labels are editorial estimates; version-specific behavior should be checked against your project.