IS NULL and IS NOT NULL in SQL
These are used to check missing values in a database table. When you work with real data, some fields may not have any value stored. Because of this, SQL provides IS NULL and IS NOT NULL in SQL to filter records accurately and avoid wrong results.
Understanding these checks early helps you write accurate queries and avoid errors.
What Does NULL Mean in SQL
It means the value does not exist. It is not zero, and it is not an empty string. Instead, it shows that no data was entered.
Because of this, SQL treats NULL differently from normal values. As a result, standard comparison operators do not work with it.
Finding Missing Data Using IS NULL
Use this when you want to locate records with no value in a column.
SELECT *
FROM students
WHERE marks IS NULL;
This query returns students whose marks are not stored in the table.
Use this when you want to:
- find missing data
- check incomplete records
- clean or validate tables
Fetching Available Data Using IS NOT NULL
It works in the opposite way. It returns only rows that contain actual values.
SELECT *
FROM students
WHERE marks IS NOT NULL;
This query is useful when generating reports or performing calculations, since it ignores empty entries.
Why = NULL Does Not Work
Many beginners try to compare NULL using the equals sign. However, SQL does not allow this type of comparison.
-- This will not work
WHERE marks = NULL;
Instead, always use the proper condition to check missing values.
Real World Example
Suppose you want a list of students who shared their email addresses.
SELECT *
FROM students
WHERE email IS NOT NULL;
This approach helps you avoid empty contact details. As a result, your data remains reliable.
Key Points to Remember
- NULL represents missing information
- Normal comparisons do not work with NULL
- IS NULL finds empty fields
- IS NOT NULL filters valid data
Once you understand these checks, you can manage incomplete data with confidence.
Check out our resources!
- Bootstrap Templates: Explore our Bootstrap Projects section.
- Free E-Books: Download your Free E-Books here.
