SQL inline conditional in Select

Question

I was wondering if something like this was possible:

SELECT name or alt_name FROM users Where userid=somenum

I have also tried this:

SELECT name IF NOT NULL ELSE alt_name ...

If anyone has any insight I'd appreciate it.

Answer 1

Not the most efficient answer, but what about this:

SELECT name FROM users WHERE userid=somenum AND name IS NOT NULL
UNION
SELECT altname FROM users WHERE userid=somenum AND name IS NULL

I think this will produce what you are after.

Answer 2

If your trying to replace nulls then

select coalesce(name,alt_name)....

it will return first non null value

Answer 3

If you;re using a database that supports them then one of the simplest ways is to use a user defined function, you'd then have

SELECT udfPreferredName() FROM users

where udfPreferredName() would encapsulate the logic required to choose between the name and alternative_name fields.

One of the advantages of using a function is that you can abstract away the choice logic and apply it in multiple SQL statements wherever you need it. Doing the logic inline using a case is fine, but will usually be (much) harder to maintain across a system. In most rdbms the additional overhead of the function call will not be significant unless you are handling very large tables

Answer 4

SELECT CASE WHEN userid = somenum THEN name ELSE alt_name END FROM users

Answer 5

Do you mean something like this? I'm assuming TSQL

SELECT CASE WHEN [name] IS NOT NULL THEN [name] ELSE [alt_name] END AS [UserName]
FROM [Users] WHERE [UserId] = somenum

Answer 6

How about this?

SELECT IsNull(name, alt_name) FROM users Where userid=somenum

Its similar to the Coalesce function, but it only takes two arguments and is easier to spell.