🇯🇵 日本語 | 🇺🇸 English | 🇪🇸 Español | 🇵🇹 Português | 🇹🇭 ไทย | 🇨🇳 中文

【SQL NULL Tutorial】Master Data Extraction by Conquering IS NULL & IS NOT NULL

When you start learning SQL, you'll inevitably encounter a mysterious entity: `NULL`. It's used to represent a state where data doesn't exist, like "a user with an unregistered phone number" or "a product with nothing written in the notes field." However, handling `NULL` is very special, and many beginners hit a wall, wondering, "Why can't I retrieve the data I expect?"

The biggest reason for this is trying to compare `NULL` with = (equals), as if it were the same as "0" or an "empty string ('')". In the world of SQL, a philosophical rule exists: `NULL` is not equal to any other value, not even to itself.

This article will thoroughly explain the nature of `NULL` and the dedicated operators for handling it correctly, `IS NULL` and `IS NOT NULL`, using copy-and-paste code. It's no exaggeration to say that those who master `NULL` master SQL. Let's take this opportunity to definitively master the skill of checking for data existence!

Preparation: Let's Get Member Data with NULLs Ready

To see how `NULL` actually behaves, let's first prepare some sample data. This time, we'll create a simple `members` table to manage member information. The key is to intentionally include records with `NULL` values (where data is empty), such as for phone numbers and last login dates.

-- Drop the members table if it exists (for repeatable testing)
DROP TABLE IF EXISTS members;

-- Create a new members table
CREATE TABLE members (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  phone_number TEXT, -- Allows NULL
  last_login DATE,   -- Allows NULL
  points INTEGER DEFAULT 0
);

-- Insert initial data
INSERT INTO members (id, name, email, phone_number, last_login, points) VALUES
(1, 'Yamada Taro', 'yamada@example.com', '090-1111-2222', '2025-06-25', 150),
(2, 'Suzuki Hanako', 'suzuki@example.com', NULL, '2025-07-01', 50),
(3, 'Sato Jiro', 'sato@example.com', '080-3333-4444', NULL, 200),
(4, 'Ito Sakura', 'ito@example.com', NULL, NULL, 0),
(5, 'Watanabe Kenta', 'watanabe@example.com', '', '2025-06-28', 300); -- Phone number is an empty string

This data includes users with a `NULL` phone number (IDs 2, 4) and users with a `NULL` last login date (IDs 3, 4). Also, note that the phone number for Watanabe (ID 5) is not `NULL` but an **empty string** (''). This distinction will be important later.

The Biggest Pitfall: Why Can't You Search with `= NULL`?

It's no exaggeration to say that 100% of SQL beginners fall into this trap: writing WHERE phone_number = NULL. At a glance, it looks like it should work correctly, but if you run this query, not a single record will be returned.

-- 【WRONG!】This query will not work as intended
SELECT * FROM members WHERE phone_number = NULL;

Why is that? It's because `NULL` represents a special state of "no value" or "unknown." Since `NULL` is not a value, any comparison with it (even a comparison with `NULL` itself) results not in `TRUE` or `FALSE`, but in a third state: `UNKNOWN`. The WHERE clause only extracts records for which the condition is `TRUE`, so records that evaluate to `UNKNOWN` are never selected.

Think of it like a survey question asking for "favorite food." If you want to find people who left it "blank," you can't find them by searching for people who wrote the word "blank." You need to search for the state of "being blank" itself.

【The Basics】The Correct Way to Use `IS NULL` and `IS NOT NULL`

SQL provides dedicated operators for checking for this special state of `NULL`. They are `IS NULL` and `IS NOT NULL`.

`IS NULL`: Extracting Records Where a Value is NULL

If you want to find records where a column's value is `NULL` (the data is empty), you must use `IS NULL`, not `= NULL`.

Scenario: "Find all members who have not registered a phone number (where `phone_number` is `NULL`)."

SELECT * FROM members WHERE phone_number IS NULL;

Result:

