SQLDailyDrills

WITH (common table expressions)

Name a subquery with WITH, then read it in the main query like a table.

Syntax

WITH name AS (SELECT …) SELECT … FROM name;
  • You can name several subqueries, separated by commas.

Example

WITH counts AS (SELECT customer_id, COUNT(*) AS orders FROM orders GROUP BY customer_id) SELECT customer_id, orders FROM counts WHERE orders >= 2;

Customers with at least two orders.

Tables used: orders. Try it in a lesson below.

Common mistakes

  • A WITH name lasts for one query only. Use a table when you need the data again later.

Practise it