NULL values in SQL

In SQL there may be some records in a table that do not have values or data for every field and those fields are termed as a NULL value.

NULL values could be possible because at the time of data entry information is not available. So SQL supports a special value known as NULL which is used to represent the values of attributes that may be unknown or not apply to a tuple. SQL places a NULL value in the field in the absence of a user-defined value. For example, the Apartment_number attribute of an address applies only to addresses that are in apartment buildings and not to other types of residences. 

So, NULL values are those values in which there is no data value in the particular field in the table.

Importance of NULL Value

• It is important to understand that a NULL value differs from a zero value.
• A NULL value is used to represent a missing value, but it usually has one of three different interpretations: 
  • The value unknown (value exists but is not known)
  • Value not available (exists but is purposely withheld)
  • Attribute not applicable (undefined for this tuple)
• It is often not possible to determine which of the meanings is intended. Hence, SQL does not distinguish between the different meanings of NULL.

Principles of NULL values

• Setting a NULL value is appropriate when the actual value is unknown, or when a value is not meaningful.
• A NULL value is not equivalent to a value of ZERO if the data type is a number and is not equivalent to spaces if the data type is a character.
• A NULL value can be inserted into columns of any data type.
• A NULL value will evaluate NULL in any expression.
• Suppose if any column has a NULL value, then UNIQUE, FOREIGN key, and CHECK constraints will ignore by SQL.

In general, each NULL value is considered to be different from every other NULL in the database. When a NULL is involved in a comparison operation, the result is considered to be UNKNOWN. Hence, SQL uses a three-valued logic with values True, False, and Unknown. It is, therefore, necessary to define the results of three-valued logical expressions when the logical connectives AND, OR, and NOT are used. 

How To Test for NULL Values?

SQL allows queries that check whether an attribute value is NULL. Rather than using = or to compare an attribute value to NULL, SQL uses IS and IS NOT. This is because SQL considers each NULL value as being distinct from every other NULL value, so equality comparison is not appropriate. 

Now, consider the following Employee Table.

Query:

CREATE TABLE Employee (
  Fname VARCHAR(50),
  Lname VARCHAR(50),
  SSN VARCHAR(11),
  Phoneno VARCHAR(15),
  Salary FLOAT
);

INSERT INTO Employee (Fname, Lname, SSN, Phoneno, Salary)
VALUES 
  ('Shubham', 'Thakur', '123-45-6789', '9876543210', 50000.00),
  ('Aman', 'Chopra', '234-56-7890', NULL, 45000.00),
  ('Aditya', 'Arpan', NULL, '8765432109', 55000.00),
  ('Naveen', 'Patnaik', '345-67-8901', NULL, NULL),
  ('Nishant', 'Jain', '456-78-9012', '7654321098', 60000.00);

Output:

The IS NULL Operator

Suppose we find the Fname and Lname of the Employee having no Super_ssn then the query will be:

Query:

SELECT Fname, Lname FROM Employee WHERE SSN IS NULL;

Output:

The IS NOT NULL Operator

Now if we find the Count of number of Employees having SSNs.

Query:

SELECT COUNT(*) AS Count FROM Employee WHERE SSN IS NOT NULL;

Output:

Updating NULL Values in a Table

We can update the NULL values present in a table using the UPDATE statement in SQL. To do so, we can use the IS NULL operator in the WHERE clause to select the rows with NULL values and then we can set the new value using the SET keyword.

Let’s suppose that we want to update SSN in the row where it is NULL.

Query:

UPDATE Employee
SET SSN = '789-01-2345'
WHERE Fname = 'Aditya' AND Lname = 'Arpan';

select* from Employee;

Output: