In a table, attribute values are not always known values. Column values need not be known at the time of insertion, latter it may be updated or calculated. Sometimes, column will not have any known value for the particular set of combination.
Suppose student table has a column AVG_MARKS, whose value cannot be inserted at the time student details entered into the table (imagine student details are entered when he enrolls to the course). This column value will be available once he attends the exam and gets his marks. Similarly, suppose phone number of the student is not available or unknown. What value would be entered in such case? Can we enter NO or unknown? When we enter such values, it does not give the meaning that it does not exist. It is considered as some value. In order to represent ‘Not known’, SQL uses special value called NULL values. It signifies the compiler that the value does not exist.
In DB tables, NULL column values are represented by empty column (no space), or NULL or (null). In a query when column value has to be checked if null or not, we cannot directly compare using ‘=’ operator as this value is unknown, i.e.; unknown cannot be always equal to unknown or we cannot compare such values. But we can compare them by checking if they are unknown using ‘IS NULL’ or ‘IS NOT NULL’ clause.
SELECT * FROM STUDENT WHERE AVG_MARKS IS NULL; -- checks if the value is null and if yes returns the row. SELECT * FROM STUDENT WHERE AVG_MARKS IS NOT NULL; -- checks if the value is not null and if yes returns the row.
- Any arithmetic operation with NULL columns will result in NULL values. Consider the below example where one of the student has not yet received his average mark. Now we try to add the avg_mark column, it will result in NULL value.
- All aggregate functions, except COUNT ignores the NULL values. Consider the above example again. If we query to find the max and min of average marks, it will ignore the null value and give the result.
SELECT MAX (AVG_MARK), MIN (AVG_MARK) FROM STUDENT;
At the same time if query to count the students in the table using avg_mark column, it will consider null column also and result in 3. That means count does not consider column or its value when used in the query.
SELECT COUNT (AVG_MARK) FROM STUDENT;
- Any comparison operator used along with NULL value will return null value itself.
SELECT * FROM STUDENT WHERE STD_ID = 100 AND AVG_MARK > 50;
This query will not result in any value as this comparison results in NULL, and it cannot find any value in the table.