MariaDB If Null + Examples

by
MariaDB if null example

In this MariaDB tutorial, we will study the use of the MariaDB If Null function and we will also cover some examples. There are lists of the topic that comes under discussion:

  • MariaDB if null
  • MariaDB if not null
  • MariaDB check if null
  • MariaDB if variable is null
  • MariaDB update if not null
  • MariaDB check if not null
  • MariaDB check if column is null
  • MariaDB procedure if null
  • MariaDB insert if null

MariaDB If Null

In this section, we will learn how to use the MariaDB If Null Function and it is explained with the help of syntax and examples.

If an expression is NULL, the MariaDB IFNULL function returns an alternate value. The IFNULL function is only used in MariaDB 10 or above version. The syntax of the IFNULL function in MariaDB is given below:

SYNTAX:

SELECT IFNULL(EXPRESSION,VALUE_IF_NULL);

The syntax explanation:

  • EXPRESSION: the value is set to be NULL.
  • VALUE_IF_NULL: If expression is NULL, this value is returned.

NOTE:

  • If expression is NOT NULL, the IFNULL function returns expression.
  • If expression is NULL, the IFNULL function returns value_if_null.
  • The IFNULL function is comparable to MSAccess’s Nz function.

First, let’s have a look at the USA_BASKETBALL table by the SELECT statement as shown below:

SELECT * FROM USA_BASKETBALL;

In this query, the SELECT statement retrieves all records from the USA_BASKETBALL table.

MariaDB if null
MariaDB Select statement for USA_BASKETBALL table

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

SELECT IFNULL(PLAYER_NAME,NULL) FROM USA_BASKETBALL;

In this query, the IFNULL function took the PLAYER_NAME column as and NULL value as if_value_null column from the USA_BASKETBALL table. So, by using the IFNULL function the expression is not null it returns the expression by itself as the resultset.

MariaDB if null example
MariaDB IFNULL Function Example

Read: MariaDB ISNULL + Examples

MariaDB If Not Null

In this section, we will learn how to use the NOT NULL condition in the query and it is explained with the help of syntax and examples.

In MariaDB, normally the NOT NULL clause is used with the WHERE condition in the SELECT, UPDATE, ALTER or DELETE statement. We can also use the column_name with the empty space in the” “ bracket.

The syntax of the NOT NULL clause with the SELECT statement is given below:

SYNTAX:

SELECT [EXPRESSION] FROM TABLE_NAME 
WHERE COLUMN_NAME IS NOT NULL
OR COLUMN_NAME != ' ';

The syntax explanation:

  • table_name: The table from which we want tot retreive data.
  • where: The criteria that must be met in order for records to be chosen.
  • column_name is not null: The name of the column which contains empty rows or NULL values in that column for the result set.
  • or: The OR condition in MariaDB is used to test two or more conditions, with records being returned if any of them is met. In a SELECT, INSERT, UPDATE, or DELETE query, the OR condition can be employed.

First, let’s have a look at the DC_CHARACTERS table by the following query:

SELECT * FROM DC_CHARACTERS;

The MariaDB SELECT statement is used to retrieve all records from the DC_CHARACTERS table.

MariaDB if not null example
MariaDB SELECT statement for DC_CHARACTERS table

The sample example of the MariaDB NOT NULL condition in the SELECT statement is given below:

EXAMPLE:

SELECT * FROM DC_CHARACTERS
WHERE LAST_NAME IS NOT NULL 
OR LAST_NAME != ' ';

In this query, by using the SELECT statement, we want to retrieve all records from the DC_CHARACTERS table. And with the WHERE condition in which to check the met with the LAST_NAME column should be null or contains empty space in it.

Because of the NOT NULL condition or by utilizing the != as not equal operator in the OR condition, we receive the same result of the SELECT statement for the DC_CHARACTERS table based on the previous query.

MariaDB if not null example
MariaDB If Not Null Example

Read: MariaDB Regexp

MariaDB Check if Null

In this sub-subtopic, we will learn how to check if a value is NULL and it is explained with the help of syntax and examples.

The MariaDB IS NULL condition is used to test NULL values in the SELECT, DELETE, INSERT and UPDATE statements. The syntax of the IS NULL condition with the SELECT statement is given below:

SYNTAX:

SELECT EXPRESSION FROM TBL_NAME
WHERE COLUMN_NAME IS NULL;

The sample example of the MariaDB IS NULL condition with the SELECT statement is given below:

EXAMPLE:

