【SQL IN Clause Tutorial】Handle Multiple Conditions Smartly! A Better Way to Write WHERE Clauses with IN and NOT IN

When developing websites, you'll frequently encounter situations where you need to find data that matches one of several options, such as "display a list of products from specific categories" or "send a campaign notification only to users in Tokyo, Kanagawa, and Chiba."

In such cases, how would you write the WHERE clause?

WHERE category = 'Stationery' OR category = 'Appliances' OR category = 'Food'

Of course, this works correctly. But what if the number of options increases to five, or ten? The code would become a long, hard-to-read, and difficult-to-maintain chain of ORs. The hero that solves this problem in an instant is today's main topic: the IN clause.

This article will thoroughly explain everything from the basic usage of the IN clause and its opposite, the NOT IN clause, to advanced techniques using subqueries, and even the "NULL trap" that many people fall into, all with copy-and-paste code. Let's master the IN clause and evolve your SQL to be smarter and more readable!

Preparation: Let's Get Our Product Data Ready

To try out the operations, we first need some data. This time, we'll create a simple products table to manage a product list and a suppliers table to manage information about the suppliers. Having multiple categories and suppliers will make it easier to appreciate the convenience of the IN clause.

-- Drop tables if they exist (for repeatable testing)
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS suppliers;

-- Create suppliers table
CREATE TABLE suppliers (
  id INTEGER PRIMARY KEY,
  supplier_name TEXT NOT NULL,
  region TEXT NOT NULL -- 'Kanto', 'Kansai'
);

-- Create products table
CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  category TEXT NOT NULL,
  price INTEGER,
  supplier_id INTEGER,
  notes TEXT -- A notes column that allows NULL
);

-- Insert supplier data
INSERT INTO suppliers (id, supplier_name, region) VALUES
(101, 'Tokyo Office Supply', 'Kanto'),
(102, 'Osaka Kitchenware', 'Kansai'),
(103, 'Chiba Food Service', 'Kanto'),
(104, 'Kyoto Traditional Stationery', 'Kansai');

-- Insert product data
INSERT INTO products (id, product_name, category, price, supplier_id, notes) VALUES
(1, 'Amazing Ballpoint Pen', 'Stationery', 150, 101, 'Smooth writing'),
(2, 'Magic Notebook', 'Stationery', 300, 104, NULL),
(3, 'Ultimate Rice Cooker', 'Appliances', 25000, 102, 'Very popular'),
(4, 'Miracle Apple', 'Food', 500, 103, 'Limited quantity'),
(5, 'USB-C Hub', 'PC Peripherals', 4000, 101, NULL),
(6, 'Traditional Brush', 'Stationery', 1200, 104, 'Handcrafted'),
(7, 'Osaka Mixed Juice', 'Food', 350, NULL, 'Supplier unknown');

Now we're all set to extract data with various conditions.

【The Basics】Specifying Multiple Options with the IN Clause

The basic syntax of the IN clause is very simple.

WHERE column_name IN (value1, value2, value3, ...);

You just put a list of the values you're looking for inside the parentheses. This means "it's a match if the column's value is equal to any one of the values in this list." The example from the introduction that used many ORs can be written cleanly with the IN clause as follows.

Scenario: "Find all products that are in either the 'Stationery' or 'Food' category."

SELECT * FROM products
WHERE category IN ('Stationery', 'Food');

Result:

id | product_name          | category   | price | supplier_id | notes
---|-----------------------|------------|-------|-------------|------------------
1  | Amazing Ballpoint Pen | Stationery | 150   | 101         | Smooth writing
2  | Magic Notebook        | Stationery | 300   | 104         | NULL
4  | Miracle Apple         | Food       | 500   | 103         | Limited quantity
6  | Traditional Brush     | Stationery | 1200  | 104         | Handcrafted
7  | Osaka Mixed Juice     | Food       | 350   | NULL        | Supplier unknown

What do you think? Isn't it much easier to understand the intent compared to chaining multiple ORs? This "list" concept is the foundation of the IN clause.

【The Opposite Condition】Specifying Everything Except the List with NOT IN

Of course, there are times when you want to find things that are **not** in a list. That's when you use the NOT IN clause.

Scenario: "Find all products that are in categories **other than** 'Stationery' and 'Food'."

SELECT * FROM products
WHERE category NOT IN ('Stationery', 'Food');

Result:

id | product_name        | category        | price | supplier_id | notes
---|---------------------|-----------------|-------|-------------|--------------
3  | Ultimate Rice Cooker| Appliances      | 25000 | 102         | Very popular
5  | USB-C Hub           | PC Peripherals  | 4000  | 101         | NULL

By simply adding NOT before IN, you can easily specify a negative condition. This is also a very useful feature.

【Application】Combining the IN Clause with a Subquery

The true power of the IN clause is unleashed when it's combined with a subquery. A subquery is another SELECT statement nested inside an SQL statement.

Using this, instead of hardcoding a list of values after IN, you can use the result of another query as the list.

Scenario: "Find all products supplied by suppliers located in the 'Kanto' region."

This search can be thought of in two steps:

1. First, get a list of supplier IDs from the suppliers table where the region is 'Kanto'.
2. Next, use that list of IDs to find the corresponding products in the products table.

You can embed Step 1 as a subquery inside the IN clause.

SELECT * FROM products
WHERE supplier_id IN (SELECT id FROM suppliers WHERE region = 'Kanto');

Result:

