MariaDB IF Tutorial

In this MariaDB tutorial, we will study the use of the MariaDB IF statement and we will also cover some examples. This is the list of the topics that come under discussion:

• MariaDB if
• MariaDB if else
• MariaDB if exists
• MariaDB if table exists
• MariaDB if not exists
• MariaDB if not exists insert
• MariaDB if statement in select
• MariaDB if null
• MariaDB if not null

MariaDB IF

In this section, we will explain how to use MariaDB IF statement as well as MariaDB IF function with syntax and examples.

1. MariaDB IF Function

If the statement evaluates to TRUE, the MariaDB IF function returns one value; otherwise, it returns another value if it evaluates to FALSE.

The syntax of the IF function in MariaDB is given below:

IF( condition, [value_if_true], [value_if_false] )

The syntax explanation:

• condition: the value that we want to test as a user.
• value_if_true: it is the value that will be returned as IF statement turns to be TRUE. [Optional].
• value_if_false: it is the value that will be returned as if statement turns out be FALSE. [Optional].

Let me show you how the flowchart of the MariaDB IF statement works as follows:

Let’s look at an example of how to use the IF function in MariaDB to return string value as shown below:

SELECT IF(5<15, 'YES','NO');

In the above example, we are using a MariaDB IF function to check a condition. The condition will return YES string value as output and if 15 is greater than 5 otherwise it will return NO string value as 15 numeric value is less than 5.

2. MariaDB IF Statement

Now, let’s see how we can combine all MariaDB IF statements in the example as it follows:

DELIMITER $
CREATE FUNCTION usa_dollar ( DOLLAR_value INT )
RETURNS VARCHAR(30)
BEGIN
DECLARE size VARCHAR(30);
IF DOLLAR_value < 70 THEN
SET size = 'DOLLAR INDEX LOW';
ELSEIF DOLLAR_value >= 75 AND DOLLAR_value <= 70 THEN
SET size = 'DOLLAR INDEX CALLBACK';
ELSE
SET size = 'DOLLAR INDEX HIGH';
END IF;
RETURN size;
END; 
DELIMITER ;

SELECT usa_dollar(50) as Dollar_MarketPrice;

As shown in the above query, we have created a new function usa_dollar() in which we will check the input value by using the IF statement.

Now, if DOLLAR_value is less than 70, we will get a return statement as DOLLAR INDEX LOW. And if the value of DOLLAR_value is between 70 and 75 then it will return value as DOLLAR INDEX CALLBACK and ELSE size is DOLLAR INDEX HIGH.

After that, we will return the SIZE then the end function, and the END IF statement.

But here I have used DOLLAR_value as 50 which is less than 75. Therefore it put the result as “DOLLAR INDEX LOW” for a new column as Dollar_MarketPrice.

Also, check: MariaDB AUTO_INCREMENT

MariaDB If Else

In this section, we will explain how to use the IF- ELSE statement in MariaDB with syntax and an example.

In MariaDB, the IF-ELSE statement executes a code and returns a value if a condition is TRUE otherwise it will return another value if a condition is FALSE.

The syntax of the IF-ELSE statement is given below:

IF condition_1 THEN
   {...statements to execute when condition_1 is TRUE...}

[ ELSEIF condition_2 THEN
   {...statements to execute when condition1 is FALSE and condition_2 is TRUE...} ]

[ ELSE
   {...statements to execute when both condition_1 and condition_2 are FALSE...} ]

END IF;

The IF-ELSE syntax explanation:

• ELSEIF: [Optional] We should use ELSEIF statement/ condition when we want to execute a set of statement and also when second condition get TRUE result.
• ELSE: [Optional] We should use ELSE statement when we want to execute a set of statement when IF or ELSEIF statement doesn’t give TRUE result.

The following example explains how to use the IF-ELSE statement in a MariaDB function:

