SQL NULL values

SQL Tutorial

 SQL NULL Values (Beginner → Advanced)

In SQL, NULL represents missing, unknown, or not applicable data.
It is not the same as 0, empty string (''), or false—this difference is very important for correctness and interviews.

 What is NULL in SQL?

NULL means no value or unknown value.

NULL ≠ 0
NULL ≠ ''
NULL ≠ FALSE

 Example of NULL Values

Table: employees

idnameemailsalary
1Rahulrahul@gmail.com50000
2AmitNULL45000
3Nehaneha@gmail.comNULL

  • Amit has no email

  • Neha’s salary is unknown

 Inserting NULL Values

  • NULL is explicitly inserted
  •  Column must allow NULL

 Checking NULL Values (Very Important)

 WRONG (Never works)

 CORRECT

 NOT NULL

  • NULL must be checked using IS NULL / IS NOT NULL

NULL with Comparison Operators

 Rows with salary = NULL are ignored

Why?
Because any comparison with NULL results in UNKNOWN.

NULL and Aggregate Functions

Table

salary
50000
40000
NULL
FunctionBehavior
COUNT(column)Ignores NULL
SUM()Ignores NULL
AVG()Ignores NULL
MIN() / MAX()Ignore NULL

Handling NULL with COALESCE()

Replace NULL with a value

  •  If salary is NULL, it becomes 0

IFNULL() / NVL() (Database Specific)

MySQL

Oracle

NULL with WHERE + Logic

  •  Rows with salary = NULL are excluded

Correct Handling

NULL vs DEFAULT

FeatureNULLDEFAULT
MeaningUnknown / MissingPredefined value
Value exists? NoYes
Used whenData not availableValue not provided
salary INT DEFAULT 0
  •  Better than allowing NULL in many cases

NOT NULL Constraint

  • Prevents NULL values
  •  Ensures data integrity

 Common Mistakes

  •  Using = with NULL
  •  Forgetting IS NULL
  •  Assuming NULL = 0
  •  Ignoring NULL in calculations
  •  Wrong COUNT() usage

 Interview Questions (Must Prepare)

  1. What is NULL in SQL?

  2. Difference between NULL and 0?

  3. How to check NULL values?

  4. Does COUNT(*) count NULL?

  5. How does NULL behave in comparisons?

  6. Difference between NULL and DEFAULT?

Real-Life Use Cases

  • Optional user fields (email, phone)

  • Missing data in reports

  • Incomplete transactions

  • API data storage

  • Data migration handling

 Summary

  • NULL means no value / unknown
  •  Use IS NULL / IS NOT NULL
  •  Comparisons with NULL return UNKNOWN
  •  Aggregates ignore NULL (except COUNT(*))
  •  Use COALESCE() to handle NULL safely
  • Essential for correct SQL logic & interviews

You may also like...