MariaDB Case Statement

In this MariaDB tutorial, we will learn about MariaDB Case Statement. Here we will illustrate various examples related to the MariaDB case statement. Additionally, we will cover the following topics

• MariaDB Case Statement
• MariaDB Case Statement With Select
• MariaDB Case Statement Examples
• MariaDB Case When Multiple Conditions
• MariaDB Case When Like
• MariaDB Case With Where Statement

MariaDB Case Statement

Here, we will learn about MariaDB Case Statement, its syntax, and an example.

A CASE statement in MariaDB is a control flow structure that enables us to add if-else logic to a query. Moreover. it can be utilized wherever that allows a correct expression, such as the SELECT, ORDER BY, and even WHERE clauses.

MariaDB Case Statement has the same functionality as that of IF-THEN-ELSE by allowing us to execute conditions and returns a value if the first condition is matched.

The syntaxes of the MariaDB Case Statement are given below.

Syntax 1 :

CASE expression

   WHEN val_1 THEN result1
   WHEN val_2 THEN result2
   ...
   WHEN val_n THEN resultn

   ELSE result

END

Syntax 2 :

CASE

   WHEN cond1 THEN result1
   WHEN cond2 THEN result2
   ...
   WHEN condN THEN resultN

   ELSE result

END

Here, the parameters and arguments are given below.

• expression – The value that we are going to compare with the list of values(val_1, val_2, …., val_n)
• val_1 to val_n – It is estimated in the order of the list and after matching the expression and the MariaDB CASE statement returns the corresponding result.
• condition_1 to condition_n – It is estimated in the order of the list and after the condition is true, the CASE statement returns the corresponding result.
• result_1 to result_n– It is the value that is returned after the condition is true.

Now, let’s understand how to use the CASE statement in MariaDB with the help of an example. And the script for the example is as follows.

CREATE TABLE stateTbl (
	id INT AUTO_INCREMENT PRIMARY KEY,
	country_name VARCHAR(50),
	state_name VARCHAR(50)
);

INSERT INTO stateTbl (country_name, state_name) 
VALUES ('United States', 'Washington'),
       ('United States', 'New York'),
       ('United States', 'California'),
       ('Canada', 'Alberta'),
       ('Canada', 'Manitoba'),
       ('Canada', 'Ontario'),
       ('Australia', 'New South Wales'),
       ('Australia', 'Victoria');

In the above example, first, we are creating a new table in MariaDB name stateTbl. Within this table, we have defined 3 columns id, country_name, and state_name. And we have also inserted some rows in the table.

Next, let’s see how to use the CASE statement on the result of the above table.

SELECT id, state_name,
CASE country_name
  WHEN 'United States' THEN 'US'
  WHEN 'Canada' THEN 'CA'
  WHEN 'Australia' THEN 'AU' 
END
AS 'country_code'
FROM stateTbl;

In the above script, we are using the CASE statement to produce the country_state column which will display the country code based upon the given country name. For example, if the country_name holds the United States value, it will assign country_code as the ‘US‘. Here is the final result.

Read: MariaDB query examples

MariaDB Case Statement With Select

Here, we will learn about MariaDB Case Statement with Select, its syntax, and an example.

MariaDB Case Statement has the same functionality as that of IF-THEN-ELSE by allowing us to execute conditions and returns a value if the first condition is matched.

The syntaxes of the MariaDB Case Statement with Select are given below.

Syntax 1 :

SELECT column1, column2, 
CASE expression

   WHEN val_1 THEN result1
   WHEN val_2 THEN result2
   ...
   WHEN val_n THEN resultN

   ELSE result

END
FROM tableName
WHERE conditions;

Syntax 2 :

SELECT column1, column2, 
CASE

   WHEN cond1 THEN result1
   WHEN cond2 THEN result2
   ...
   WHEN condN THEN resultN

   ELSE result

END
FROM tableName
WHERE conditions;

Here, the parameters and arguments are given below.

• column1, column2 – The columns whose value we want to show after the execution of the Case statement.
• tablename – the table on which the Case statement is being applied.
• expression – The value that we are going to compare with the list of values(val_1, val_2, …., val_n)
• val_1 to val_n – It is estimated in the order of the list and after matching the expression and the MariaDB CASE statement returns the corresponding result.
• condition_1 to condition_n – It is estimated in the order of the list and after the condition is true, the CASE statement returns the corresponding result.
• result_1 to result_n – It is the value that is returned after the condition is true.

An example of the MariaDB Case Statement is given below.

SELECT name, marks,  
CASE subject
  WHEN 'MariaDB' THEN 'Good'
  WHEN 'MySQL' THEN 'Better'
  ELSE 'Nothing'
END
FROM usa_student
WHERE name in ('James', 'Mary');

Here, we can see the result with name, marks, and corresponding case statements.

Read: MariaDB Insert Into + Examples

MariaDB Case When Multiple Conditions

In this section, we will learn about how to use the MariaDB Case Statement with multiple conditions, its syntax, and an example.

MariaDB Case Statement just like IF-THEN-ELSE enables us to execute conditions and returns a value if the first condition is matched. Now to specify multiple conditions in the CASE statement, we use multiple WHEN keywords. And with the WHEN keyword, we specify a condition.

The syntax of the MariaDB Case Statement When Multiple Conditions are given below.

CASE

   WHEN cond1 THEN result1
   WHEN cond2 THEN result2
   ...
   WHEN condN THEN resultN

   ELSE result

END

Here, the parameters and arguments are given below.

