MySQL – NULL Values


MySQL – NULL Values


Table of content


”;


Previous

Next

The MySQL NULL Values

MySQL uses the term “NULL” to represent a non-existent data value in the database. These values are different from an empty string or zero and do not occupy any storage space in the database. They are used to indicate the absence of a value or an unknown value in a data field.

There are some common reasons why a value may be NULL −

  • The value may not be provided during data entry.

  • The value is not yet known.

Since NULL values are non-existent, you cannot use standard comparison operators such as “=”, “<,” or “>” with them. Instead, you can use the “IS NULL,” “IS NOT NULL,” or “NOT NULL” operators to check if a value is NULL.

Creating a Table without NULL Values

To create a table without NULL values, you can use the “NOT NULL” keyword while defining the columns. If a column is specified as “NOT NULL,” an error will occur when attempting to insert NULL values into that specific column.

Syntax

The basic syntax for creating a table with “NOT NULL” columns is as follows −


CREATE TABLE table_name (
   column1 datatype NOT NULL,
   column2 datatype NOT NULL,
   ...
   columnN datatype
);

Where, “NOT NULL” indicates that a column must always contain a specific value of the defined data type. Columns marked as “NOT NULL” cannot accept NULL values. On the other hand, you can insert NULL values into the columns without the “NOT NULL” constraint.

Example

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 NOT NULL,
   ADDRESS CHAR (25) ,
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, let us insert some records into the above-created table −


INSERT INTO CUSTOMERS VALUES 
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00),
(2, ''Khilan'', 25, ''Delhi'', 1500.00),
(3, ''Kaushik'', 23, ''Kota'', 2000.00),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00),
(6, ''Komal'', 22, ''Hyderabad'', NULL),
(7, ''Muffy'', 24, ''Indore'', NULL);

The CUSTOMERS table obtained is as follows −










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

Now, to retrieve records that are not NULL, you can use the “IS NOT NULL” operator as shown below−


SELECT ID, NAME, AGE, ADDRESS, SALARY 
FROM CUSTOMERS 
WHERE SALARY IS NOT NULL;

Following is the output of the above code −








ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00

To retrieve records that are NULL, you can use the “IS NULL” operator as shown below −


SELECT ID, NAME, AGE, ADDRESS, SALARY 
FROM CUSTOMERS
WHERE SALARY IS NULL;

The output produced is as follows −





ID NAME AGE ADDRESS SALARY
6 Komal 22 Hyderabad NULL
7 Muffy 24 Indore NULL

Updating NULL Values in a Table

To update NULL values in a table, you can use the “UPDATE” statement with the “IS NULL” operator. This filter the rows containing NULL values and set new values using the “SET” keyword.

Example

Here, we are updating the NULL values in the SALARY column of the CUSTOMERS table as shown below −


UPDATE CUSTOMERS SET SALARY = 9000 WHERE SALARY IS NULL;

Output

After executing the above code, we get the following output −


Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

Verification

You can verify whether the records in the table are updated or not using the following query −


SELECT * FROM CUSTOMERS;

The output displayed is as follows −










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

Deleting Records with NULL Values

To delete records with NULL values from a table, you can use the “DELETE FROM” statement with the “IS NULL” operator in the “WHERE” clause.

Example

Now, we are deleting records with NULL values in the SALARY column as shown below −


DELETE FROM CUSTOMERS WHERE SALARY IS NULL;

Output

Output of the above code is as shown below −


Query OK, 2 rows affected (0.01 sec)

Verification

You can verify whether the records in the table is deleted or not using the SELECT statement as follows −


SELECT * FROM CUSTOMERS;

The table produced is as shown below −








ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00



NULL Value Using a Client Program

We can also execute NULL value using the client program.

Syntax


PHP
NodeJS
Java
Python

To check whether a column”s value is null through a PHP program, we need to execute the “SELECT” statement using the mysqli function query() as follows −


$sql = "SELECT * from tcount_tbl WHERE tutorial_count IS NULL";
$mysqli->query($sql);

To check whether a column”s value is null through a JavaScript program, we need to execute the “SELECT” statement using the query() function of mysql2 library as follows −


sql = "SELECT * from tcount_tbl WHERE tutorial_count IS NOT NULL";
con.query(sql);

To check whether a column”s value is null through a Java program, we need to execute the “SELECT” statement using the JDBC function executeQuery() as follows −


String sql = "SELECT * FROM tcount_tbl WHERE tutorial_count IS NULL";
statement.executeQuery(sql);

To check whether a column”s value is null through a Python program, we need to execute the “SELECT” statement using the execute() function of the MySQL Connector/Python as follows: −


null_values_query = "SELECT * FROM tcount_tbl WHERE tutorial_count IS NULL"
cursorObj.execute(null_values_query)

Example

Following are the programs −


PHP
NodeJS
Java
Python 


$dbhost = ''localhost'';
$dbuser = ''root'';
$dbpass = ''password'';
$db = ''TUTORIALS'';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf(''Connected successfully.
'');
$sql = "SELECT * from tcount_tbl WHERE tutorial_count IS NULL";
if($result = $mysqli->query($sql)){
    printf("Table record: n");
    while($row = mysqli_fetch_array($result)){
        printf("Tutorial_author %s, Tutorial_count %d",
        $row[''tutorial_author''],
        $row[''tutorial_count'']);
        printf("n");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Output

The output obtained is as shown below −


Table record:
Tutorial_author mahnaz, Tutorial_count 0
Tutorial_author Jen, Tutorial_count 0



var mysql = require(''mysql2'');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});
 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
  //console.log("Connected successfully...!");
  //console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 sql = "SELECT * from tcount_tbl WHERE tutorial_count IS NOT NULL";
 if(con.query(sql)){
    console.log("Select query executed successfully....!");
 }
 else{
    console.log("Error");
 }
 console.log("Table records: ");
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 });
});

Output

The output obtained is as shown below −


Select query executed successfully....!
Table records: 
[
  { tutorial_author: ''mahran'', tutorial_count: 20 },
  { tutorial_author: ''Gill'', tutorial_count: 20 }
]



import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class NullValues {
   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 tcount_tbl WHERE tutorial_count = NULL";
            rs = st.executeQuery(sql);
            System.out.println("Table records(tutorial_count = null): ");
            while(rs.next()) {
               String tutorial_author = rs.getString("tutorial_author");
               String tutorial_count = rs.getString("tutorial_count");
               System.out.println("Author: " + tutorial_author + ", Tutorial_count: " + tutorial_count);
            }
            String sql1 = "SELECT * FROM tcount_tbl WHERE tutorial_count IS NULL";
            rs = st.executeQuery(sql1);
            System.out.println("Table records(tutorial_count is null): ");
            while(rs.next()) {
               String tutorial_author = rs.getString("tutorial_author");
               String tutorial_count = rs.getString("tutorial_count");
               System.out.println("Author: " + tutorial_author + ", Tutorial_count: " + tutorial_count);
            }
      }catch(Exception e) {
         e.printStackTrace();
      }
   }
}

Output

The output obtained is as shown below −


This will generate the following output - 
Table records(tutorial_count = null): 
Table records(tutorial_count is null): 
Author: mahnaz, Tutorial_count: null
Author: Jen, Tutorial_count: null



import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
null_values_query = f"""
SELECT * FROM tcount_tbl 
WHERE tutorial_count IS NULL
"""
cursorObj.execute(null_values_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

The output obtained is as shown below −


(''mahnaz'', None)
(''Jen'', None)




Print Page




Previous



Next


Advertisements

”;

Leave a Reply

Your email address will not be published. Required fields are marked *