Case When Select Command in MySQL Server

Arjun Panwar
Sep 30, 2012
20.3k
0
1
Article

In th MySQL server, the case expression shows multiple conditions. Expression is the compatible aggregated type of all returnvalues, but also depends on the context. If we have to use in a numeric context, the result is returnedas a decimal, real, or integer value.

CASE Syntax

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

Use CASE WHEN statement in SELECT

CREATE TABLE games (
   ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   Name VARCHAR(30) NOT NULL,
   NumDisks TINYINT NOT NULL DEFAULT 1,
   RatingID VARCHAR(4) NOT NULL,
   StatID CHAR(3) NOT NULL
)
ENGINE=INNODB;
Syntax
SELECT Name, RatingID AS Rating,
   CASE RatingID
      WHEN 'R' THEN 'Under 17 requires an adult.'
      WHEN 'X' THEN 'No one 17 and under.'
      WHEN 'NR' THEN 'Use discretion when renting.'
      ELSE 'OK to rent to minors.'
   END AS Policy
FROM games
ORDER BY Name;

first img.gif

Use CASE WHEN for string value

CREATE TABLE sales(
    num INT NOT NULL AUTO_INCREMENT,
    name CHAR(20),
    winter INT,
    spring INT,
    summer INT,
    fall INT,
    category CHAR(10),
    primary key(num)
);

Syntax
SELECT name AS Name,
CASE category
WHEN "Holiday" THEN "Seasonal"
WHEN "Profession" THEN "Bi_annual"
WHEN "Literary" THEN "Random" END AS "Pattern"
FROM sales;

second 2.gif

CASE WHEN with calculation

SELECT CASE 10*2
   WHEN 20 THEN '20 correct'
   WHEN 30 THEN '30 correct'
   WHEN 40 THEN '40 correct'
END;

Simple CASE WHEN command

SELECT CASE WHEN 10*2=30 THEN '30 correct'
   WHEN 10*2=40 THEN '40 correct'
   ELSE 'Should be 10*2=20'
END;

Use case when
Syntax
SELECT CASE WHEN 9>7
THEN "TRUE"
ELSE
"FALSE"
END
AS "Results";