Menu

SQL Subqueries vs CTEs: What I Reach For When Writing Compliance and Reconciliation Queries
πŸ“°

SQL Subqueries vs CTEs: What I Reach For When Writing Compliance and Reconciliation Queries

DEV CommunityΒ·Kahuthu MuriukiΒ·7 months ago
#bpwwNYQc
#sql#beginners#select#subquery#query
Reading 0:00
15s threshold

When I started writing SQL against transaction data in fintech and securities brokerage environments, my queries were a mess. Three levels of nesting, the same aggregate computed twice, and a debugging process that involved commenting out chunks until something ran. The shift came when I stopped treating subqueries as the only way to compose logic and started picking the right structure for the job.

This article walks through:

what a subquery actually is

the four shapes a subquery can take

when a subquery is the correct choice

what a Common Table Expression (CTE) is

non-recursive and recursive CTEs, with examples drawn from real reporting work

a side-by-side comparison covering readability, debugging, and performance

the rule of thumb I now use to decide between the two

Most of the examples below are loosely modelled on the kinds of tables I work with day to day β€” member records in a SACCO, KYC verification logs, trade tickets, and transaction ledgers.…

Continue reading β€” create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.