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

3.8 ★ | 4 Vote

May be interested

  • The 4 rarest blood types in the world, are like goldThe 4 rarest blood types in the world, are like gold
    blood types bombay, rh-null, lutheran, rh-negative are the rarest blood types in the world because the percentage of people owning less than one in 1,000 people.
  • stddef.h in Cstddef.h in C
    the file header named stddef.h in c library defines various types of variables and macros. many of these definitions are also present in other headers.
  • String (String) in C / C ++String (String) in C / C ++
    this form of string originates from c language and continues to be supported in c / c ++. the string in the c programming language is essentially a one-dimensional array of characters that ends with a null character ''.
  • Variable in PHPVariable in PHP
    the main way to store information in php programs is to use a variable.
  • The strlen () function in CThe strlen () function in C
    the function size_t strlen (const char * str) calculates the length of str string (not including the ending null character).
  • Function puts () in CFunction puts () in C
    the function int puts (const char * str) in the standard c library writes a str string to stdout (not writing null characters). a newline character (new line) is appended to the output.
  • String in CString in C
    the string in the c programming language is essentially a one-dimensional array of characters that ends with a null character ''.
  • Function fputs () in CFunction fputs () in C
    the function int fputs (const char * str, file * stream) in the standard c library writes a string to the specified stream (not writing null characters).
  • Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 10Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 10
    in part 10, i will show you how to use powershell scripts in conjunction with smo and parameters to create sql server scripts. creating sql server scripts is an important task for administrators and sql server database development professionals.
  • Cursor in CCursor in C
    pointer in the c language is easy to learn. some tasks in c language are made easier by pointers, and other tasks become more flexible, such as in memory allocation, which cannot be performed without using a cursor.