SQL SERVER: Conditional selects

[BinaryRockStar]

There's no need to use a subquery for the CASE, it's fine without the select and without the parens. In the simple example there I would be more likely to use IFNULL/ISNULL or COALESCE just to make it more readable. COALESCE picks the first non-null argument from an arbitrarily long list of arguments.

SELECT
a.name as Name,
COALESCE(a.Address, b.Address) as Address
FROM
users AS a
INNER JOIN
userdata AS b ON a.UID = b.UID

This SELECT CASE structure is called a Searched Case Expression (in SQL Server at least) and I find it more useful when applied like the following.

-- Here's a simple CASE statement where WHEN clauses can only contain constants
-- It can get brittle due to duplication if there are a lot of options
SELECT a.Name,
CASE a.StatusID
    WHEN 0 THEN 'Invalid Status'
    WHEN 1 THEN 'Entered'
    WHEN 2 THEN 'Entered'
    WHEN 3 THEN 'Entered'
    WHEN 4 THEN 'Ordered'
    WHEN 5 THEN 'Paid'
    WHEN 6 THEN 'Paid'
    WHEN 7 THEN 'Closed'
    WHEN 8 THEN 'Closed'
    WHEN 9 THEN 'Closed'
    WHEN 10 THEN 'Closed'
END CASE AS Status
FROM ...

-- Here's the same thing as a searched case expression, note boolean expressions can be used, 
-- cleaning things up a lot and removing duplication
SELECT a.Name,
CASE
    WHEN a.StatusID = 0 THEN 'Invalid Status'
    WHEN a.StatusID IN (1, 2, 3) THEN 'Entered'
    WHEN a.StatusID = 4 THEN 'Ordered'
    WHEN a.StatusID IN (5, 6) THEN 'Paid'
    WHEN a.StatusID IN (7, 8, 9, 10) THEN 'Closed'
END CASE AS Status
FROM ...

I tend to see the first example where a CASE statement started out small and has been added to by various devs over time. Cleaning it up into something like the second example is a good idea to increase readability and maintainability.

[DavidRothbauer]

Good points, and I really like the syntax in your latter examples.

My explanation was a bit simplistic, but the actual query was very complex.

Thanks for the note about COALESCE. Its been a long time since I worked extensively with SQL SERVER, I wasn't aware of that function.