DELIMITER //
CREATE FUNCTION USA_Nasdaq ( sensex BIGINT )
RETURNS varchar(10) DETERMINISTIC
BEGIN
DECLARE level varchar(20);
IF SENSEX < 50000 THEN
      SET level = 'SENSEX IS BEARISH';
ELSEIF SENSEX >= 50000 AND SENSEX <= 40000 THEN
      SET level = 'SENSEX IS NEITHER BULL NOR BEAR';
ELSE
      SET level = 'SENSEX IS BULLISH';
END IF;
RETURN level;
END; 
DELIMITER ;

SELECT USA_Nasdaq(5000) as 'Final_Result';

As shown in the above query, we have created a new function USA_Nasdaq() in which we have set different values by using the IF statement.

And if SENSEX is less than 50000, we will get a return statement as SENSEX IS BEARISH and if the value of SENSEX is between 50000 and 40000 then, it will return value as SENSEX IS NEITHER BULLISH NOR BEARISH and ELSE size is SENSEX IS BULLISH.

After that, we will return the LEVEL then the end function, and the END IF statement.

In the above execution, we are calling the function USA_Nasdaq by using parameters 5000. Now, as the parameter value is less than 50000. Therefore it took LEVEL value “SENSEX IS BEARISH” as the output and put it under the new column as FINAL_RESULT.

Read: MariaDB Delete Row

MariaDB If Exists

In MariaDB, we can use the IF EXISTS clause of the DROP TABLE statement to check whether the table exists in the database or not before dropping it.

The sample example to demonstrate the IF EXISTS clause is shown below:

DROP TABLE IF EXISTS USA_MARVEL;

By using this statement, it will drop the USA_MARVEL table if it exists.

When I run this statement, the table already existed, and so it dropped the table and I got the following message which is shown below:

/* Affected Rows:0 Found Rows:0 Warning:0 Duration for 1 query:0.172*/

When I run this statement again (after it had already been dropped), we will get the following message as shown below:

/* Affected rows:0 Found rows:0 Warnings:1 Duration for 1 query:0.031 sec.*/

Therefore, there was no error but it gave us a warning. Let’s have a look at the warning:

SHOW WARNINGS;

So, the warning indicates that the table doesn’t exist at least it is not an error. Here, what will happen we don’t use the IF EXISTS clause:

DROP TABLE USA_MARVEL;

This time we will get an error.

Also, check: MariaDB Cast with Examples

MariaDB If Table Exists

Let us understand, how to check if a table exists in the MariaDB database or not.

We will create a new table first by running down the below query. We are creating a table usa_airbnb. And we will verify it by different methods to check whether a table exists in the database or not.

CREATE TABLE usa_airbnb(
id INT PRIMARY KEY,
PropertyName VARCHAR(50) NOT NULL UNIQUE,
host_name VARCHAR(20),
neighbourhood VARCHAR(50) NOT NULL,
latitude FLOAT NOT NULL UNIQUE,
longitude FLOAT NOT NULL UNIQUE,
room_type VARCHAR(20) NOT NULL,
Price float NOT NULL);

We get a message as a verification part that the table usa_airbnb has been created.

Let’s check whether the table usa_airbnb by using DESC usa_airbnb query to check the description of the table as it is shown below:

DESC USA_AIRBNB;

We are going to write a function to check whether a table exists or not in MariaDB 10.5. The method is shown below:

The parameters description:

• Function_Name: tableExistsOrNot [use Camel case method while describing function name].
• input Parameters: table_Name.
• Returns: 1 or 0.
• Functionality: function to check whether the inputs data exists in the database or not, if the table does exists it will return 1 otherwise 0.

DELIMITER $$
CREATE FUNCTION tableExistsOrNot (table_Name varchar(255))
RETURNS BOOLEAN
BEGIN
  IF 
  (SELECT COUNT(*)FROM information_schema.tables WHERE table_schema =  DATABASE() AND table_name = table_Name)= 1
  THEN 
  RETURN TRUE;
   ELSE
     RETURN FALSE;
     END IF;
  