id | name         | email             | phone_number | last_login | points
---|--------------|-------------------|--------------|------------|-------
2  | Suzuki Hanako| suzuki@example.com| NULL         | 2025-07-01 | 50
4  | Ito Sakura   | ito@example.com   | NULL         | NULL       | 0

This time, the users with IDs 2 and 4 were correctly extracted as intended. Note that Watanabe (ID 5) is not included in the result because their phone number is an "empty string," not `NULL`.

`IS NOT NULL`: Extracting Records Where a Value is Not NULL

Conversely, if you want to find records where there is some data (i.e., the value is not empty), you use `IS NOT NULL`.

Scenario: "Find all members who have a last login record (where `last_login` is not `NULL`)."

SELECT * FROM members WHERE last_login IS NOT NULL;

Result:

id | name           | email                | phone_number   | last_login | points
---|----------------|----------------------|----------------|------------|-------
1  | Yamada Taro    | yamada@example.com   | 090-1111-2222  | 2025-06-25 | 150
2  | Suzuki Hanako  | suzuki@example.com   | NULL           | 2025-07-01 | 50
5  | Watanabe Kenta | watanabe@example.com |                | 2025-06-28 | 300

As you can see, using `IS NOT NULL` allows you to efficiently select only the records that do not have missing data.

Application: Understanding the Difference Between `NULL`, Empty Strings, and `0`

Beginners often treat "empty strings ('')" and the "number 0" as if they are the same as `NULL`. In SQL, these are treated as distinctly different things.

The phone number for Watanabe (ID 5) is an empty string, not `NULL`. Therefore, the following query will only match the user with ID 5.

SELECT * FROM members WHERE phone_number = '';

Similarly, the points for Ito (ID 4) is `0`, but this is different from `NULL`. This difference becomes even clearer when using aggregate functions like `COUNT`.

-- Total members, members with a registered phone number, and sum of points
SELECT COUNT(*), COUNT(phone_number), SUM(points) FROM members;

Result:

COUNT(*) | COUNT(phone_number) | SUM(points)
--------|---------------------|------------
5        | 3                   | 650

COUNT(*) counts all records, so it's 5, but COUNT(phone_number) ignores `NULL` values, so it's 3 (IDs 1, 3, and 5). Similarly, `SUM(points)` also excludes records with `NULL` from its calculation. This behavior is very important in data analysis, so be sure to remember it.

【Interactive Playground】Let's Check the Behavior of NULL by Running SQL in Your Browser!

Now it's time to turn knowledge into a skill! Copy the entire HTML code below, save it as a file like sql_null_test.html, and open it in your browser. Your own personal SQL environment will launch, pre-loaded with the members table we've been using in this article.

