Working with NULL Values in SQL

When dealing with real-world data, it's common to come across missing or unknown values . In SQL, such values are represented by a special keyword: NULL.

🧠 What is NULL in SQL?

• NULL represents a missing , unknown , or not applicable value.
• It is not the same as :
  • An empty string ( '' )
  • Zero ( 0 )
• It means: "We don’t know what this value is yet."

🧪 Our Sample Table: students

id	name	age	course	marks
1	Rahul Sharma	21	Advanced SQL	90.00
2	Anjali Kapoor	22	Advanced SQL	NULL
3	Vikas Gupta	24	Advanced SQL	91.00
4	Riya Jain	20	NULL	92.50
5	Aman Verma	24	Python	NULL

🔍 Checking for NULL Values

✅ Use IS NULL

SELECT * FROM students 
 WHERE marks IS NULL ;  

Finds: Anjali Kapoor and Aman Verma (whose marks are not entered yet)

❌ Use IS NOT NULL

SELECT * FROM students 
 WHERE marks IS NOT NULL ;  

Finds: Students who have marks recorded.

⚠️ Important: Don’t use = NULL

This will not work:

-- WRONG! 
 WHERE marks = NULL ;  

SQL requires you to use IS NULL or IS NOT NULL because NULL is not a value ; it is a placeholder for unknown data.

🧮 Handling NULL in Calculations

Let’s say you want the average marks of all students:

SELECT AVG (marks) AS avg_marks 
 FROM students;  

✅ SQL will ignore NULL values by default in aggregate functions like AVG() , SUM() , COUNT() , etc.

📦 Use COALESCE() or IFNULL() to Replace NULL

If you want to replace NULL with a default value , use:

✅ MySQL:

SELECT name, IFNULL(marks, 0 ) AS final_marks 
 FROM students;  

✅ SQL Standard:

SELECT name, COALESCE (marks, 0 ) AS final_marks 
 FROM students;  

This will show 0 instead of NULL where marks are missing.

🔄 COUNT with NULLs

SELECT COUNT (marks) 
 FROM students;  

Only counts non-NULL marks.

If you want to count all rows :

SELECT COUNT ( * ) 
 FROM students;  

🧠 Summary

Operation	Syntax	Notes
Check NULL	IS NULL	For missing values
Check not NULL	IS NOT NULL	For present values
Replace NULL	COALESCE() or IFNULL()	Provide fallback values
Avoid	= NULL or != NULL	Won’t work correctly

💡 Real-Life Use Cases

Use Case	Query Example
Find students without marks	WHERE marks IS NULL
Show "N/A" if course is NULL	SELECT COALESCE(course, 'N/A') AS course_name
Count students with marks	SELECT COUNT(marks)

📝 Conclusion

Handling NULL values properly is crucial for data accuracy. It ensures your queries are reliable and your reports are correct, especially when working with real-world datasets where missing information is common.