LIKE Pattern Matching and NULL Handling

What Is It?

What Is Pattern Matching and Why Is NULL Special?

Sometimes you do not want an exact match. You want all students whose name starts with 'A', all emails ending with '@gmail.com', or all products containing the word 'Pro'. That is pattern matching, and SQL provides the LIKE operator with two wildcards (% and _) for exactly this.

The second half of this chapter tackles SQL's most confusing concept: NULL. NULL is not zero, not empty string, not anything — it represents unknown or missing. Comparing anything to NULL does not give TRUE or FALSE — it gives NULL. This breaks beginners' assumptions and causes real production bugs. You must learn IS NULL, IS NOT NULL, and the NULL-handling functions COALESCE, IFNULL, and NULLIF.

Master these two topics and you will write queries that match exactly the rows you want and handle missing data correctly.

Why Does It Matter?

Why This Matters

1. Search Bars Use LIKE

Every search bar on every website uses LIKE (or full-text search, which builds on LIKE). When you search 'iphone' on Flipkart and it shows iPhone 13, iPhone 15 Pro Max, iPhone Case — that is WHERE name LIKE '%iphone%'.

2. NULL Is Everywhere in Real Data

Your database WILL have NULLs. Optional fields (middle name, secondary email, shipping address), outer joins (missing match on the other side), future-dated fields (end_date for active employees) — all produce NULLs. Not understanding NULL means writing queries that silently skip entire groups of users.

3. Failing the NULL Interview Question

"What is the difference between WHERE salary = NULL and WHERE salary IS NULL?" is asked in every intermediate SQL interview. If you answer wrong, the interview is over. Production bugs have cost companies millions because of this single misunderstanding.

4. COALESCE Is Your Best Friend

When a user's middle_name is NULL, you want to display the first name only, not "Aarav NULL Sharma". COALESCE(middle_name, '') returns empty string if NULL. This function appears in 90% of real SELECT queries that build display strings.

Detailed Explanation

Detailed Explanation

Reference Table: students

We will use a students table for this chapter. Notice the intentional NULLs.

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100),
    course VARCHAR(40),
    city VARCHAR(30),
    marks INT
);

INSERT INTO students VALUES
(1, 'Aarav Sharma', 'aarav@gmail.com', 'Computer Science', 'Mumbai', 85),
(2, 'Priya Patel', 'priya.p@yahoo.com', 'Mechanical', 'Ahmedabad', 72),
(3, 'Rohan Gupta', 'rohan@gmail.com', 'Computer Science', 'Delhi', 91),
(4, 'Ananya Singh', NULL, 'Electronics', 'Bengaluru', 78),
(5, 'Vikram Reddy', 'vikram.r@outlook.com', 'Computer Science', 'Hyderabad', NULL),
(6, 'Sneha Iyer', 'sneha@gmail.com', 'Civil', NULL, 65),
(7, 'Karan Mehta', 'karan_m@yahoo.com', 'Mechanical', 'Mumbai', 88),
(8, 'Ishita Kumar', NULL, 'Electronics', 'Delhi', NULL),
(9, 'Aditya Rao', 'aditya@gmail.com', 'Computer Science', 'Pune', 70),
(10, 'Diya Nair', 'diya_n@outlook.com', 'Civil', 'Chennai', 82);

1. The LIKE Operator Basics

LIKE performs pattern matching on strings. Use it in WHERE clauses with two wildcards:

• % matches zero or more of any character
• _ matches exactly one of any character

Common Patterns

-- Names starting with 'A'
WHERE name LIKE 'A%'

-- Names ending with 'a'
WHERE name LIKE '%a'

-- Names containing 'ro'
WHERE name LIKE '%ro%'

-- Names starting with 'A' and having exactly 5 chars total
WHERE name LIKE 'A____'  -- 'A' plus 4 underscores = 5 chars

-- Second letter is 'o'
WHERE name LIKE '_o%'

2. Concrete Examples on students

-- Names starting with 'A'
SELECT name FROM students WHERE name LIKE 'A%';
-- Returns: Aarav Sharma, Ananya Singh, Aditya Rao (3 rows)

-- Gmail addresses
SELECT name, email FROM students
WHERE email LIKE '%@gmail.com';
-- Returns: Aarav, Rohan, Sneha, Aditya (4 rows)

-- Names with exactly 11 characters
SELECT name FROM students WHERE name LIKE '___________';
-- 11 underscores — matches Priya Patel, Sneha Iyer, Diya Nair

