In SQL, the term NULL represents missing or unknown data. A NULL value is not equivalent to an empty string or zero; rather, it indicates the absence of any value. It is used when data is unknown or not available for a particular column.
To find records with NULL values, you can use the IS NULL or IS NOT NULL conditions. Here's the basic syntax:
SELECT column1, column2
FROM table_name
WHERE column_name IS NULL;Example: Retrieve employees where the department is unknown (NULL):
SELECT Name, Department
FROM Employees
WHERE Department IS NULL;When inserting data into a table, you can explicitly insert a NULL value for a column:
INSERT INTO Employees (Name, Department)
VALUES ('Laeeque Deshmukh', NULL);This query inserts a new employee, but the department is unknown, so we use NULL for that column.
You can also update existing records to set a column's value to NULL:
UPDATE Employees
SET Department = NULL
WHERE ID = 3;This query updates the department of the employee with ID 3 to be NULL (unknown).
Many SQL functions, such as COUNT, SUM, and AVG, ignore NULL values by default. However, you can use functions like IFNULL() (in MySQL) or COALESCE() (in most SQL systems) to replace NULL with a default value.
SELECT Name, IFNULL(Department, 'Unknown')
FROM Employees;In this query, if the department is NULL, it will return 'Unknown' instead.
The concept of NULL is supported across many popular relational database systems: