MySQL IS NOT NULL

Last updated: October 19, 2026Oct 19, 2026 4 min read

IS NOT NULL selects rows where a column has a value. It is how you find complete records, count filled-in fields and skip unknowns before calculating. It is the opposite of IS NULL.

1. Basic Usage

WHERE column IS NOT NULL

Table: applicants (6 rows)

SELECT name, portfolio_url FROM applicants WHERE portfolio_url IS NOT NULL;

Result: 4 rows

2. Why <> NULL Doesn't Work

SELECT name FROM applicants WHERE portfolio_url <> NULL;

<> NULL: always empty

SELECT name FROM applicants WHERE NOT (portfolio_url IS NULL);

NOT (… IS NULL): same as IS NOT NULL

3. Combining with Other Conditions

SELECT name, email, phone FROM applicants WHERE email IS NOT NULL AND phone IS NOT NULL;   -- fully contactable

Result: 3 rows

SELECT name, years_exp FROM applicants WHERE years_exp IS NOT NULL AND years_exp >= 4;

Result: 3 rows

4. COUNT(column) Counts Non-NULL Values

SELECT COUNT(*)             AS applicants,
       COUNT(email)         AS with_email,
       COUNT(phone)         AS with_phone,
       COUNT(portfolio_url) AS with_portfolio,
       COUNT(referred_by)   AS referred
FROM applicants;

Result: 1 row

A data-completeness report as percentages:

SELECT ROUND(100 * COUNT(email) / COUNT(*))         AS email_pct,
       ROUND(100 * COUNT(phone) / COUNT(*))         AS phone_pct,
       ROUND(100 * COUNT(portfolio_url) / COUNT(*)) AS portfolio_pct
FROM applicants;

Result: 1 row

5. Counting Filled Fields per Row

IS NOT NULL returns 1 or 0, so you can add the results to score each record:

SELECT name,
       (email IS NOT NULL) + (phone IS NOT NULL) + (portfolio_url IS NOT NULL) + (years_exp IS NOT NULL) AS fields_filled
FROM applicants
ORDER BY fields_filled DESC, name;

Result: 6 rows

6. IS NOT NULL with Joins

After a LEFT JOIN, unmatched rows have NULLs on the right side. IS NOT NULL keeps only the matched ones, here applicants who were referred, with their referrer's name:

SELECT a.name AS applicant, r.name AS referred_by
FROM applicants a
LEFT JOIN applicants r ON r.id = a.referred_by
WHERE a.referred_by IS NOT NULL;

Result: 3 rows

7. IS NOT NULL in UPDATE

UPDATE applicants SET portfolio_url = LOWER(TRIM(portfolio_url)) WHERE portfolio_url IS NOT NULL;
SELECT COUNT(*) AS cleaned FROM applicants WHERE portfolio_url IS NOT NULL;

Result: 1 row

8. IS NOT NULL vs the NOT NULL Constraint

If a column should never be empty, enforce it with the NOT NULL constraint rather than filtering later.

9. Practice Exercises

SELECT name FROM applicants WHERE portfolio_url IS NOT NULL AND phone IS NULL;

SELECT COUNT(years_exp) AS known, ROUND(AVG(years_exp), 1) AS avg_years FROM applicants WHERE years_exp IS NOT NULL;

10. Summary

• IS NOT NULL keeps rows where the column has a value; <> NULL never works.
• COUNT(column) counts non-NULL values; COUNT(*) counts rows.
• IS NOT NULL returns 1/0, useful for completeness scores.
• Use it after LEFT JOINs to keep matched rows.
• Use the NOT NULL constraint to stop NULLs being stored at all.