3. NOT LIKE

Invert LIKE with NOT:

-- Non-gmail addresses
SELECT name, email FROM students
WHERE email NOT LIKE '%@gmail.com';

4. ESCAPE for Literal % or _

What if you want to search for a literal % (like in discount codes)? Use ESCAPE:

-- Match strings containing '50%' literally
WHERE coupon LIKE '%50\%%' ESCAPE '\\'

-- Or choose your own escape character
WHERE coupon LIKE '%50!%%' ESCAPE '!'

The ESCAPE clause tells SQL: "treat this character as escape." The character immediately after it is literal, not a wildcard.

5. Case Sensitivity

This is database-dependent and important:

• MySQL: LIKE is case-insensitive by default for VARCHAR columns (using the default collation utf8mb4_0900_ai_ci — 'ci' = case-insensitive). WHERE name LIKE 'a%' matches 'Aarav'.
• PostgreSQL: LIKE is case-sensitive. WHERE name LIKE 'a%' will NOT match 'Aarav'. Use ILIKE for case-insensitive matching (PostgreSQL only).
• SQL Server: Depends on collation. Default is case-insensitive.

For portable, case-insensitive matching, wrap both sides in LOWER():

WHERE LOWER(name) LIKE LOWER('a%')

6. Performance Note

Patterns starting with % (like '%gmail.com') cannot use a regular B-tree index — the database must scan every row. Patterns anchored at the start ('Aarav%') CAN use an index. For high-volume text search with leading wildcards, use a full-text index (covered in advanced chapters).

7. NULL — The Three-Valued Logic

SQL uses three-valued logic: TRUE, FALSE, and NULL (unknown). Most programming languages have only TRUE/FALSE. In SQL:

• 5 = 5 is TRUE
• 5 = 3 is FALSE
• 5 = NULL is NULL (not FALSE!)
• NULL = NULL is NULL (not TRUE!)

The WHERE clause keeps only rows where the condition is TRUE. NULL is NOT TRUE, so rows with NULL in comparisons are silently dropped.

8. IS NULL and IS NOT NULL

Since = NULL never works, SQL provides dedicated operators:

-- Find students with missing email
SELECT name FROM students WHERE email IS NULL;
-- Returns: Ananya Singh, Ishita Kumar

-- Find students with emails
SELECT name FROM students WHERE email IS NOT NULL;
-- Returns: the other 8 students

NEVER write WHERE email = NULL — it always returns 0 rows.

9. COALESCE — First Non-NULL

COALESCE(val1, val2, val3, ...) returns the first non-NULL value from the argument list.

-- Show email, or 'No email' if NULL
SELECT name, COALESCE(email, 'No email') AS contact FROM students;

-- Show marks, or 0 if NULL
SELECT name, COALESCE(marks, 0) AS marks FROM students;

COALESCE is standard SQL — works on every database. Use it over IFNULL for portability.

10. IFNULL — MySQL-Specific Shortcut

IFNULL(value, default) is MySQL's 2-argument version:

SELECT IFNULL(marks, 0) FROM students;
-- Equivalent to COALESCE(marks, 0)

IFNULL only takes 2 arguments. COALESCE takes any number. For portability, prefer COALESCE.

11. NULLIF — The Opposite

NULLIF(a, b) returns NULL if a = b, otherwise returns a.

-- Turn empty string into NULL (common data cleaning trick)
SELECT NULLIF(email, '') FROM students;

-- Avoid division by zero
SELECT marks / NULLIF(total_marks, 0) FROM students;
-- If total_marks is 0, NULLIF returns NULL, and dividing by NULL gives NULL (safer than error)

12. NULL in Aggregate Functions

Aggregate functions (COUNT, SUM, AVG, MIN, MAX) skip NULLs — except COUNT(*):

• COUNT(*) — all rows
• COUNT(column) — non-NULL rows
• SUM(column) — sum of non-NULL values
• AVG(column) — average of non-NULL values (NULL not treated as 0)

This matters: if you want NULLs counted as 0 in an AVG, you must handle them explicitly: AVG(COALESCE(marks, 0)).

13. NULL in Arithmetic and Concatenation

Any arithmetic with NULL yields NULL. 5 + NULL = NULL. NULL * 10 = NULL. String concatenation with NULL also gives NULL: CONCAT('Hello ', NULL) = NULL.

This is why you wrap NULL-able columns with COALESCE in display strings:

