Create Tables Show Tables Alter Tables Rename Tables Clone Tables Truncate Tables Temporary Tables Repair Tables Describe Tables Add/Delete Columns Show Columns Rename Columns Table Locking Drop Tables Derived Tables

Queries Constraints Insert Query Select Query Update Query Delete Query Replace Query Insert Ignore Insert on Duplicate Key Update Insert Into Select

Create Views Update Views Drop Views Rename Views

Indexes Create Index Drop Index Show Indexes Unique Index Clustered Index Non-Clustered Index

Using Joins Inner Join Left Join Right Join Cross Join Full Join Self Join Delete Join Update Join Union vs Join

Unique Key Primary Key Foreign Key Composite Key Alternate Key

Triggers Create Trigger Show Trigger Drop Trigger Before Insert Trigger After Insert Trigger Before Update Trigger After Update Trigger Before Delete Trigger After Delete Trigger

Data Types VARCHAR BOOLEAN ENUM DECIMAL INT FLOAT BIT TINYINT BLOB SET

Regular Expressions RLIKE Operator NOT LIKE Operator NOT REGEXP Operator regexp_instr() Function regexp_like() Function regexp_replace() Function regexp_substr() Function

Fulltext Search Natural Language Fulltext Search Boolean Fulltext Search Query Expansion Fulltext Search ngram Fulltext Parser

Date and Time Functions Arithmetic Operators Numeric Functions String Functions Aggregate Functions

Useful Functions Statements Reference Quick Guide Useful Resources Discussion

Feedback

Submit request if you have any questions.

Course

IS NULL Operator

MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

