Library › Book 15 › Subqueries and CTEs -- Queries Within Queries
Subqueries and CTEs -- Queries Within Queries
5.1🔒What Are Subqueries?A subquery is a query nested inside another query. It answers "compared to what?" questions:
5.2🔒Types of SubqueriesScalar subquery -- returns a single value:
5.3🔒EXISTS and NOT EXISTSEXISTS is often more efficient than IN for large datasets, and it handles NULL correctly:
5.4🔒Common Table Expressions (CTEs)CTEs use the WITH keyword to create named temporary result sets. They make complex queries readable:
5.5🔒Multiple CTEsYou can chain multiple CTEs, each building on the previous:
5.6🔒CTEs for Data ValidationCTEs are excellent for building multi-step validation queries:
5.7🔒Recursive CTEs (Advanced)Recursive CTEs can traverse hierarchical data:
5.8🔒Practice Schema 5: Reviews and Ratings
5.9🔒ExercisesBeginner:
5.10🔒Q&AResume phrasing- Architected multi-CTE data validation queries that verify order total consistency, referential integrity, and duplicate detection in a…
5.11🔒Q&ACover letter framingComplex data validation requires breaking problems into manageable steps. I use CTEs to build layered validation queries where each step is…
5.12🔒Q&AInterview framing"I approach complex queries by decomposing them into CTEs -- named, self-contained steps that I can test individually before combining. For…
5.13🔒Q&AWhat not to say- "I write one giant query with multiple nested subqueries." -- This signals unreadable, unmaintainable code. - "I do not know the…
5.14🔒Q&AQuestion 1Prompt: You need to build a single query that identifies all data integrity issues across an order management system: orders where the…
5.15🔒Q&AQuestion 2Prompt: A product manager asks: "Which customers are in the top 10% by spending but have never left a review?" Explain how you would build…
5.16🔒Q&AQuestion 3Prompt: You discover that a correlated subquery in your test suite takes 45 seconds to run on a 500K-row table. The query finds the most…