SQL NULL Value

<< Previous - SQL UNION Operator

Next - SQL Alias >>

Null is a keyword that indicates the missing data, unknown or absent data in the database. In SQL, the null is a special keyword representing no value. NULL is not the same as empty string

A field with the NULL value looks like a field with no value.

If you do not have the correct data, you may leave that field as blank. To represent this blank, you may use NULL value.

For example, if some students do not have a laptop for attending online classes, you may not know who are not having laptops in their hands, but you need to maintain their student records. To handle this situation, You can define the laptop field as a Null value. Then only you may leave that field as a space for a future update.

Syntax

CREATE TABLE Student_Details (ID_no int NOT NULL,
	Name varchar(20) NOT NULL ,
	Dept varchar(20),
	Laptop varchar(10));

IS Null and IS NOT NULL Operator

There are two operators used for checking null records in the table.

• IS NULL operator ----- used to get the records with the null values from the table.
• IS NOT NULL operator ----- used to get the records with not null value from the table.

  Example

  mysql> select *  from student_details;
  +--------+-----------+---------+-------------+-------------------+----------+-----------+
  | RollNo | Name      | PhoneNo | Laptop_Info | Postal_address    | Pin_code | City      |
  +--------+-----------+---------+-------------+-------------------+----------+-----------+
  |      1 | Kamalesh  |  689900 | yes         | 20 bharath nagar  |   600002 | Chennai   |
  |      2 | Aadhi     |  683200 | yes         | 2 bhrindha street |   600001 | Pandy     |
  |      3 | Sunil     |  631200 | NULL        | 2 panny street    |    60020 | Mumbai    |
  |      4 | Makesh    |  634560 | yes         | 2nd street        |   610020 | Chennai   |
  |      5 | Bavithra  |  634110 | yes         | 7 Kannan Street   |   600010 | Pandy     |
  |      6 | Boomi     |  234110 | NULL        | 23 lala Street    |   602310 | Bangalore |
  |      7 | Yashvanth |  204110 | NULL        | 20 Muthu Street   |   602312 | Bangalore |
  |      8 | Dev       |  204890 | yes         | 3rd Street        |   602342 | Mumbai    |
  +--------+-----------+---------+-------------+-------------------+----------+-----------+
  8 rows in set (0.00 sec)
  

  Let us consider the following query statement to select the null records:

  SELECT * FROM Student_Details Where Laptop_Info IS NULL;
  

  Now, you will get the below result.

  
  mysql> select *  from student_details where Laptop_Info Is null;
  +--------+-----------+---------+-------------+-----------------+----------+-----------+
  | RollNo | Name      | PhoneNo | Laptop_Info | Postal_address  | Pin_code | City      |
  +--------+-----------+---------+-------------+-----------------+----------+-----------+
  |      3 | Sunil     |  631200 | NULL        | 2 panny street  |    60020 | Mumbai    |
  |      6 | Boomi     |  234110 | NULL        | 23 lala Street  |   602310 | Bangalore |
  |      7 | Yashvanth |  204110 | NULL        | 20 Muthu Street |   602312 | Bangalore |
  +--------+-----------+---------+-------------+-----------------+----------+-----------+
  3 rows in set (0.00 sec)
  
  

  Let's consider the following query statement to select the records that are not null:

  
  SELECT * FROM Student_Details
  WHERE Laptop_Info IS NOT NULL;
  
  

  While executing the above query, you will get the following result

  mysql> select *  from student_details where Laptop_Info Is not null;
  
  +--------+----------+---------+-------------+-------------------+----------+---------+
  | RollNo | Name     | PhoneNo | Laptop_Info | Postal_address    | Pin_code | City    |
  +--------+----------+---------+-------------+-------------------+----------+---------+
  |      1 | Kamalesh |  689900 | yes         | 20 bharath nagar  |   600002 | Chennai |
  |      2 | Aadhi    |  683200 | yes         | 2 bhrindha street |   600001 | Pandy   |
  |      4 | Makesh   |  634560 | yes         | 2nd street        |   610020 | Chennai |
  |      5 | Bavithra |  634110 | yes         | 7 Kannan Street   |   600010 | Pandy   |
  |      8 | Dev      |  204890 | yes         | 3rd Street        |   602342 | Mumbai  |
  +--------+----------+---------+-------------+-------------------+----------+---------+
  5 rows in set (0.00 sec)
  

  << Previous - SQL UNION Operator

  Next - SQL Alias >>

  
  
  
  

  ---