Can I use… with (non-recursive in subquery)

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. CTE in subquery cannot see global CTEs
6. Query names in with don’t hide schema objects

The with clause can be used in subqueries to define even more locally scoped “views”:

SELECT …
  FROM (  WITH …
        SELECT …
          FROM …
       ) table_name

Related

• Tutorial: With — Organize Complex Queries

• Other forms: with at top-level, with recursive, with recursive in subquery.

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

Normative References

The with recursive clause in subqueries is defined in ISO/IEC 9075:2016-2 as optional feature T122, “WITH (excluding RECURSIVE) in subquery”.