END
$$
DELIMITER ;

We will be calling this function to check whether customer_data or usa_airbnb exists or not. If it exists it will return 1 or returns 0.

SELECT tableExistsOrNot("customer_data");

SELECT tableExistsOrNot("usa_airbnb");

In this way, we can check if a table exists or not in MariaDB.

Read: MariaDB Substring [11 Examples]

MariaDB If not Exists

Let us understand, how to create a table using if not exist in MariaDB.

In MariaDB, IF NOT EXISTS clause is used to create a new table when a new table doesn’t exist in the database. Let me give you a short introduction to CREATE TABLE statement with syntax and an example.

Use CREATE TABLE statement is used to create a new table with the given name by the user.

CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...) [table_options]... [partition_options]

The sample example of the IF NOT EXISTS clause is shown below:

CREATE TABLE IF NOT EXISTS DC_characters(
DC_id INT PRIMARY KEY,
first_name VARCHAR(20) NOT NULL UNIQUE,
last_name VARCHAR(20) NOT NULL UNIQUE,
Actor_name VARCHAR(30) NOT NULL);

DESC DC_characters;

As shown in the above query, this table didn’t exist in the database, so it created a new table name as DC_characters.

Read: MariaDB LIMIT + Examples

MariaDB If not Exists Insert

In MariaDB, there is no way to insert new data if the data doesn’t exist. It is because of the PRIMARY KEY and UNIQUE KEY constraints columns in the table. Each time MariaDB will throw an error if we try to insert a new query with the duplicate values of that column.

Keep in mind that if we create an insert IF NOT EXISTS query, the MariaDB table already has PRIMARY KEY and UNIQUE KEY constraint with one or more columns in it.

In MariaDB, let’s create a new Marvel_characters table as shown below:

CREATE TABLE Marvel_characters(
Marvel_id INT PRIMARY KEY,
first_name VARCHAR(20) NOT NULL UNIQUE,
last_name VARCHAR(30) NOT NULL UNIQUE,
Actor_name VARCHAR(30) NOT NULL);

DESC MARVEL_characters;

INSERT INTO Marvel_characters 
VALUES(201,'TONY','STARK','Robert Downey Jr'),
(202,'BRUCE','BANNER','Mark Raffalo'),
(203,'THOR','ODINSON','Chris Hemsworth'),
(204,'BLACK','WIDOW','Scarlett Johnson'),
(205,'LOKI','','Tom Hiddleston'),
(206,'CLINT','BARTON','Hawkeye'),
(207,'STEVE','ROGERS','CAPTAIN AMERICA'),
(208,'T','CHALLA','Black Panther'),
(209,'PETER','PARKER','Tom Holland'),
(210,'THE MAD','TITAN','Josh Brolin');

For suppose, we have a Marvel_characters table with the following data:

SELECT * FROM MARVEL_CHARACTERS;

As we see during insertion time in the Marvel_characters table, the Marvel_id column has a primary key column. So, MariaDB will throw an error, if try to insert a new row with an existing value.

The query below uses the Marvel_id as 202 due to which MariaDB will produce the following error:

INSERT INTO Marvel_Characters values(202,'BRUCE','BANNER','Mark Raffalo');

There is another way to insert duplicate data in the Marvel_characters table by using the INSERT IGNORE statement will cause MariaDB to do nothing even when the insertion throws an error.

INSERT IGNORE INTO Marvel_characters 
values(202,'BRUCE','BANNER','Mark Ruffalo');

The MariaDB will produce the following output:

/* Affected Rows:0 Found Rows:0 Warnings:1 Duration for 1 query: 0.000sec */

With the help of the INSERT IGNORE statement, MariaDB will insert a new row only when a new row doesn’t exist in the table.

Read: MariaDB index with Examples

MariaDB If statement in Select

In this section, we will explain how to use the IF function in the SELECT statement in MariaDB.

