Conditional execution refers to the ability to execute a block of code only if a specific condition is met. It is a widespread operation in development. For example, the most common way to achieve a conditional execution in SQL is using the CASE statement.
The CASE statement allows us to perform the conditional logic by checking the value of one or more columns in a table and then executing a specific block of code based on the result of that check.
This tutorial teaches us how to use the CASE statement if a given value is NULL.
SQL Case Statement
The following shows the syntax of the CASE statement in SQL:
WHEN value_1 THEN result_1
WHEN value_2 THEN result_2
...
ELSE default_result
END
Consider the following example:
id int auto_increment not null primary key,
name varchar(50),
science_score int not null,
math_score int not null,
history_score int not null,
other int not null
);
INSERT INTO students (name, science_score, math_score, history_score, other)
VALUES
('John Doe', 80, 70, 90, 85),
('Jane Smith', 95, 85, 80, 92),
('Tom Wilson', 70, 75, 85, 80),
('Sara Lee', 88, 92, 90, 85),
('Mike Johnson', 75, 80, 72, 68),
('Emily Chen', 92, 88, 90, 95),
('Chris Brown', 85, 80, 90, 88),
('Lisa Kim', 90, 85, 87, 92),
('Mark Davis', 72, 68, 75, 80),
('Ava Lee', 90, 95, 92, 88);
For example, we can use the CASE statement to evaluate the various options and return a specific action as demonstrated in the following example:
name,
science_score,
math_score,
history_score,
other,
CASE
WHEN (science_score + math_score + history_score + other)/4 >= 90 THEN 'A'
WHEN (science_score + math_score + history_score + other)/4 >= 80 THEN 'B'
WHEN (science_score + math_score + history_score + other)/4 >= 70 THEN 'C'
WHEN (science_score + math_score + history_score + other)/4 >= 60 THEN 'D'
ELSE 'F'
END AS 'Grade'
FROM students;
Resulting Output:
SQL Case Statement When the Value Is Null
We can use the CASE statement to execute a code block when dealing with null values. In addition, we can use the IS NULL and IS NOT Null operators to evaluate for NULL values and perform a specific action based on the returned value.
Consider the following example of syntax:
CASE
WHEN column_name IS NULL THEN 'Value is null'
ELSE 'Value is not null'
END AS result
FROM table_name;
In this case, we check if the value of the provided column_name is null. If true, we return the “Value is null.” string. Otherwise, if the value is not null, we return the “Value is not null.” string.
We also use the AS keyword as an alias for the result string.
Consider the following example table:
VALUES
('John Doe', 80, 70, 90, 85, null),
('Jane Smith', 95, 85, 80, 92, null),
('Tom Wilson', 70, 75, 85, 80, 't'),
('Sara Lee', 88, 92, 90, 85, 's'),
('Mike Johnson', 75, 80, 72, 68, null),
('Emily Chen', 92, 88, 90, 95, null),
('Chris Brown', 85, 80, 90, 88, 'null'),
('Lisa Kim', 90, 85, 87, 92, ''),
('Mark Davis', 72, 68, 75, 80, null),
('Ava Lee', 90, 95, 92, 88, 'a');
We can then print a specific message for null values using the CASE statement.
CASE
WHEN temp IS NULL THEN 'Value is null'
ELSE 'Value is not null'
END AS result
FROM students s ;
Output:
Conclusion
We learned how to work with the SQL CASE statement to evaluate a specific condition and perform a required task based on the resulting value.
