Khóa học miễn phí MySQL – IS NULL Operator nhận dự án làm có lương

MySQL – IS NULL Operator

Table of content


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 −

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);

To execute the IS NULL Operator in MySQL through a JavaScript program, we need to execute the SQL query with IS NULL operator using the mysql2 function named query() as −

sql= " SELECT column_name1, column_name2, ...FROM table_name 
WHERE column_name IS NULL";
con.query(sql);

To execute the IS NULL Operator in MySQL through a Java program, we need to execute the SQL query with IS NULL operator using the function named executeQuery() provided by JDBC type 4 driver −

String sql = "SELECT column_name1, column_name2, ... FROM table_name 
WHERE column_name IS NULL";
statement.executeQuery(sql);

To execute the IS NULL Operator in MySQL through a Python program, we need to execute the SQL query with IS NULL operator using the function named execute() provided by MySQL Connector/Python

is_null_query = "SELECT column_name1, column_name2, ... FROM table_name 
WHERE column_name IS NULL"
cursorObj.execute(is_null_query);

Example

Following are the implementations of this operation in various programming languages −

$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
var mysql = require(''mysql2'');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  //Creating a Database
  sql = "create database TUTORIALS"
  con.query(sql);

  //Select database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Creating CUSTOMERS table
  sql = "CREATE TABLE CUSTOMERS(ID INT NOT NULL,NAME VARCHAR(20),AGE INT,ADDRESS CHAR(25),SALARY DECIMAL(18, 2),PRIMARY KEY(ID));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(1,''Ramesh'', 32, ''Hyderabad'',NULL),(2,''Khilan'', 25, NULL, 1500.00),(3,''kaushik'', NULL, ''Hyderabad'', 2000.00),(4,''Chaital'', 25, ''Mumbai'', NULL),(5,''Hardik'', 27, ''Vishakapatnam'', 8500.00),(6, ''Komal'',NULL, ''Vishakapatnam'', 4500.00),(7, ''Muffy'',24, NULL, 10000.00);"
  con.query(sql);

  //Using IS NULL Operator
  sql = "SELECT * FROM CUSTOMERS WHERE AGE IS NULL;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});         

Output

The output produced is as follows −

Connected!
--------------------------
[
  {
    ID: 3,
    NAME: ''kaushik'',
    AGE: null,
    ADDRESS: ''Hyderabad'',
    SALARY: ''2000.00''
  },
  {
    ID: 6,
    NAME: ''Komal'',
    AGE: null,
    ADDRESS: ''Vishakapatnam'',
    SALARY: ''4500.00''
  }
]         
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class IsNullOperator {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = " SELECT * FROM CUSTOMERS WHERE AGE IS NULL";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
              String id = rs.getString("Id");
              String name = rs.getString("Name");
              String age = rs.getString("Age");
              String address = rs.getString("Address");
              String salary = rs.getString("Salary");
              System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Addresss: " + address + ", Salary: " + salary);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}                            

Output

The output obtained is as shown below −

Table records: 
Id: 3, Name: kaushik, Age: null, Addresss: Hyderabad, Salary: 2000.00
Id: 6, Name: Komal, Age: null, Addresss: Vishakapatnam, Salary: 4500.00             
import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
is_null_query = f"""
SELECT * FROM CUSTOMERS
WHERE AGE IS NULL;
"""
cursorObj.execute(is_null_query)
# Fetching all the rows that meet the criteria
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()                           

Output

Following is the output of the above code −

(3, ''kaushik'', None, ''Hyderabad'', Decimal(''2000.00''))
(6, ''Komal'', None, ''Vishakapatnam'', Decimal(''4500.00''))         

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc