If a WITH clause defines a name that also exists as a table, which one does the query read?

## The rule Names introduced by a `WITH` clause are local to the statement that carries the clause, and within that statement they take precedence over schema objects — base tables and views — of the same name. A query that says `FROM sales_order` after defining a CTE called `sales_order` reads the CTE. ```sql WITH sales_order AS ( SELECT * FROM sales_order WHERE order_date >= '2024-01-01' ) SELECT COUNT(*) FROM sales_order; -- counts only the filtered rows ``` There are two different references here and they resolve differently, which is the part candidates trip over: - The `FROM sales_order` in the **main query** resolves to the CTE. - The `FROM sales_order` **inside the CTE's own body** resolves to the base table, because a non-recursive CTE is not permitted to reference itself. Without `RECURSIVE` there is no self-reference to resolve to, so the name falls through to the schema object. That second rule is standard, and it is what makes the "filter a table and keep its name" idiom work at all. It is also exactly the kind of subtlety you should not build a production query on: if a reader has to know the self-reference rule to understand which rows are being counted, the query is under-communicating. ## Scope and lifetime The shadowing is confined to one statement. Nothing about the base table changes: it is not hidden, not locked out, not renamed. The moment the statement finishes, the name means the schema object again, even inside the same transaction and the same session. This is the clearest illustration of the difference between a statement-scoped name and a catalog object: one is part of the text of a query, the other is part of the database. ```sql WITH sales_order AS (SELECT * FROM sales_order WHERE order_date >= '2024-01-01') SELECT COUNT(*) FROM sales_order; -- filtered rows SELECT COUNT(*) FROM sales_order; -- separate statement: the whole table ``` Two consecutive statements, the same text in the `FROM`, two different answers. That is legal and well-defined, and it is also a good argument for not doing it. ## Why it bites in practice A statement with a shadowing CTE reads like a statement over the table. In a fifty-line query with several joins, every `JOIN sales_order` now silently means "orders since 2024", and a reviewer skimming the joins will read them as the full table. When the query later grows a join that genuinely wants all history, the author adds `JOIN sales_order` and gets the filtered set without any error, because the name resolves quietly. Nothing fails; the numbers are just wrong. The same hazard shows up in the other direction during refactoring. Someone deletes the `WITH` block — perhaps moving the filter into the main `WHERE` — and every reference in the statement silently re-points at the base table. Again, no error. ## The fix Name the CTE for what it *is*, not for what it came from: ```sql WITH recent_order AS ( SELECT * FROM sales_order WHERE order_date >= '2024-01-01' ) SELECT COUNT(*) FROM recent_order; ``` Now every reference states which set it means, the reviewer cannot misread it, and adding a join to the full table later is unambiguous. A useful team convention is that CTE names describe the restriction (`recent_order`, `active_customer`, `dept_size`) and never reuse a table or view name. ## How this differs from the other two constructs A derived table has no name that can collide in this way — its correlation name is chosen fresh and is local to one block, so aliasing it `sales_order` shadows nothing outside that block. A view, by contrast, is a catalog object and *cannot* share a name with a table in the same schema at all: the engine rejects the creation, because both live in the same namespace. Only a CTE can transiently take over an existing name, and only for one statement, precisely because it is not a schema object. ## Answering it well State the precedence rule, then the scope: the CTE wins, for that statement only. Add the inner-reference detail if asked, because it shows you know a non-recursive CTE cannot see itself. Then say what you would actually do — rename the CTE — because interviewers asking about a shadowing gotcha are usually checking whether you treat clever name reuse as a trick to show off or as a readability defect to avoid. ## Common mistakes Expecting an ambiguity error; expecting the base table to win; believing the CTE definition somehow modifies or replaces the table; and believing the shadowing persists for the session or the transaction.