• condition_1 to condition_n – It is estimated in the order of the list and after the condition is true, the CASE statement returns the corresponding result.
• result_1 to result_n – It is the values that are returned after the condition is true.

An example of the MariaDB Case Statement is given below.

SELECT id, marks, 
CASE
  WHEN subject = 'MariaDB' THEN 'Good'
  WHEN subject = 'MySQL' THEN 'Better'
  ELSE 'Nothing'
END
FROM usa_student
WHERE name in ('James', 'Mary');

• In the above query, we are using the CASE statement in MariaDB to check multiple conditions.
• For instance, the first condition is checking if the value in the subject column is MariaDB or not. And if its value is MariaDB then, it will return “Good“.
• For the second condition, it will check if the value of the subject column is MySQL and if it is MySQL then it will return “Better“.
• In the last, if the subject column does not hold any of the specified values then it will return “Nothing“.

Here, we can see the result with the id, marks, and corresponding case statement.

Read: MariaDB create procedure

MariaDB Case When Like

The main use of the LIKE operator in MariaDB is to search for a certain pattern using wildcards. And we can generally use it in the WHERE clause of a SELECT statement. However, here we will discuss how to use it with the CASE statement in MariaDB.

MariaDB Case Statement with LIKE statement has the same functionality as that of IF-THEN-ELSE. And it allows us to execute conditions using the Like Statement and returns a value if the first condition is matched according to the Like Statement.

The syntax of the MariaDB Case Statement With Like Statement is given below.

CASE

   WHEN expression LIKE THEN result1
   WHEN expression LIKE THEN result2
   ...
   WHEN expression LIKE THEN resultN

   ELSE result

END

Here, the parameters and arguments are given below.

• expression – Expression can be any column or attribute that can be compared using Like Statement.
• result_1 to result_n – It is the value that is returned after the condition is true.

An example of the MariaDB Case Statement is given below.

CREATE TABLE stateTbl (
	id INT AUTO_INCREMENT PRIMARY KEY,
	country_code VARCHAR(50),
	state_name VARCHAR(50)
);

INSERT INTO stateTbl (country_code, state_name) 
VALUES ('USA', 'Washington'),
       ('USA', 'New York'),
       ('USA', 'California'),
       ('CA', 'Alberta'),
       ('CA', 'Manitoba'),
       ('CA', 'Ontario'),
       ('AU', 'New South Wales'),
       ('AU', 'Victoria');

For the example implementation, we will use the following table named stateTbl. This table consists of 3 columns, state_name, id, and country_code. Now, we will use the CASE statement with the LIKE operator to create another country_name column. Here is the query for this task.

SELECT id, state_name,
CASE 
  WHEN country_code LIKE "USA%" THEN 'United States'
  WHEN country_code LIKE "CA%" THEN 'Canada'
  WHEN country_code LIKE "AU%" THEN 'Australia' 
END
AS 'country_name'
FROM statetbl;

In the above query, we are using a CASE statement with a LIKE operator to return the country name by checking the country code of a state. For example, the USA will be shown as the United States. Here is the complete output of the above query.

Read: How to Change Column in MariaDB

MariaDB Case with Where Statement

Here, we will learn how to use the MariaDB Case statement in the Where clause, its syntax, and an example.

MariaDB Case with Where Statement has the same functionality as that of IF-THEN-ELSE by allowing us to execute conditions and returns a value if the first condition is matched with a where clause at the last.

The syntaxes of the MariaDB Case Statement with the Where clause are given below.

Syntax 1 :

SELECT column1, column2, 
CASE expression

   WHEN val_1 THEN result1
   WHEN val_2 THEN result2
   ...
   WHEN val_n THEN resultN

   ELSE result

END
FROM tableName
WHERE conditions;

Syntax 2 :

SELECT column1, column2, 
CASE

   WHEN cond1 THEN result1
   WHEN cond2 THEN result2
   ...
   WHEN condN THEN resultN

   ELSE result

END
FROM tableName
WHERE conditions;

Here, the parameters and arguments are given below.

• column1, column2 – The columns whose value we want to show after the execution of the Case statement.
• tablename – the table on which the Case statement is being applied.
• expression – The value that we are going to compare with the list of values(val_1, val_2, …., val_n)
• val_1 to val_n – It is estimated in the order of the list and after matching the expression and the MariaDB CASE statement returns the corresponding result.
• condition_1 to condition_n – It is estimated in the order of the list and after the condition is true, the CASE statement returns the corresponding result.
• result_1 to result_n – It is the values that are returned after the condition is true.

An example of the MariaDB Case Statement is given below.

SELECT id, name, marks,  
CASE subject
  WHEN 'PostgreSQL' THEN 'Good'
  WHEN 'MySQL' THEN 'Better'
  ELSE 'Nothing'
END
FROM USA_student
WHERE name in ('James', 'Mary');

Here, we are using a Case Statement with a Where clause in MariaDB.

You may also like to read the following MariaDB tutorials.

• MariaDB Update Statement
• MariaDB JSON Data Type
• MariaDB Left Join – Helpful Guide
• How to Remove User in MariaDB
• How to Create View in MariaDB
• Replace Function in MariaDB
• MariaDB on Duplicate Key Update

In this tutorial, we have learned about MariaDB Case Statement. Additionally, we have covered the following topics

• MariaDB Case Statement
• MariaDB Case Statement With Select
• MariaDB Case Statement Examples
• MariaDB Case When Multiple Conditions
• MariaDB Case When Like
• MariaDB Case with Where Statement