SQL Case Statement
November 5, 2012
In SQL, the CASE statement can be used to further control the output of a column, or limit the choices that are returned.
Due to the particular case I'm dealing with, this query will return a list of band names and their regions. Regions are the ISO country code (no=Norway, nz=New Zealand, ch=Switzerland) except where it is in the uk, when four different regions are listed: england, wales, scotland, northernireland.
SELECT b.name, r.country_code FROM bands_band b INNER JOIN regions_region r ON r.id = b.region_id
This outputs data in the following format:
name | country_code ---------------------------------------------------------------------------+----------------- Dublin Concert | ie Dundalk Concert Brass Band | ie Brass Band Rotterdam | nl Connacht Brass | ie Ceramic City Brass | england 1st Renfrew R.V. (Greenock) Brass Band | scotland Bedliniog | wales St Cynons | wales New Cross SE Railway Mission | england
Lets say we want to output the real ISO country code (gb) for the uk regions in a new column, otherwise we want it to contain the existing code. We can do this with a case statement.
SELECT b.name, r.country_code, CASE r.country_code WHEN 'england' THEN 'gb' WHEN 'wales' THEN 'gb' WHEN 'scotland' THEN 'gb' WHEN 'northernireland' THEN 'gb' ELSE r.country_code END as iso_code FROM bands_band b INNER JOIN regions_region r on r.id = b.region_id
The results of this query are:
name | country_code | iso_code ---------------------------------------------------------------------------+-----------------+---------- Dublin Concert | ie | ie Dundalk Concert Brass Band | ie | ie Brass Band Rotterdam | nl | nl Connacht Brass | ie | ie Ceramic City Brass | england | gb 1st Renfrew R.V. (Greenock) Brass Band | scotland | gb Bedliniog | wales | gb St Cynons | wales | gb New Cross SE Railway Mission | england | gb
Order By
You can also use the case statement as part of an order by clause
SELECT b.name, r.country_code FROM bands_band b INNER JOIN regions_region r on r.id = b.region_id ORDER BY CASE r.country_code WHEN 'england' THEN 'gb' WHEN 'wales' THEN 'gb' WHEN 'scotland' THEN 'gb' WHEN 'northernireland' THEN 'gb' ELSE r.country_code END
This will order the results by the country_code column, but place all england, wales, scotland, northernireland matching rows between fr and ie, as it's using gb for ordering.
The order within gb is not set, but adding a second order by column like this will ensure that the rows within gb are also grouped into the four regions:
SELECT b.name, r.country_code FROM bands_band b INNER JOIN regions_region r on r.id = b.region_id ORDER BY CASE r.country_code WHEN 'england' THEN 'gb' WHEN 'wales' THEN 'gb' WHEN 'scotland' THEN 'gb' WHEN 'northernireland' THEN 'gb' ELSE r.country_code END, r.country_code
