How to Write SQL CTEs and Subqueries with AI

Ask the AI to describe the desired result before choosing syntax. A report might need one row per customer with total spending and a flag indicating whether a recent order exists. Naming those intermediate ideas can make the query easier to reason about. A common table expression, or CTE, gives an auxiliary query a name within a larger statement using WITH. An ordinary SELECT CTE does not create a persistent table. A subquery is a query nested inside another statement; it may supply rows, test existence or provide a scalar value depending on its location. Neither form is automatically superior. Give each proposed intermediate result a clear meaning. A CTE called customer_totals should identify its grouping key and expected columns. Test that step on its own before joining it into the final report. If it unexpectedly contains multiple rows per customer, a later join may multiply results. Use EXISTS when the question is whether at least one matching row exists. A scalar subquery instead needs to satisfy the database's single-value requirements. In PostgreSQL, more than one returned row causes an error in a scalar context. Do not repair that by selecting an arbitrary row unless an explicit business rule justifies the choice. Readability does not determine execution strategy. PostgreSQL can fold some nonrecursive CTEs into the surrounding query, while others are materialized. The engine, version and query shape matter, so examine the execution plan before claiming that a rewrite is faster. Recursive CTEs add another concern: termination. A hierarchy may contain unexpected cycles. Ask the AI to explain how recursion ends and how repeated nodes are handled, then test a small cyclic example before running the query on a large graph.