Search code examples
sqlcase-statement

SQL - Select two different values based on the same condition

Is it possible in SQL to have a case statement that selects two different values based on the same condition?

For example, if I had a table called Person with the following columns:

  • Name
  • Age
  • EyeColor

So if the person is 18 years old or older, I want to select their name. But if they are less than 18, I want to select their eye color. How can I do this using a case statement?

Sample Data:

NAME    AGE    EyeColor
------  -----  --------
John    17     Brown
Mike    21     Blue
Peter   16     Green
Alan    18     Brown

Expected result:

NameOrEyeColor
--------------
Brown
Mike
Green
Alan
Solution

  • If both columns are the same data type, then:

    select
  case
    when age >= 18 then name
    else eyecolor
  end as columnname
from person

    although in a Mysql database this would work for columns with different data types because of the implicit conversion done (check this link).