A common table expression very similar to a derived table, only that it can be used multiple times in a query

WITH plist (id, name) AS (
  SELECT id, 
         firstname||' '||lastname
  FROM person
  WHERE lastname LIKE 'De%'
)
SELECT o.amount,
       p.name
FROM orders o
  JOIN plist p ON p.id = o.person_id;

A more detailed introduction is available in the PostgreSQL manual

Common table expressions can also be used to query hierarchical data

Back to the SQL Feature Comparison