IS NOT NULL Operator
NULL values in a MySQL table fields indicate that no (or unknown) values are present in them. These values are different from zeroes or invalid values.
In MySQL, it is not possible to check NULL values with comparison operators such as =, <, or <>. Instead, we use the IS NULL and IS NOT NULL (filtering non-null values) operators.
MySQL IS NULL Operator
The IS NULL operator in MySQL is used to check whether a value in a column is NULL. Using the IS NULL operator with a conditional clause allows us to filter records that contain NULL values in a particular column.
We can also use this operator with SELECT, UPDATE, and DELETE SQL statements.
Syntax
Following is the syntax of IS NULL in MySQL 
SELECT column_name1, column_name2, ...
FROM table_name
WHERE column_name IS NULL;
Example
Firstly, let us create a table named CUSTOMERS using the following query 
CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
); 
In the following query, we are using the INSERT statement to insert values to the table 
INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', NULL),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', NULL, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', NULL),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', NULL, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);
The table is created as follows 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
NULL
2
Khilan
25
Delhi
1500.00
3
Kaushik
NULL
Kota
2000.00
4
Chaitali
25
Mumbai
NULL
5
Hardik
27
Bhopal
8500.00
6
Komal
NULL
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
IS NULL with SELECT statement
The MySQL IS NULL operator can be used with the SELECT statement to filter the records with NULL values.
Example
In the following query, we are going to return all the records from the CUSTOMERS table where the AGE is null.
SELECT * FROM CUSTOMERS
WHERE AGE IS NULL;
Output
On executing the above query, it will generate an output as shown below 
ID
NAME
AGE
ADDRESS
SALARY
3
Kaushik
NULL
Kota
20000.00
6
Komal
NULL
Hyderabad
20000.00
IS NULL with COUNT() function
We can use the MySQL IS NULL operator with the COUNT() function to count the number of records with NULL values in a particular column.
Syntax
Following is the syntax of the IS NULL with COUNT() function in MySQL 
SELECT COUNT(column_name1, column_name2, ...)
FROM table_name
WHERE condition IS NULL;
Example
The following query returns the count of records have a blank field (NULL) in ADDRESS column of the CUSTOMERS table.
SELECT COUNT(*) FROM CUSTOMERS
WHERE ADDRESS IS NULL;
Output
On executing the above query, it will generate an output as shown below 
COUNT(*)
2
IS NULL with UPDATE statement
In MySQL, we can use the IS NULL operator with the UPDATE statement to update records with NULL values in a particular column.
Syntax
Following is the syntax of the IS NULL operator with the UPDATE statement in MySQL 
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE columnname1, columnname2, ... IS NULL;
Example
In the following query, we are updating the blank (NULL) records of the SALARY column to a value of 9000.
UPDATE CUSTOMERS
SET SALARY = 9000
WHERE SALARY IS NULL;
Verification
To check whether the table has been updated or not, execute the SELECT query to display the table.
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
9000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
NULL
Kota
2000.00
4
Chaitali
25
Mumbai
9000.00
5
Hardik
27
Bhopal
8500.00
6
Komal
NULL
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
IS NULL with DELETE statement
In MySQL, we can use the IS NULL operator with the DELETE statement to delete records with NULL values in a particular column.
Syntax
Following is the syntax of the IS NULL operator with the DELETE statement in MySQL 
DELETE FROM table_name
WHERE column_name(s) IS NULL;
Example
In the following query, we are trying to delete the blank (NULL) records present in the ADDRESS column of CUSTOMERS table.
DELETE FROM CUSTOMERS
WHERE AGE IS NULL;
Verification
To check whether the table has been changed or not, execute the SELECT query to display the table.
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
NULL
2
Khilan
25
Delhi
1500.00
4
Chaitali
25
Mumbai
NULL
5
Hardik
27
Bhopal
8500.00
7
Muffy
24
Indore
10000.00
IS NULL Operator Using Client Program
In addition to executing the IS NULL Operator on a MySQL Server using SQL query, we can also execute it using a client program.
Syntax
Following are the syntaxes of the IS NULL Operator in MySQL table in various programming languages 
PHPNodeJSJavaPython
To execute the IS NULL Operator in MySQL through a PHP program, we need to execute the SQL query with IS NULL operator using the mysqli function named query() as 
$sql = "SELECT column_name1, column_name2, ... FROM table_name 
WHERE column_name IS NULL";
$mysqli->query($sql);
Example
Following are the implementations of this operation in various programming languages 
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s", $mysqli->connect_error);
   exit();
}
//printf('Connected successfully.');
$sql = "SELECT * FROM CUSTOMERS WHERE AGE IS NULL";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records: \n");
   while($row = $result->fetch_assoc()) {
      printf("Id %d, Name: %s, Age: %d, Address %s, Salary %f",   
      $row["ID"],
      $row["NAME"],
      $row["AGE"],
      $row["ADDRESS"],
      $row["SALARY"]);
      printf("\n");         
   }
} else {
   printf('No record found.');
}
mysqli_free_result($result);
$mysqli->close();
Output
The output obtained is as follows 
Table records:
Id 3, Name: kaushik, Age: 0, Address Hyderabad, Salary 2000.000000
Id 6, Name: Komal, Age: 0, Address Vishakapatnam, Salary 4500.000000
﻿
﻿
﻿
﻿
﻿
﻿
﻿

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	NULL
2	Khilan	25	Delhi	1500.00
3	Kaushik	NULL	Kota	2000.00
4	Chaitali	25	Mumbai	NULL
5	Hardik	27	Bhopal	8500.00
6	Komal	NULL	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	9000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	NULL	Kota	2000.00
4	Chaitali	25	Mumbai	9000.00
5	Hardik	27	Bhopal	8500.00
6	Komal	NULL	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

IS NOT NULL Operator

MySQL IS NULL Operator

Syntax

Example

IS NULL with SELECT statement

Example

Output

IS NULL with COUNT() function

Syntax

Example

Output

IS NULL with UPDATE statement

Syntax

Example

Verification

IS NULL with DELETE statement

Syntax

Example

Verification

IS NULL Operator Using Client Program

Syntax

Example

Output