I ran into this today, and it looks really helpful for times when you need to have a certain value in a field which is based on (but not identical to) a value in one of your tables.
To illustrate: say you needed to pull data on a person which included whether or not said person was retired. The has “Retired” and “Working” as possible options in the “Retirement” field of the “People” table.
The Select query may look like this:
SELECT FirstName, LastName, DOB, Retirement
But what if you were required to provide a code instead of “Retired” or “Working”? Maybe “Retired” needs to come back as “01” and “Working” needs to come back as “02.”
The way I would have dealt with this on my own is to make a tiny table with the codes and the retirement status in separate columns, then joined the table to the “People” table. But there’s another way – I think maybe a better way in terms of stress on the server’s CPU. (Is it better?)
It’s the “CASE WHEN” clause, and this is what it looks like:
WHEN Retirement = ‘Retired’ THEN ’01’
WHEN Retirement = ‘Working’ THEN ’02’
I did find a real explanation of CASE modeanalytics.com – and it looks like it might be a good resource for learning SQL in general.