Conditional Statements in MySQL

Use IF in select clause: The IF syntax is used to execute some code only if a specified condition is true.

Syntax :  if (condition) code to be executed if condition is true;

Example : In the given 'browse' table, use an IF statemnt in the select command.

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

insert into browse value(1, 'Java', 1067 , 200, 150, 267,'Holiday');
insert into browse value(2, 'C',970,770,531,486,'Profession');
insert into browse value(3, 'JavaScript',53,13,21,856,'Literary');
insert into browse value(4, 'SQL',782,357,168,250,'Profession');
insert into browse value(5, 'Oracle',589,795,367,284,'Holiday');
insert into browse value(6, 'MySQL',953,582,336,489,'Literary');
insert into browse value(7, 'Cplus',752,657,259,478,'Literary');
insert into browse value(8, 'Python',67,23,83,543,'Holiday');
insert into browse value(9, 'PHP',673,48,625,52,'Profession');

Query : SELECT name AS Name, category AS Category,
IF(winter>500, "Sells", "Slow") AS Trend
FROM browse;

Using Control Flow Functions in a SELECT Statement:

CREATE TABLE DVDs (
ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(60) NOT NULL,
NumDisks INT NOT NULL DEFAULT 1,
RatingID VARCHAR(4) NOT NULL,
StatID CHAR(3) NOT NULL
);


INSERT INTO DVDs (Name, NumDisks, RatingID, StatID)
VALUES ('Christmas', 1, 'NR', 's1'),
('Doc', 1, 'G', 's2'),
('Africa', 1, 'PG', 's1'),
('Falcon', 1, 'NR', 's2'),
('Amadeus', 1, 'PG', 's2'),
('Show', 2, 'NR', 's2'),
('View', 1, 'NR', 's1'),
('Mash', 2, 'R', 's2');

Query : SELECT Name AS Title, StatID AS Status, RatingID AS Rating,
IF(NumDisks>1, 'Check for extra disks!', 'Only 1 disk.') AS Verify
FROM DVDs
ORDER BY Title;



Use CASE WHEN statement in SELECT:

Example :  In the following example we have to use the case statement.

Query : 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 DVDs
ORDER BY Name;

Use CASE WHEN for string value : This statement used for the string values.

Example : In the following example show that case statement apply on string value.

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



Simple CASE WHEN command : This is a simple case when command.

For Example : In this example shows when synatx is correct or not.

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