I was looking through the thread for this solution. It's great because it's much harder to mess up and is less verbose than a case, and it's built on common SQL features making it portable and fast and easy to grok (as long as both the engine and the dba knows how joins work).

As a data analyst this technique is also great for scrubbing dirty data at query time when you don't have the ability to just fix it.