Blog / 2012 / November / 5

SQL With Clause

November 5, 2012

Using the SQL with clause you can effectively define a temporary table and then select from it. This is formally known as subquery factoring.

WITH band_names AS
  (SELECT b.name, r.country_code
  FROM bands_band b  
  INNER JOIN regions_region r ON r.id = b.region_id)
SELECT * from band_names WHERE country_code = 'no'

This creates a table alias called band_names that has a query behind it. We then select from this table alias, specifying a further where clause.

You can specify more than one temporary table:

WITH 
  band_names AS (SELECT name, region_id FROM bands_band WHERE name LIKE 'R%'),
  regions AS (SELECT id, name, country_code FROM regions_region)
SELECT b.name, r.country_code FROM band_names b INNER JOIN regions r ON r.id = b.region_id

Tags: sql with subquery factoring