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.
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