Can I use… with (non-recursive, top level)

1. With implies recursive
2. With query names are visible in accessed views
3. With names even mask qualified table names (schema.table)
4. Without column list: WITH name AS (SELECT…)
5. Query names in with don’t hide schema objects

The with clause defines “statement-scoped views”:

WITH query_name (column_name, …) AS (
   SELECT …
     FROM …
) [, …]
SELECT …
  FROM query_name

Related

• Tutorial: With — Organize Complex Queries

• Other forms: with recursive, with in subquery, with recursive in subquery.

• Product specific forms: with before insert, DML in with.

Normative References

The with clause is defined in ISO/IEC 9075:2016-2 as optional feature T121, “WITH (excluding RECURSIVE) in query expression”.