SELECT CONCAT(first_name, ' ', COALESCE(middle_name, ''), ' ', last_name) AS full_name FROM users;

Code Examples

-- Names starting with 'A'
SELECT name FROM students WHERE name LIKE 'A%';

-- Names ending with 'a'
SELECT name FROM students WHERE name LIKE '%a';

-- Names containing 'an'
SELECT name FROM students WHERE name LIKE '%an%';

-- Names where second letter is 'o'
SELECT name FROM students WHERE name LIKE '_o%';

-- Names exactly 11 characters long
SELECT name FROM students WHERE name LIKE '___________';

-- Gmail users
SELECT name, email FROM students
WHERE email LIKE '%@gmail.com';

-- Non-gmail users
SELECT name, email FROM students
WHERE email NOT LIKE '%@gmail.com'
  AND email IS NOT NULL;

-- WRONG: returns 0 rows even though 2 students have NULL email
SELECT name FROM students WHERE email = NULL;

-- WRONG: same problem
SELECT name FROM students WHERE email != NULL;

-- CORRECT:
SELECT name FROM students WHERE email IS NULL;
SELECT name FROM students WHERE email IS NOT NULL;

-- Replace NULL email with 'No email on file'
SELECT 
    name,
    COALESCE(email, 'No email on file') AS contact,
    COALESCE(marks, 0) AS marks,
    COALESCE(city, 'Unknown') AS city
FROM students;

-- IFNULL is the MySQL 2-arg version of COALESCE
SELECT
    name,
    IFNULL(email, 'N/A') AS email,
    IFNULL(marks, 0) AS marks
FROM students
WHERE id IN (4, 5, 8);

-- Assume total_marks column exists; avoid divide-by-zero
-- NULLIF(x, 0) returns NULL when x = 0

-- Illustrative example (we do not have total_marks in students)
SELECT 
    marks,
    NULLIF(marks, 0) AS safe_marks,
    marks / NULLIF(marks, 100) AS ratio_if_not_100
FROM students
WHERE id IN (1, 3);

-- 5-letter first names starting with specific letter (ignoring last name)
-- Name 'Aarav Sharma' - first name is 'Aarav' (5 chars)

-- All names with 'a' as the 3rd character
SELECT name FROM students WHERE name LIKE '__a%';

-- Names where 2nd char is 'r' (like 'Priya', 'Aray...')
SELECT name FROM students WHERE name LIKE '_r%';

-- marks has 2 NULLs (Vikram id 5, Ishita id 8)
SELECT
    COUNT(*) AS total_rows,
    COUNT(marks) AS marks_count,
    AVG(marks) AS avg_marks_ignoring_nulls,
    AVG(COALESCE(marks, 0)) AS avg_marks_null_as_zero
FROM students;

Common Mistakes

-- Finding students without email
SELECT name FROM students WHERE email = NULL;

SELECT name FROM students WHERE email IS NULL;

-- In PostgreSQL, this misses 'Aarav'
SELECT name FROM students WHERE name LIKE 'aarav%';

-- Portable: wrap in LOWER()
SELECT name FROM students WHERE LOWER(name) LIKE LOWER('aarav%');

-- PostgreSQL-specific: ILIKE
SELECT name FROM students WHERE name ILIKE 'aarav%';

-- Rohan expects this to find 'Aarav Sharma'
SELECT name FROM students WHERE name LIKE 'Aarav';

-- If you want an exact match, use =
SELECT name FROM students WHERE name = 'Aarav Sharma';

-- If you want 'Aarav anything', use wildcard
SELECT name FROM students WHERE name LIKE 'Aarav%';

-- Building full name: some middle_names are NULL
SELECT CONCAT(first_name, ' ', middle_name, ' ', last_name) AS full_name
FROM users;

SELECT CONCAT(
    first_name, ' ',
    COALESCE(middle_name, ''), ' ',
    last_name
) AS full_name FROM users;

-- Or use CONCAT_WS (MySQL) which skips NULLs:
SELECT CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name FROM users;

-- Find students not in banned list (banned has NULL rows)
SELECT name FROM students
WHERE id NOT IN (SELECT student_id FROM banned);

-- Filter NULLs in subquery
SELECT name FROM students
WHERE id NOT IN (SELECT student_id FROM banned WHERE student_id IS NOT NULL);

-- Or use NOT EXISTS (handles NULLs correctly)
SELECT name FROM students s
WHERE NOT EXISTS (SELECT 1 FROM banned b WHERE b.student_id = s.id);

Summary