Syntax:

The syntax of the IF function in MariaDB is given below:

IF(expression_1,expression_2,expression_3);

The syntax explanation is given below:

• If expression_1 is TRUE when (expression_1>0 and expression_1>NULL), expression_2 will be returned; otherwise, expression_3 will be returned.
• The if() function returns a number or string value based on the context of the user.

Example:

The sample example of the IF function in the SELECT statement is given below:

SELECT IF(1>2,2,3);

As shown in the first image of a SELECT statement by using the IF function to check which value is greater in between 1 and 2 as expression_1 is 1>2, expression_2 as 2, and expression_3 as 3 value in the query.

By using IF function and also using > operator in between 1 and 2 numeric value and to check which one is the greater value between them.

Once the condition finds a greater value between 1 and 2 values, it will return the expression_2 value as 2 numeric value. Otherwise, the condition is FALSE it will return the expression_3 value as 3 as the output value.

SELECT IF(2>1,"YES","NO");

As shown in the second image of a SELECT statement by using the IF function to check which value is greater in between 1 and 2 as expression_1 is 2>1, expression_2 as YES, and expression_3 as NO value in the query.

By using the IF function and also using > operator in-between 2 and 1 value to check which one has the greater value.

Once the condition is TRUE, it will return the expression_2 value as YES string value and if the condition is FALSE it will return the expression_3 value as NO string value as the output value.

SELECT IF(STRCMP("TEST","TEST1"),"NO","YES");

As shown in the third image of a SELECT statement by using the IF function to check which value is greater in between 1 and 2 as expression_1 is strcmp(“TEST”, “TEST1”), expression_2 as NO and expression_3 as YES value in the query.

In MariaDB, the strcmp() function is known as string comparison function which is an in-built string function that compares two strings and returns 0 or 1.

By using the IF function and also using string comparison function in-between TEST and TEST1 values to check whether both strings have name characters in them or not.

Once the condition is TRUE, it will return the expression_2 value as YES otherwise, the condition is FALSE it will return the expression_3 value as NO as the output value.

Read: MariaDB LIKE Operator [7 Examples]

MariaDB IF Null

In MariaDB, we are going to explain how to use the IFNULL function with the help of syntax and an example. The MariaDB IFNULL function returns an alternative value if the expression is NULL.

The syntax of the IFNULL function in MariaDB is given below:

IFNULL(expression, value_if_null);

The syntax explanation:

• expression: the value to be tested as NULL.
• value_if_null: The value to be returned if expression is NULL.

The sample example of IFNULL function in the MariaDB is given below:

SELECT IFNULL(NULL,"MariaDBTips.com");

Read: MariaDB Insert Into + Examples

MariaDB If Not Null

In MariaDB, IFNULL() function returns a numeric or string value depending on the user’s context. The syntax of the IFNULL() function is given below:

IFNULL(expr1,expr2);

The syntax explanation is shown below:

• if expr1 is not NULL then IFNULL() function will return the expr1 value as output.
• it will return the expr2 value.

The sample example of IFNULL() function is given below:

SELECT IFNULL(1,2);

As the above query explains, in IFNULL() function, the expr1 is 2 and expr2 is 1 value. The function says that if the expr1 is NOT NULL value then it will return the value of expr1 as 2 output otherwise it will return expr2 value as 1 output.

You may also like to read the following MariaDB tutorials.

• MariaDB create procedure
• MariaDB Variables Tutorial
• How to Change Column in MariaDB
• How to Remove User in MariaDB
• MariaDB Update Statement

In this tutorial, we have learned about the MariaDB IF using some examples. Moreover, we have also covered the following topics in this tutorial.

• MariaDB if
• MariaDB if else
• MariaDB if exists
• MariaDB if table exists
• MariaDB if not exists
• MariaDB if not exists insert
• MariaDB if statement in select
• MariaDB if null
• MariaDB if not null