id | product_name        | category        | price | supplier_id | notes
---|---------------------|-----------------|-------|-------------|------------------
1  | Amazing Ballpoint Pen | Stationery      | 150   | 101         | Smooth writing
4  | Miracle Apple         | Food            | 500   | 103         | Limited quantity
5  | USB-C Hub           | PC Peripherals  | 4000  | 101         | NULL

The database first executes the subquery inside the parentheses, SELECT id FROM suppliers WHERE region = 'Kanto', and gets a list of IDs (in this case, `(101, 103)`). Then, the outer query effectively runs as WHERE supplier_id IN (101, 103). The ability to dynamically generate this condition list is the biggest advantage of combining it with a subquery.

The Most Important Warning! The Terrifying Trap of `NOT IN` and `NULL`

The IN clause is intuitive and easy to use, but the NOT IN clause has a very nasty "trap" that many developers fall into at least once. This happens when the list contains a `NULL` value.

To put it bluntly, if a list used with `NOT IN` contains even one `NULL`, the entire query will return zero records.

Why? In SQL, NULL is a special value representing "no value," and any comparison with `NULL` (e.g., `id = NULL` or `id <> NULL`) always results in `UNKNOWN`, not `TRUE` or `FALSE`.

A condition like WHERE id NOT IN (1, 2, NULL) is evaluated internally in a way that is roughly equivalent to WHERE id <> 1 AND id <> 2 AND id <> NULL. Even if a record's `id` is `3`, the final part, `3 <> NULL`, evaluates to `UNKNOWN`. Since the overall condition can never become `TRUE`, no rows will ever match.

This is especially dangerous when combined with the subqueries we just used.

Scenario: "Find all products **except** those supplied by an unknown supplier (where supplier_id is NULL)."

This might look correct at first glance, but...

-- 【TRAP!】This query will not work as intended!
SELECT * FROM products
WHERE supplier_id NOT IN (SELECT id FROM suppliers WHERE region = 'Kansai');

-- The subquery above returns (102, 104), but if the suppliers table
-- had a row with a NULL id, the list would become (102, 104, NULL),
-- and this query would return 0 results.

If there's a possibility that the subquery result could include `NULL`, the query will return zero rows. Our sample data doesn't have a supplier with a `NULL` id, but our `products` table does have a record with a `NULL` `supplier_id` (the product with ID 7). Let's see how this record is treated by `NOT IN`.

Solution: When using `NOT IN` with a subquery, it is an ironclad rule to either **ensure the subquery can never return `NULL` values by adding a condition like `WHERE id IS NOT NULL` inside the subquery**, or to use a different syntax like `NOT EXISTS`.

【Interactive Playground】Master the IN Clause 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_in_test.html, and open it in your browser. Your own personal SQL environment will launch, pre-loaded with the products and suppliers tables we've been using in this article.

Try changing the contents of the `IN` or `NOT IN` lists, or modify the subquery conditions, and see how the results change!

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>SQL IN Clause 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: #8e44ad; }
    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: #9b59b6; color: white; border: none; padding: 12px 22px; font-size: 16px; border-radius: 6px; cursor: pointer; transition: background-color 0.2s; }
    button:hover { background-color: #8e44ad; }
    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">-- Let's find products supplied by suppliers in the 'Kansai' region
SELECT * FROM products
WHERE supplier_id IN (SELECT id FROM suppliers WHERE region = 'Kansai');
  </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 products;
          DROP TABLE IF EXISTS suppliers;

          CREATE TABLE suppliers (id INTEGER PRIMARY KEY, supplier_name TEXT NOT NULL, region TEXT NOT NULL);
          CREATE TABLE products (id INTEGER PRIMARY KEY, product_name TEXT NOT NULL, category TEXT NOT NULL, price INTEGER, supplier_id INTEGER, notes TEXT);

          INSERT INTO suppliers (id, supplier_name, region) VALUES (101, 'Tokyo Office Supply', 'Kanto'), (102, 'Osaka Kitchenware', 'Kansai'), (103, 'Chiba Food Service', 'Kanto'), (104, 'Kyoto Traditional Stationery', 'Kansai');
          INSERT INTO products (id, product_name, category, price, supplier_id, notes) VALUES (1, 'Amazing Ballpoint Pen', 'Stationery', 150, 101, 'Smooth writing'), (2, 'Magic Notebook', 'Stationery', 300, 104, NULL), (3, 'Ultimate Rice Cooker', 'Appliances', 25000, 102, 'Very popular'), (4, 'Miracle Apple', 'Food', 500, 103, 'Limited quantity'), (5, 'USB-C Hub', 'PC Peripherals', 4000, 101, NULL), (6, 'Traditional Brush', 'Stationery', 1200, 104, 'Handcrafted'), (7, 'Osaka Mixed Juice', 'Food', 350, NULL, 'Supplier unknown');
        `;
        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 IN and NOT IN clauses for smartly handling multiple conditions.

• IN Clause: It can cleanly rewrite verbose conditions like WHERE A = 1 OR A = 2 OR A = 3 into the much more readable WHERE A IN (1, 2, 3).
• NOT IN Clause: The opposite of IN, it extracts records that are not included in the list.
• Integration with Subqueries: The list for the IN clause can be dynamically generated from the result of another SELECT statement, which is extremely powerful.
• The `NULL` Trap: A critical warning that if a list in a NOT IN clause contains a `NULL`, the result will be zero rows. This is especially important to watch out for when using subqueries.

The IN clause is a fundamental technique for making your code simpler and more readable. The combination with subqueries, in particular, is an unavoidable path when extracting data with complex conditions. And knowing the relationship between NOT IN and NULL` is crucial knowledge that can drastically change the time you spend debugging. Be sure to master the content of this article to level up your SQL skills!