Blog / 2012 / November / 5

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

Tags: sql case oracle postgres