SQL WITH #

WITH clause defines a temporary dataset (i.e., not saved anywhere, only exists during the scope of the query) that can be called by later queries.

The construct is: WITH <temporary object name> AS (...) ...

WITH temp_data
AS
(
    SELECT
        A,
        SUM(B) as total
    FROM main_table
    GROUP BY A
)
SELECT AVG(total) average_of_individual_totals
FROM temp_data

Different from Subqueries #

WITH does seem to operate like a subquery.

Major difference: the WITH object can be re-used throughout the query at different points.

WITH temp_data
AS
(
    SELECT
    A, B from temp_data
    WHERE B ...
),
temp_data2
AS
(A, count(A) as count_a, ...) -- this query is pre-conditioned on something that already happened in temp_data
SELECT
    A, count_a
FROM temp_data2

^ bit of a hacky example, but I think it conveys what WITH does in a fairly concise form.