SQL NULL and LIKE Clause (CBSE Class 12 Computer Science - 083)

In real-world databases, some information may not be available at the time of data entry. SQL uses the special value NULL to represent missing or unknown data. SQL also provides the LIKE operator for searching text using patterns.

Learning Objectives

• Understand the meaning of NULL.
• Differentiate between NULL and other values.
• Retrieve records using IS NULL and IS NOT NULL.
• Search records using the LIKE operator.
• Use the % and _ wildcard characters.

---

Sample Student Table

StudentID	Name	Gender	Class	Section	City	Marks	Email	DeptID
101	Riya	F	XII	A	Jaipur	95	riya@gmail.com	1
102	Aman	M	XII	B	Delhi	91	NULL	2
103	Priya	F	XII	A	Jaipur	88	priya@gmail.com	1
104	Rahul	M	XII	C	Mumbai	76	NULL	3
105	Neha	F	XII	B	Delhi	84	neha@gmail.com	2
106	Arjun	M	XII	A	Jaipur	92	NULL	1
107	Simran	F	XII	C	Mumbai	81	simran@gmail.com	3
108	Karan	M	XII	B	Delhi	69	NULL	2

---

What is NULL?

NULL represents a missing, unknown, or unavailable value in a database. It does not mean zero, an empty string, or a blank space.

---

Difference Between NULL, Zero and Empty String

Value	Meaning
NULL	Unknown or missing value.
0	A numeric value.
''	An empty string (no characters).
Space (' ')	A character containing one blank space.

---

Why Can't We Use '=' with NULL?

Since NULL represents an unknown value, it cannot be compared using relational operators such as = or <>.

Incorrect Query

SELECT *
FROM Student
WHERE Email = NULL;

The above query will not return the expected result.

---

IS NULL

The IS NULL operator is used to retrieve records containing NULL values.

Syntax

SELECT column_name
FROM table_name
WHERE column_name IS NULL;

---

Example 1 : Students Without Email ID

SELECT Name, Email
FROM Student
WHERE Email IS NULL;

Output

Name	Email
Aman	NULL
Rahul	NULL
Arjun	NULL
Karan	NULL

---

IS NOT NULL

The IS NOT NULL operator retrieves records that contain valid (non-NULL) values.

Syntax

SELECT column_name
FROM table_name
WHERE column_name IS NOT NULL;

---

Example

SELECT Name, Email
FROM Student
WHERE Email IS NOT NULL;

---

LIKE Operator

The LIKE operator is used to search for text values matching a specified pattern.

---

Wildcards Used with LIKE

Wildcard	Meaning
%	Represents zero, one, or more characters.
_	Represents exactly one character.

---

Using % Wildcard

Example 1 : Names Starting with 'R'

SELECT *
FROM Student
WHERE Name LIKE 'R%';

Output

Name
Riya
Rahul

---

Example 2 : Names Ending with 'a'

SELECT *
FROM Student
WHERE Name LIKE '%a';

Output

Name
Riya
Priya
Neha

---

Example 3 : Names Containing 'ri'

SELECT *
FROM Student
WHERE Name LIKE '%ri%';

---

Using _ Wildcard

The underscore (_) represents exactly one character.

Example 1 : Four-Letter Names Starting with 'R'

SELECT *
FROM Student
WHERE Name LIKE 'R___';

This matches names containing four characters, beginning with R.

---

Example 2 : Second Letter is 'a'

SELECT *
FROM Student
WHERE Name LIKE '_a%';

---

Combining WHERE and LIKE

SELECT Name, City
FROM Student
WHERE City='Jaipur'
AND Name LIKE 'R%';

---

Common LIKE Patterns

Pattern	Meaning
'A%'	Starts with A
'%A'	Ends with A
'%A%'	Contains A anywhere
'A___'	Starts with A and has exactly four characters
'____'	Exactly four characters

---

Execution Flow

SELECT
   │
   ▼
WHERE
   │
   ▼
IS NULL / IS NOT NULL / LIKE
   │
   ▼
DISPLAY RESULT

---

Comparison of SQL Clauses

Clause	Purpose
IS NULL	Finds missing values.
IS NOT NULL	Finds available values.
LIKE	Searches using text patterns.

---

Common Errors

• Using '=' instead of IS NULL.
• Confusing NULL with zero.
• Confusing NULL with an empty string.
• Using % and _ incorrectly.
• Forgetting quotation marks in LIKE patterns.

---

Exam Tips

---

Frequently Asked Questions (FAQs)

1. What is NULL in SQL?

NULL represents missing, unknown, or unavailable information.

2. Which operator is used to search for NULL values?

The IS NULL operator.

3. Which operator searches for non-NULL values?

The IS NOT NULL operator.

4. What is the purpose of the LIKE operator?

LIKE searches for text matching a specified pattern.

5. What is the difference between % and _?

% represents zero or more characters, whereas _ represents exactly one character.

---

Summary

• NULL represents missing or unknown data.
• NULL cannot be compared using relational operators.
• IS NULL retrieves records with missing values.
• IS NOT NULL retrieves records containing valid values.
• LIKE performs pattern matching on text values.
• % matches zero or more characters, while _ matches exactly one character.
• These clauses are commonly used with the WHERE clause to retrieve specific records.