See for yourself how the results differ between = NULL and IS NULL, and why COUNT(*) and COUNT(column_name) give different results. Let's find out by getting your hands dirty!

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>SQL NULL Online Playground</title>
  <style>
    body { font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, "Helvetica Neue", Arial, sans-serif; line-height: 1.7; color: #333; max-width: 800px; margin: 2rem auto; padding: 0 1rem; }
    h1 { color: #34495e; }
    textarea { width: 100%; height: 180px; font-family: "SF Mono", "Consolas", monospace; font-size: 16px; padding: 12px; border: 1px solid #ccc; border-radius: 6px; box-sizing: border-box; margin-bottom: 1rem; }
    button { background-color: #34495e; color: white; border: none; padding: 12px 22px; font-size: 16px; border-radius: 6px; cursor: pointer; transition: background-color 0.2s; }
    button:hover { background-color: #2c3e50; }
    button:disabled { background-color: #bdc3c7; cursor: not-allowed; }
    #result-container { margin-top: 2rem; border: 1px solid #ddd; padding: 1rem; border-radius: 6px; background: #fdfdfd; min-height: 50px; }
    #error-message { color: #e74c3c; font-weight: bold; }
    table { border-collapse: collapse; width: 100%; margin-top: 1rem; }
    th, td { border: 1px solid #ddd; padding: 10px; text-align: left; }
    th { background-color: #f2f2f2; }
    tr:nth-child(even) { background-color: #f9f9f9; }
  </style>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.10.3/sql-wasm.js"></script>
</head>
<body>

  <h1>Let's Try SQL!</h1>
  <p>Enter an SQL statement in the textarea below and click the "Execute" button. Try out different conditions!</p>

  <textarea id="sql-input">-- Find members who have never logged in (last_login is NULL)
SELECT * FROM members WHERE last_login IS NULL;
  </textarea>
  
  <button id="execute-btn">Execute</button>
  
  <div id="result-container">
    <p id="error-message"></p>
    <div id="result-output"></div>
  </div>

  <script>
    const sqlInput = document.getElementById('sql-input');
    const executeBtn = document.getElementById('execute-btn');
    const errorMsg = document.getElementById('error-message');
    const resultOutput = document.getElementById('result-output');

    let db;

    async function initDb() {
      executeBtn.disabled = true;
      executeBtn.textContent = 'DB Initializing...';
      try {
        const SQL = await initSqlJs({
          locateFile: file => `https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.10.3/${file}`
        });
        db = new SQL.Database();
        
        const setupSql = `
          DROP TABLE IF EXISTS members;
          CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE, phone_number TEXT, last_login DATE, points INTEGER DEFAULT 0);
          INSERT INTO members (id, name, email, phone_number, last_login, points) VALUES
          (1, 'Yamada Taro', 'yamada@example.com', '090-1111-2222', '2025-06-25', 150),
          (2, 'Suzuki Hanako', 'suzuki@example.com', NULL, '2025-07-01', 50),
          (3, 'Sato Jiro', 'sato@example.com', '080-3333-4444', NULL, 200),
          (4, 'Ito Sakura', 'ito@example.com', NULL, NULL, 0),
          (5, 'Watanabe Kenta', 'watanabe@example.com', '', '2025-06-28', 300);
        `;
        db.run(setupSql);
        
        executeBtn.disabled = false;
        executeBtn.textContent = 'Execute';
        resultOutput.innerHTML = '<p>Ready! Feel free to try out your own SQL queries.</p>';

      } catch (err) {
        errorMsg.textContent = 'Failed to initialize database: ' + err.message;
        console.error(err);
      }
    }

    function executeSql() {
      if (!db) return;
      
      const sql = sqlInput.value;
      errorMsg.textContent = '';
      resultOutput.innerHTML = '';

      try {
        const results = db.exec(sql);
        if (results.length === 0) {
          resultOutput.innerHTML = '<p>Query executed successfully, but no result set was returned.</p>';
          return;
        }
        
        results.forEach(result => {
          const table = document.createElement('table');
          const thead = document.createElement('thead');
          const tbody = document.createElement('tbody');
          
          const headerRow = document.createElement('tr');
          result.columns.forEach(colName => {
            const th = document.createElement('th');
            th.textContent = colName;
            headerRow.appendChild(th);
          });
          thead.appendChild(headerRow);
          
          result.values.forEach(row => {
            const bodyRow = document.createElement('tr');
            row.forEach(cellValue => {
              const td = document.createElement('td');
              td.textContent = cellValue === null ? 'NULL' : cellValue;
              bodyRow.appendChild(td);
            });
            tbody.appendChild(bodyRow);
          });
          
          table.appendChild(thead);
          table.appendChild(tbody);
          resultOutput.appendChild(table);
        });

      } catch (err) {
        errorMsg.textContent = 'SQL Error: ' + err.message;
        console.error(err);
      }
    }

    executeBtn.addEventListener('click', executeSql);
    
    initDb();
  </script>
</body>
</html>

Conclusion

In this article, we learned about the correct way to handle `NULL` in SQL, covering its concept and specific checking methods.

Correctly understanding the concept of `NULL` is an unavoidable step in mastering SQL. It might be a little confusing at first, but if you keep in mind that "`NULL` is not a value, but a state," you'll gradually get the hang of it. This skill is essential for correctly handling missing data and developing robust, bug-free applications. Your SQL level has just gone up another notch!

Related Templates