SELECT * FROM DC_CHARACTERS 
WHERE LAST_NAME IS NULL;

We applied the IS NULL condition on the LAST_NAME column of the DC_CHARACTERS table using the SELECT statement in the previous query. As a result, the IS NULL condition will determine whether the LAST_NAME column in the DC_CHARACTERS database contains a NULL value. If the LAST_NAME column has a NULL value, entries from the DC_CHARACTERS table with a NULL value will be returned.

MariaDB check if null example
MariaDB Check IF NULL Example

Read: MariaDB Case Statement

MariaDB If Variable is Null

In this sub-topic, we will use the store procedure to find the variable is NULL and it is explained with the help of examples.

The sample example of the store procedure with a NULL value in the variable is given below:

DELIMITER //
CREATE procedure SP_IF_NULL (NAME VARCHAR(20) )
BEGIN
DECLARE real_name VARCHAR(20);
SET Real_NAME= Name;
 if Real_Name is NULL then
     select 'Dwayne Johnson';
     else
     select real_name;
     end if ;
     END //
     
CALL sp_if_Null(' JOhn Cena');

We utilized the parameter NAME with the data type VARCHAR(20) and stored it in another variable called Real_Name with the data type VARCHAR(20). So, it will check if the REAL_NAME variable is null or not, and if it is, it will call the sp_if_null method, which will return John Cena in the resultset.

MariaDB iif variable is null example
MariaDB If Variable is Null Example

Read: MariaDB Window functions

MariaDB Update If Not Null

In this section, we will learn how to use the UPDATE statement with IS NOT NULL clause and it is explained with the help of syntax and examples.

In MariaDB, the UPDATE statement is used to update the existing record in the table. The syntax of the UPDATE statement with the NOT NULL condition is given below:

SYNTAX:

UPDATE TABLE_NAME SET
COLUMN_1=EXPRESSION_1
COLUMN_2=EXPRESSION_2 
WHERE 
COLUMN_NAME IS NOT NULL;

The syntax explanation:

  • column_1,column_2: The column that we want to update by using the UPDATE statement.
  • expression_1,expression_2: The new values that should be assigned to column_1, column_2. As a result, the value of expression_1 would be assigned to column_1, the value of expression_2 to column_2, and so on.
  • WHERE: The conditions that must be completed in order for the update to run.
  • IS NOT NULL: The condition in which the column should be null in the WHERE condition to get the resultset.

First, let’s have a look at the TAKEN table by the following query:

SELECT * FROM TAKEN;

In this query, the SELECT statement retrieves all records from the TAKEN table.

MariaDB update if not null
MariaDB SELECT statement for TAKEN table

The sample example of the MariaDB Update If Not Null is given below:

UPDATE TAKEN SET 
ACTOR_AGE=46, ACTOR_NAME='LELAND ORSER'
WHERE ACTOR_AGE IS NOT NULL;

As can be seen in the preceding query, we used the UPDATE statement to update the TAKEN table and set the ACTOR_AGE as 46 and ACTOR_NAME as ‘LELAND ORSER’ with a WHERE clause condition to see if the ACTOR_AGE column contains no NULL values. As a result, the result set is generated based on this query.

MariaDB Check If Not Null

In this sub-topic, we will know and check how the if NOT NULL operator is used and it is explained with the help of syntax and examples.

The MariaDB IS NOT NULL operator is used to test a NOT NULL value in the SELECT, INSERT, DELETE, or UPDATE statement. The syntax of the IS NOT NULL condition with the SELECT statement is given below:

SYNTAX:

SELECT * FROM TBL_NAME
WHERE COLUMN_NAME IS NOT NULL;

First, let’s have a look at the DC_CHARACTERS table by the following query:

SELECT * FROM DC_CHARACTERS;

The MariaDB SELECT statement retrieves all records from the DC_CHARACTERS table.

MariaDB check if not null
MariaDB SELECT statement for DC_CHARACTERS table

The sample example of the IS NOT NULL condition with the SELECT statement is given below:

EXAMPLE:

SELECT * FROM DC_CHARACTERS 
WHERE LAST_NAME IS NOT NULL;

Using the SELECT statement, we used the IS NOT NULL condition on the LAST_NAME column of the DC_CHARACTERS table in the above query. So, using the IS NOT NULL condition, it will verify whether the LAST_NAME column in the DC_CHARACTERS table has a NULL value and provide the resultset accordingly.

MariaDB check if not null example
MariaDB Check If Not Null Example

