The with clause is also known as common table expression (CTE) and subquery refactory. It is a temporary named result set.

SQL:1999 added the with clause to define "statement scoped views". They are not stored in the database scheme: instead, they are only valid in the query they belong to. This makes it possible to improve the structure of a statement without polluting the global namespace.

Syntax

with <QUERY_NAME_1> (<COLUMN_1>[, <COLUMN_2>][, <COLUMN_N>]) as
     (<INNER_SELECT_STATEMENT>)
[,<QUERY_NAME_2> (<COLUMN_1>[, <COLUMN_2>][, <COLUMN_N>]) as
     (<INNER_SELECT_STATEMENT>)]
<SELECT_STATEMENT>

Non-Recursive Example

with sales_tbl as (
select sales.*
	from (VALUES
		('Spiderman',1,19750),
		('Batman',1,19746),
		('Superman',1,9227),
		('Iron Man',1,9227),
		('Wonder Woman',2,16243),
		('Kikkoman',2,17233),
		('Cat Woman',2,8308),
		('Ant Man',3,19427),
		('Aquaman',3,16369),
		('Iceman',3,9309)
	) sales (emp_name,dealer_id,sales)
)
select ROW_NUMBER() over (order by dealer_id) as rownumber, *
from sales_tbl

Recursive Example

WITH [counter] AS (

   SELECT 1 AS n  -- Executes first and only once.

   UNION ALL      -- UNION ALL must be used.

   SELECT n + 1   -- The portion that will be executed 
   FROM [counter] -- repeatedly until there's no row 
                  -- to return.

   WHERE  n < 50  -- Ensures that the query stops.
)
SELECT n FROM [counter]