NULL value in SQL

NULL in SQL is the term used to represent a missing value. The NULL value in a table is the value in an empty field. Field with NULL value is a field with no value.

Note : The field contains a NULL value other than a field with a value of zero (zero value) and a field containing a space (space).

Syntax

The basic syntax of NULL during table creation is:

 SQL> CREATE TABLE NHANVIEN( 
  ID INT NOT NULL, 
  TEN VARCHAR (20) NOT NULL, 
  TUOI INT NOT NULL, 
  DIACHI CHAR (25) , 
  LUONG DECIMAL (18, 2), 
  PRIMARY KEY (ID) 
 ); 

Here, NOT NULL indicates that the column must always accept a certain value of the given data type. There are two columns that do not use NOT NULL, meaning that these columns can be NULL.

Fields with NULL values ​​are fields that are left blank during record creation.

Example of NULL value

NULL values ​​can cause some problems while selecting data, because when comparing an unspecified value to any other value, the result is always an unknown result.

You must use the IS NULL or IS NOT NULL operators to check a NULL value.

Considering the table NHANVIEN has the following records:

 +----+----------+-----+-----------+----------+ | ID | TEN |TUOI | DIACHI | LUONG | +----+----------+-----+-----------+----------+ | 1 | Thanh | 32 | Haiphong | 2000.00 | | 2 | Loan | 25 | Hanoi | 1500.00 | | 3 | Nga | 23 | Hanam | 2000.00 | | 4 | Manh | 25 | Hue | 6500.00 | | 5 | Huy | 27 | Hatinh | 8500.00 | | 6 | Cao | 22 | HCM | | | 7 | Lam | 24 | Hanoi | | +----+----------+-----+-----------+----------+

The following is the usage of the IS NOT NULL operator in SQL:

 SQL> SELECT ID, TEN, TUOI, DIACHI, LUONG 
  FROM NHANVIEN 
  WHERE LUONG IS NOT NULL; 

The result is:

 +----+----------+-----+-----------+----------+ | ID | TEN |TUOI | DIACHI | LUONG | +----+----------+-----+-----------+----------+ | 1 | Thanh | 32 | Haiphong | 2000.00 | | 2 | Loan | 25 | Hanoi | 1500.00 | | 3 | Nga | 23 | Hanam | 2000.00 | | 4 | Manh | 25 | Hue | 6500.00 | | 5 | Huy | 27 | Hatinh | 8500.00 | +----+----------+-----+-----------+----------+

And the usage of the IS NULL operator in SQL is as follows:

 SQL> SELECT ID, TEN, TUOI, DIACHI, LUONG 
  FROM NHANVIEN 
  WHERE LUONG IS NULL; 

The above example will return the result:

 +----+----------+-----+-----------+----------+ | ID | TEN |TUOI | DIACHI | LUONG | +----+----------+-----+-----------+----------+ | 6 | Cao | 22 | HCM | | | 7 | Lam | 24 | Hanoi | | +----+----------+-----+-----------+----------+

In the next article, Quantum will discuss with you how to create a temporary name using ALIAS in SQL. Have you remembered to it!

Previous article: The clause combines UNION data in SQL

Next lesson: Create a temporary name using ALIAS in SQL