Read: MariaDB Row_Number Tutorial

MariaDB Check If Column Is Null

In this section, we will check if a column is null and it is explained with the help of syntax and examples.

We can use the where clause with IS NULL to see if a column is empty or null, and we can use the condition as ” ” to see if it is empty space. The syntax of the MariaDB checks if a column is given below in two ways:

First Syntax:

SELECT [EXPRESSION] FROM TABLE_NAME WHERE 
COLUMN ='';

Second Syntax:

SELECT * FROM TABLE _NAME WHERE COLUMN IS NULL;

The syntax explanation:

  • The column_name has an empty space after the WHERE condition in the first syntax, indicating that it will return a result set that meets the condition with column_name empty.
  • In second syntax, we have used the IS NULL condition to bring the result set .

The sample example of the MariaDB check if a column is null as given below:

First, let’s have a look at the USA_LAPTOPCOMPANY table by the following query:

SELECT * FROM USA_LAPTOPCOPANY;

In this query, the MariaDB SELECT statement retrieves all records from the USA_LAPTOPCOMPANY table.

MariaDB check if column is null
MariaDB Select statement for USA_LAPTOPCOMPANY table

EXAMPLE:

SELECT * FROM USA_LAPTOPCOMPANY WHERE LAPTOP_ID IS NULL;

In this query, we have tried to retrieve all records from the USA_LAPTOPCOMPANY table by using the WHERE condition and to check if the column_name as LAPTOP_ID is null by using the IS NULL condition and giving it as a result set.

MariaDB check if column is null example
MariaDB Check If Column is Null Example

Read: MariaDB Date Function with Examples

MariaDB Procedure If Null

In this section, we will know about the NULL condition with IF statement in the stored procedure and it is explained with the help of syntax and examples.

A procedure in MariaDB is a parameterized stored program. Unlike a function, it does not return a value. As we can create a store procedure in other languages, we can also create our own procedure in MariaDB.

The syntax of the MariaDB procedure is given below:

SYNTAX:

CREATE 
PROCEDURE pdure_name [ (parameter data type,) ]
[ LANGUAGE SQL
| DETERMINISTIC
| NOT DETERMINISTIC
| { CONTAINS SQL 
  | NO SQL
  | READS SQL DATA
  | MODIFIES SQL DATA }
| SQL SECURITY { INVOKER | DEFINER }
| COMMENT 'cmmt_value'
BEGIN
   declare_sec
   execute_sec
END;

The sample example of the MariaDB store procedure with IF statement and the NULL condition is given below:

EXAMPLE:

delimiter //
create procedure CHECK_FORNULL(Real_Name varchar(20))
     begin
     if Real_Name is NULL OR Real_Name='' then
     select 'Neo Andreson';
     else
     select Real_Name;
     end if ;
     END //

CALLL CHECK_FORNULL(null);

We’ve constructed a stored procedure in which we’ve used the IF statement, as we can see in the above query. The IF statement is used to check if the argument REAL_NAME contains a NULL value or an empty space, and if it does, the call() function with the parameter NULL returns REAL_NAME as ‘NEO ANDRESON’.

MariaDB procedure if null example
MariaDB Procedure If Null Example

Read: MariaDB DateTime Tutorial

MariaDB Insert If Null

In this section, we will learn how to insert with the IFNULL clause and it is explained with the help of examples.

First, let’s create a TAKEN table by the following query:

CREATE TABLE TAKEN(
ACTOR_AGE INT NULL,
ACTOR_NAME VARCHAR(50));

INSERT INTO TAKEN (ACTOR_AGE,ACTOR_NAME)
VALUES(54,'LIAM NEESON'),
(49,'KIM'),
(NULL,'LELAND ORSER');

SELECT IFNULL(ACTOR_AGE,NULL) FROM TAKEN;

In this query, we must use the INSERT command to insert a NULL value at the insertion time. As a result of the IFNULL clause, the TAKEN table has an empty resultset.

MariaDB insert ifnull example
MariaDB Insert If Null Example

You may also like to read the following MariaDB tutorials.

In this MariaDB tutorial, we will study the use of the MariaDB If Null function and we will also cover some examples. There are lists of the topic that comes under discussion:

  • MariaDB if null
  • MariaDB if not null
  • MariaDB check if null
  • MariaDB if variable is null
  • MariaDB update if not null
  • MariaDB check if not null
  • MariaDB check if column is null
  • MariaDB procedure if null
  • MariaDB insert if null