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

MySQL – IS NOT NULL Operator

Table of content


A NULL value in a MySQL table indicates a missing or unknown value. It appears to be blank and does not contain any data. This is different from zero values.

It is very important to understand that a NULL value is different than a zero value or a field that contains spaces. To check for NULL values in a table column, we can use two basic operators:

  • IS NULL

  • IS NOT NULL

MySQL IS NOT NULL Operator

The MySQL IS NOT NULL operator is used to verify whether a particular column has a non-null value or not. This operator can be used with SQL statements such as SELECT, UPDATE, and DELETE.

By using the IS NOT NULL operator in a conditional clause, we can only fetch the records that contain valid data in a particular column.

Syntax

Following is the syntax of IS NOT NULL in MySQL −

SELECT column_name1, column_name2, ...
FROM table_name
WHERE column_name IS NOT 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

Example

In the following query, we are going to return all the records from the CUSTOMERS table where the AGE is not NULL.

SELECT * FROM CUSTOMERS
WHERE AGE IS NOT NULL;

Output

Following output is produced −

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 NOT NULL with COUNT() function

We can use the IS NOT NULL operator along with the MySQL COUNT() function to count only the non-null values in a specific column(s).

Syntax

Following is the syntax of the IS NOT NULL with COUNT() function in MySQL −

SELECT COUNT(column_name1, column_name2, ...)
FROM table_name
WHERE condition IS NOT NULL;

Example

The following query returns the count of all rows in the CUSTOMERS table where the ADDRESS column is not NULL.

SELECT COUNT(*) FROM CUSTOMERS
WHERE ADDRESS IS NOT NULL;

Output

On executing the above query, it will generate an output as shown below −

COUNT(*)
5

IS NOT NULL with UPDATE statement

In MySQL, we can update all the non-null rows in a specific column(s) using the UPDATE statement with IS NOT NULL operator.

Syntax

Following is the syntax of the IS NOT NULL operator with the UPDATE statement in MySQL –

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE columnname1, columnname2, ... IS NOT NULL;

Example

In the following query, we will update the SALARY column to a value of 9000 for all records where the SALARY column is not NULL −

UPDATE CUSTOMERS
SET SALARY = 20000
WHERE SALARY IS NOT NULL;

Verification

To check whether the table has been updated or not, execute the SELECT query to display the CUSTOMERS table.

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

IS NOT NULL with DELETE statement

In MySQL, we can delete all the non-null rows in a specific column(s) using the DELETE statement with IS NOT NULL operator.

Syntax

Following is the syntax of the IS NOT NULL operator with the DELETE statement in MySQL –

DELETE FROM table_name
WHERE columnname1, columnname2, ... IS NOT NULL;

Example

In the following query, we are trying to delete records which are not null in the AGE column of CUSTOMERS table.

DELETE FROM CUSTOMERS
WHERE AGE IS NOT NULL;

Verification

To verify whether the table has been updated or not, display the table using a SELECT query.

ID NAME AGE ADDRESS SALARY
3 Kaushik NULL Kota 20000.00
6 Komal NULL Hyderabad 20000.00

IS NOT NULL Operator Using Client Program

In addition to executing the IS NOT 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 NOT NULL Operator in MySQL table in various programming languages −

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

$sql = "SELECT column_name1, column_name2, ... FROM table_name 
WHERE column_name IS NOT NULL";
$mysqli->query($sql);

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

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

To execute the IS NOT NULL Operator in MySQL through a Java program, we need to execute the SQL query with IS NOT 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 NOT NULL";
statement.executeQuery(sql);

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

is_not_null_query  = "SELECT column_name1, column_name2, ... FROM table_name 
WHERE column_name IS NOT NULL"
cursorObj.execute(is_not_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 NOT 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 1, Name: Ramesh, Age: 32, Address Hyderabad, Salary 0.000000
Id 2, Name: Khilan, Age: 25, Address , Salary 1500.000000
Id 4, Name: Chaital, Age: 25, Address Mumbai, Salary 0.000000
Id 5, Name: Hardik, Age: 27, Address Vishakapatnam, Salary 8500.000000
Id 7, Name: Muffy, Age: 24, Address , Salary 10000.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 NOT EQUAL Operator
  sql = "SELECT * FROM CUSTOMERS WHERE AGE IS NOT NULL;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});            

Output

The output produced is as follows −

Connected!
--------------------------
[
  {ID: 1, NAME: ''Ramesh'', AGE: 32, ADDRESS: ''Hyderabad'', SALARY: null},
  { ID: 2, NAME: ''Khilan'', AGE: 25, ADDRESS: null, SALARY: ''1500.00'' },
  { ID: 4, NAME: ''Chaital'', AGE: 25, ADDRESS: ''Mumbai'', SALARY: null },
  { ID: 5, NAME: ''Hardik'', AGE: 27, ADDRESS: ''Vishakapatnam'',SALARY: ''8500.00''  },
  { ID: 7, NAME: ''Muffy'', AGE: 24, ADDRESS: null, SALARY: ''10000.00'' }
]           
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class IsNotNullOperator {
  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 NOT 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: 1, Name: Ramesh, Age: 32, Addresss: Hyderabad, Salary: null
Id: 2, Name: Khilan, Age: 25, Addresss: null, Salary: 1500.00
Id: 4, Name: Chaital, Age: 25, Addresss: Mumbai, Salary: null
Id: 5, Name: Hardik, Age: 27, Addresss: Vishakapatnam, Salary: 8500.00
Id: 7, Name: Muffy, Age: 24, Addresss: null, Salary: 10000.00              
import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
is_not_null_query = f"""
SELECT * FROM CUSTOMERS
WHERE AGE IS NOT NULL;
"""
cursorObj.execute(is_not_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 −

(1, ''Ramesh'', 32, ''Hyderabad'', None)
(2, ''Khilan'', 25, None, Decimal(''1500.00''))
(4, ''Chaital'', 25, ''Mumbai'', None)
(5, ''Hardik'', 27, ''Vishakapatnam'', Decimal(''8500.00''))
(7, ''Muffy'', 24, None, Decimal(''10000.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