Module 10 Checking for Null Values (IS NULL & IS NOT NULL)

Module 10

Checking for Null Values (IS NULL & IS NOT NULL)

A step-by-step, hands-on SQL practice lesson — with a live query playground

SELECT * FROM customers WHERE phone IS NULL;

The first time a query of mine quietly returned zero rows for no obvious reason, the culprit was NULL. I had written WHERE phone = NULL, it looked perfectly reasonable, and MySQL didn't complain — it just gave me nothing back, every single time, no matter how many rows actually had a missing phone number. That was my introduction to one of SQL's quirkiest rules: NULL doesn't play by the same logic as everything else. This lesson is the explanation I wish I'd had that day.

Contents

1. Introduction
2. Requirements
3. Practice: Step by Step
4. Common Problems & Solutions
5. Conclusion
6. Homework & Quiz
7. Live SQL Playground
8. Related Lessons
9. FAQ

Introduction

What You'll Learn — and Why It Matters

My experience

Once I understood that NULL means "unknown" rather than "empty" or "zero," a lot of strange behavior I'd seen in earlier queries suddenly made sense — including a few of the NOT queries from Module 7 that quietly dropped rows I expected to see. IS NULL and IS NOT NULL are the two operators built specifically to deal with this, and honestly, I now check for them out of habit any time I'm filtering a column I know can have missing data.

🎯 Objective

By the end of this lesson, you will be able to:

• Explain what NULL actually represents in a database.
• Use IS NULL to find missing values, and IS NOT NULL to find rows that have a value.
• Understand why = NULL and != NULL never work as expected.
• Combine IS NULL / IS NOT NULL with AND, OR, and NOT from earlier modules.

⚠️ What to Avoid

Common mistakes

• Writing WHERE column = NULL — this is the mistake I made, and it silently returns nothing, every time.
• Treating NULL as the same thing as an empty string '' or the number 0 — they are not equivalent.
• Forgetting that aggregate functions like COUNT(column) skip NULL values entirely, which can make a table look smaller than it really is.

💡 Important to Know

Before you start

I explain it to myself this way: NULL isn't a value sitting in a cell — it's the absence of one. You can't ask "is the absence of a value equal to something?" with a regular equals sign, because there's nothing there to compare. That's exactly why SQL gives you two dedicated keywords, IS NULL and IS NOT NULL, instead of expecting = and != to handle it.

Requirements

What You'll Need Before Starting

Operating SystemWindows, macOS, or Linux — everything here works identically no matter which one you're on.

Database ServerMySQL installed and running. See How to Install MySQL if you haven't set it up yet.

Optional but recommendedXAMPP with phpMyAdmin, if you'd rather work visually than at the command line. See How to Install XAMPP.

Prior knowledgeComfortable with SELECT, WHERE, AND/OR, and ideally NOT from earlier modules, starting with Module 1.

Nothing needs to be installed to try the live playground further down this page — it runs directly in your browser.

Practice

Step-by-Step Practice

I'll use a small customer list for this one — it's realistic, since in almost every real database I've worked with, a "phone number" or "email" column ends up with a few missing entries sooner or later.

1 Open MySQL (CLI or phpMyAdmin)

Open a terminal and connect with the MySQL command-line client, or open phpMyAdmin at http://localhost/phpmyadmin/.

mysql -u root -p

The basic syntax patterns for this lesson look like this:

SELECT columns FROM table WHERE column IS NULL;
SELECT columns FROM table WHERE column IS NOT NULL;

2 Create a Database and Verify It

CREATE DATABASE module10_practice;
SHOW DATABASES;
USE module10_practice;

Now create a customers table and verify it — I always run DESCRIBE right after, just to confirm every column looks the way I expect before I put any data in it.

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(80),
  phone VARCHAR(20),
  signup_year INT
);
SHOW TABLES;
DESCRIBE customers;

3 Insert Sample Data

Notice a few rows below are missing a phone number or an email — that's deliberate. In a real signup form, not everyone fills in every optional field, and this table reflects that.

INSERT INTO customers (id, name, email, phone, signup_year) VALUES
(1, 'Sreymom', 'sreymom@mail.com', '012345678', 2023),
(2, 'Bunthoeun', NULL, '098765432', 2024),
(3, 'Kanha', 'kanha@mail.com', NULL, 2022),
(4, 'Vireak', 'vireak@mail.com', '011222333', 2024),
(5, 'Ratana', NULL, NULL, 2023);

4 Find Rows with a Missing Value: IS NULL

This finds every customer who never provided a phone number.

SELECT name, phone FROM customers
WHERE phone IS NULL;

5 Find Rows That Have a Value: IS NOT NULL

The opposite check — every customer who did provide an email.

SELECT name, email FROM customers
WHERE email IS NOT NULL;

6 Proving Why = NULL Doesn't Work

Run this exactly as written. It will return zero rows, even though we know at least two customers have a NULL email — that's the whole lesson in one query.

SELECT name, email FROM customers
WHERE email = NULL;

7 Combining IS NULL with AND

Customers who signed up in 2023 and never gave a phone number.

SELECT name, signup_year, phone FROM customers
WHERE signup_year = 2023 AND phone IS NULL;

8 Combining IS NOT NULL with OR and NOT

This pulls in customers missing either an email or a phone number — the people your support team would probably want to follow up with.

SELECT name, email, phone FROM customers
WHERE NOT (email IS NOT NULL AND phone IS NOT NULL);

9 Combine with Alias and Sorting

Bringing together everything from this series so far — column selection, an alias, an IS NOT NULL filter, and sorting.

SELECT name AS customer_name, signup_year
FROM customers
WHERE email IS NOT NULL
ORDER BY signup_year DESC;

Troubleshooting

Common Problems and Solutions

Every one of these is a mistake I've made myself at some point — NULL has a way of catching people off guard even after they think they've understood it.

Problem 1: WHERE email = NULL always returns zero rows

I expected this to find customers with a missing email, and instead I got nothing back — even though I could see blank emails in the table.

SolutionNULL can't be compared with =, because there's no value to compare against. Use WHERE email IS NULL instead.

Problem 2: WHERE email != NULL doesn't find customers with an email either

Same idea in reverse — I wanted everyone who did have an email, and this returned nothing.

Solution!= has the same problem as = when paired with NULL. Use WHERE email IS NOT NULL instead.

Problem 3: NULL rows disappear from my NOT queries

This one connects back to Module 7 — I wrote WHERE NOT phone = '011222333', and rows with a NULL phone quietly vanished from the results.

SolutionA NULL comparison evaluates to "unknown," not false, so NOT has nothing to flip. If you want those rows included, add OR phone IS NULL explicitly.

Problem 4: I mixed up NULL with an empty string

I ran WHERE email IS NULL and it missed a row where the email column held '' (an empty string) rather than a true missing value.

SolutionNULL and '' are different things — one means "no value was ever recorded," the other means "an empty piece of text was recorded." If your data mixes the two, you may need WHERE email IS NULL OR email = ''.

Problem 5: COUNT(column) gave a smaller number than I expected

I ran SELECT COUNT(phone) FROM customers expecting the total row count, and got a smaller number instead.

SolutionCOUNT(column) only counts rows where that specific column is not NULL. If you want the total number of rows regardless of missing values, use COUNT(*) instead.

Conclusion

What You Should Take Away

Looking back, the concept behind NULL is genuinely simple — it just means "we don't know" — but the practical habits around it took me a little longer to build. You should now be comfortable using IS NULL and IS NOT NULL to find missing or present values, and you should instinctively reach for them instead of = or != whenever a column might have gaps in it.

In Module 11, we'll build on everything from this series so far by looking at the IN operator — a cleaner way to write long chains of OR conditions on the same column.

Homework

Homework & Quiz

📝 Homework Task

Using the customers table from this lesson, write your own query that:

• Selects name, email, and phone.
• Finds customers who are missing both an email and a phone number.
• Sorts the results by signup_year, oldest first.

Try writing it yourself first — then check your work against the live playground below.

🧪 Quiz: Test Your Understanding (10 Questions)

Pick one answer per question, then click Submit to see your score instantly.

1. What does NULL represent in a database?

The number zero An empty string A missing or unknown value A deleted row

2. Which query correctly finds rows where phone is missing?

WHERE phone = NULL WHERE phone IS NULL WHERE phone != NULL WHERE phone = ''

3. What does WHERE column = NULL return?

Always zero rows Every row with a NULL value A syntax error Every row in the table

4. Which operator finds rows where a column DOES have a value?

IS NULL = NULL != NULL IS NOT NULL

5. Is NULL the same thing as an empty string ''?

Yes, always Only for text columns No, they are different Only in phpMyAdmin

6. What does COUNT(phone) count?

Every row in the table Only rows where phone is not NULL Only rows where phone is NULL The number of columns

7. Why does WHERE NOT phone = '011222333' exclude NULL phone rows?

Because NULL comparisons are unknown, not false, so NOT can't flip them Because NOT deletes NULL rows Because NULL is treated as true It doesn't — they're always included

8. Which query finds customers missing both an email and a phone?

WHERE email IS NULL OR phone IS NULL WHERE email = '' AND phone = '' WHERE NOT email IS NOT NULL WHERE email IS NULL AND phone IS NULL

9. Can IS NULL be combined with AND and OR from earlier modules?

No, it must be used alone Only with AND, never OR Yes, just like any other condition Only inside ORDER BY

10. Which clause would you add to sort IS NOT NULL results by signup_year, oldest first?

ORDER BY signup_year DESC ORDER BY signup_year ASC GROUP BY signup_year SORT signup_year

0 / 10

Try It Live

🧪 Live SQL Playground

A real SQLite database running entirely in your browser (via WebAssembly) — nothing is sent to a server. Practice using IS NULL and IS NOT NULL on the customers table below, or pick an example query to get started.

customers id INTEGER PK
name TEXT
email TEXT (nullable)
phone TEXT (nullable)
signup_year INTEGER

— Load an example query — List all customers Missing phone (IS NULL) Has an email (IS NOT NULL) The trap: email = NULL (always empty) 2023 signups missing a phone Missing email OR phone

SELECT * FROM customers;

Loading SQL engine…

Run a query to see results here.

Related Lessons

Continue the Series

Module 1: Mastering the SQL SELECT Statement (A Step-by-Step Guide)

How to Install XAMPP Step by Step for Beginners

How to Install MySQL Step by Step for Beginners

FAQ

Frequently Asked Questions

1. What is NULL, in plain terms?

It's SQL's way of saying "no value was recorded here" — not zero, not blank text, just genuinely unknown or missing.

2. Why doesn't WHERE column = NULL work?

Because = compares two known values, and NULL isn't a known value to compare against — the comparison itself produces "unknown," which SQL treats as not matching.

3. What should I use instead of = NULL?

Use IS NULL to find missing values, and IS NOT NULL to find rows that do have a value in that column.

4. Can I use IS NULL with AND and OR?

Yes — it behaves like any other condition and combines naturally with AND, OR, and NOT, following the same parentheses rules covered in earlier modules.

5. Is an empty string the same as NULL?

No. An empty string '' is a recorded value — it just happens to contain no characters — while NULL means no value was recorded at all. Depending on your data, you may need to check for both separately.

6. Why did COUNT(column) give me a smaller number than the total rows?

COUNT(column) only counts rows where that column is not NULL. If you want the total row count regardless of missing data, use COUNT(*) instead.

7. Do NULL values show up in NOT queries?

Not automatically. A NULL comparison evaluates to "unknown" rather than false, so NOT has nothing to reverse — if you want those rows included, add an explicit OR column IS NULL.

8. Can a primary key column contain NULL?

No — a PRIMARY KEY column requires a unique, non-NULL value for every row, so NULL only tends to appear in optional columns.

9. Does sorting with ORDER BY handle NULL values specially?

Yes, in most databases NULL values are grouped together during sorting, typically appearing first or last depending on the database system — it's worth checking your specific database's behavior if the order matters.

10. What's a good habit for working with nullable columns?

Whenever I'm filtering a column I know can be empty, I ask myself upfront whether I care about the NULL rows, and reach for IS NULL or IS NOT NULL immediately rather than defaulting to = or !=.

Part of the CodeMend SQL Module series. Finished Module 10? Move on to Module 11 for the IN operator.

Module 9 Set Matching with IN and NOT IN

Module 9

Set Matching with IN and NOT IN

A step-by-step, hands-on SQL practice lesson — with a live SQL playground

SELECT student_name, room FROM class_room WHERE room IN ('A101', 'B201');

After learning how to filter rows with WHERE, I can make my queries much cleaner when I need to match a column against several possible values. That is where IN becomes useful. Instead of writing many OR conditions, I can place the allowed values inside one list. In this lesson, I practice both IN and NOT IN with the class_room table, learn the important NULL behavior, and build queries step by step until the pattern feels natural.

Contents

1. Introduction
2. Requirements
3. Practice: Step by Step
4. Common Problems & Solutions
5. Conclusion
6. Homework & Quiz
7. Live SQL Playground
8. Related Lessons
9. FAQ

Introduction

What You'll Learn — and Why IN Matters

I use IN when a column can match one of several specific values. For example, if I want students in rooms A101, B201, or C301, I do not need to write three separate equality checks. I can use one readable condition:

WHERE room IN ('A101', 'B201', 'C301')

NOT IN reverses the membership test. It lets me find rows whose value is not in the listed set.

🎯 Objective

By the end of this lesson, you will be able to:

• Explain what a set of matching values means in SQL.
• Use IN to match a column against several exact values.
• Use NOT IN to exclude several exact values.
• Convert long OR conditions into cleaner IN conditions.
• Use IN with numbers, text values, and other SQL expressions.
• Combine IN or NOT IN with AND, OR, and other filters.
• Understand why NULL can make NOT IN behave unexpectedly.
• Choose between IN, NOT IN, BETWEEN, and comparison operators.

⚠️ What to Avoid

Important set-matching warnings

• Do not confuse IN with a range. IN (8, 9, 10) means those exact values, not every value between 8 and 10.
• Do not forget quotes around string values such as 'active' or 'A101'.
• Be careful with NOT IN when NULL values are possible. SQL's three-valued logic can produce no match for rows involving NULL.
• Do not put duplicate values in a list just because they are allowed. Duplicates do not add a new match.
• Do not build an enormous hard-coded list when a lookup table or subquery would be easier to maintain.

💡 Important to Know

The core idea

column IN (value1, value2, value3) is a compact way of asking whether the column equals any one of the listed values. Conceptually, room IN ('A101','B201') is similar to room = 'A101' OR room = 'B201'.

Requirements

What You'll Need Before Starting

Operating SystemWindows, macOS, Linux, or another operating system that can run MySQL. The SQL concepts in this lesson are portable across these systems.

Database ServerMySQL installed and running. If you need installation help, see How to Install MySQL Step by Step for Beginners.

Optional: XAMPPXAMPP plus phpMyAdmin provides a visual way to run the SQL. See How to Install XAMPP Step by Step for Beginners (Complete Guide 2026).

Prior knowledgeYou should know basic SELECT and WHERE. For a refresher, review Module 1: Mastering the SQL SELECT Statement (A Step-by-Step Guide).

No installation is required for the browser playground below. The real practice steps are written for MySQL or phpMyAdmin.

Practice

Step-by-Step Practice: IN and NOT IN

I recommend following these steps in order. I first prepare the database, then I test exact matching, replace repeated OR conditions with IN, and finally practice NOT IN, NULL handling, and subqueries.

1 Open MySQL or phpMyAdmin

On Windows, macOS, or Linux, open your MySQL client. If you use XAMPP, start MySQL and open phpMyAdmin. In the terminal, I can connect with:

mysql -u root -p

The basic IN syntax is:

SELECT column1, column2
FROM table_name
WHERE column_name IN (value1, value2, value3);

The opposite syntax is:

SELECT column1, column2
FROM table_name
WHERE column_name NOT IN (value1, value2, value3);

2 Create a Database and Verify It

I create a separate practice database so the examples are easy to repeat without changing another project.

CREATE DATABASE module9_in_practice;
SHOW DATABASES;
USE module9_in_practice;

After SHOW DATABASES;, confirm that module9_in_practice appears in the database list.

3 Create the class_room Table

Now I create a small table with repeated categories such as rooms and statuses. These repeated values are ideal for practicing set matching.

CREATE TABLE class_room (
  id INT PRIMARY KEY,
  student_name VARCHAR(80),
  grade_level INT,
  score DECIMAL(5,2),
  status VARCHAR(20),
  room VARCHAR(20)
);

Verify the database table and its columns:

SHOW TABLES;
DESCRIBE class_room;

4 Insert Sample Data

I insert eight students so the same room and status values appear more than once.

INSERT INTO class_room (id, student_name, grade_level, score, status, room) VALUES
(1, 'Dara', 7, 62.50, 'active', 'A101'),
(2, 'Sokha', 8, 74.00, 'inactive', 'A101'),
(3, 'Maly', 8, 81.50, 'active', 'B201'),
(4, 'Vannak', 9, 88.00, 'pending', 'B201'),
(5, 'Lina', 9, 93.50, 'active', 'C301'),
(6, 'Rith', 10, 69.00, 'inactive', 'C301'),
(7, 'Nita', 10, 97.00, 'active', 'D401'),
(8, 'Bora', 11, 55.00, 'pending', 'D401');

SELECT * FROM class_room ORDER BY id;

5 Match Several Text Values with IN

Suppose I want students in rooms A101, B201, and C301. I can write one clean condition:

SELECT student_name, room
FROM class_room
WHERE room IN ('A101', 'B201', 'C301')
ORDER BY room, student_name;

This is equivalent to writing:

SELECT student_name, room
FROM class_room
WHERE room = 'A101'
   OR room = 'B201'
   OR room = 'C301'
ORDER BY room, student_name;

My practice note

When I see several equality checks against the same column, I look for an opportunity to use IN. It usually makes the intention easier to read.

6 Match Several Numeric Values with IN

IN also works with numbers. If I want students in grades 7, 9, or 11, I can write:

SELECT student_name, grade_level
FROM class_room
WHERE grade_level IN (7, 9, 11)
ORDER BY grade_level, student_name;

Notice the difference between a set and a range:

-- Exact set: only 7, 9, or 11
WHERE grade_level IN (7, 9, 11)

-- Continuous range: every value from 7 through 11
WHERE grade_level BETWEEN 7 AND 11;

This distinction is important: IN is about membership in a specific list, while BETWEEN is about a continuous range.

7 Use NOT IN to Exclude a Set

Now I want every student except those in rooms A101 and D401.

SELECT student_name, room
FROM class_room
WHERE room NOT IN ('A101', 'D401')
ORDER BY room, student_name;

Conceptually, this is similar to:

WHERE room <> 'A101'
  AND room <> 'D401'

So NOT IN is especially useful when the list of excluded values grows.

8 Combine IN or NOT IN with Other Conditions

Real SQL queries often combine set matching with another filter. For example, I can find active students in selected rooms:

SELECT student_name, room, status
FROM class_room
WHERE status = 'active'
  AND room IN ('A101', 'B201', 'C301')
ORDER BY room, student_name;

I can also combine a numeric set with a score condition:

SELECT student_name, grade_level, score
FROM class_room
WHERE grade_level IN (8, 9, 10)
  AND score >= 75
ORDER BY grade_level, score DESC;

For more complex logic, parentheses make my intention clearer:

SELECT student_name, room, status
FROM class_room
WHERE (room IN ('A101', 'B201') OR status = 'pending')
  AND grade_level >= 8;

9 Understand NULL with NOT IN

This is one of the most important details I want to remember. SQL does not treat NULL as an ordinary value. A condition involving NULL can evaluate to UNKNOWN, not simply true or false.

For example, if a column can contain NULL, I should not assume that this always returns every row outside the listed values:

WHERE room NOT IN ('A101', 'B201')

If I specifically want rows whose room is either outside the list or NULL, I can say that explicitly:

WHERE room NOT IN ('A101', 'B201')
   OR room IS NULL

For larger projects, NOT EXISTS can also be a safer and clearer option when the exclusion list comes from another table or subquery, especially when NULLs may be present.

Troubleshooting

5 Common Problems and Solutions

Problem 1: I forgot quotes around text values

I wrote WHERE room IN (A101, B201) and MySQL reports an error or interprets the names incorrectly.

SolutionString values should normally be written with quotes: WHERE room IN ('A101', 'B201').

Problem 2: I expected IN to include every number in a range

I wrote grade_level IN (7, 11) and expected grades 7 through 11.

SolutionIN matches only the exact listed values. Use BETWEEN 7 AND 11 for a continuous inclusive range.

Problem 3: NOT IN behaves strangely when NULL exists

I expected NOT IN to return every row that is not one of the listed values, but rows involving NULL do not appear.

SolutionRemember SQL's NULL logic. If NULL should be included, add OR column_name IS NULL. When excluding values from another query, consider NOT EXISTS.

Problem 4: My IN list contains duplicate values

I have IN ('A101', 'A101', 'B201') and wonder whether the duplicate changes the result.

SolutionDuplicates do not create duplicate result rows. Keep the list unique because it is clearer and easier to maintain.

Problem 5: My IN condition is mixed with AND and OR incorrectly

The query returns more rows than I expected after I add several logical conditions.

SolutionUse parentheses to make the intended groups explicit. Test each condition separately, then combine them one at a time.

Conclusion

What I Want You to Remember

The main lesson I take from Module 9 is simple: IN is for matching a value against a specific set, while NOT IN is for excluding that set.

In my own SQL practice, I find IN especially helpful when I see repeated conditions such as room = 'A101' OR room = 'B201' OR room = 'C301'. Replacing them with room IN ('A101', 'B201', 'C301') makes the query easier to scan.

Expected result from this lesson

You should now be able to use IN and NOT IN for exact set matching, combine them with other filters, distinguish them from BETWEEN, and recognize the special care required when NULL values are involved.

Next step: keep practicing SQL filtering by combining the operators you have learned into complete real-world queries.

Homework

Homework & Quiz

📝 Homework Task

Using the class_room table, write these queries yourself before checking the live playground:

1. Find students whose room is one of A101, B201, or C301.
2. Find students whose room is not A101 or D401.
3. Find students whose grade level is exactly 7, 9, or 11.
4. Find active students whose room is in A101, B201, or C301 and whose score is at least 75.
5. Create a query that demonstrates how you would include NULL rooms while excluding A101 and B201.

🧪 Quiz: Test Your Understanding (10 Questions)

Pick one answer per question, then click Submit to see your score instantly.

1. What does room IN ('A101', 'B201') mean?

The room must be between A101 and B201 The room must equal A101 or B201 The room must contain both values The room is automatically sorted

2. Which syntax correctly matches several status values?

WHERE status MATCH ('active', 'pending') WHERE status = ('active', 'pending') WHERE status IN ('active', 'pending') WHERE status LIST 'active', 'pending'

3. What does NOT IN ('A101', 'D401') do?

Excludes rows whose value is A101 or D401 Returns only A101 and D401 Finds every value between A101 and D401 Sorts rooms alphabetically

4. What is the main difference between IN and BETWEEN?

They are always identical BETWEEN only works with text IN creates a table IN matches listed exact values, while BETWEEN describes a continuous range

5. Which condition is equivalent to grade_level IN (7, 9, 11)?

grade_level BETWEEN 7 AND 11 grade_level = 7 OR grade_level = 9 OR grade_level = 11 grade_level > 7 AND grade_level < 11 grade_level <> 7 AND grade_level <> 9

6. What should you remember about NULL and NOT IN?

NULL always matches NOT IN NULL is treated as zero NULL uses SQL's unknown logic, so it may need explicit IS NULL handling NULL is automatically converted to an empty string

7. Which query finds active students in rooms A101 or B201?

WHERE status = 'active' AND room IN ('A101', 'B201') WHERE status IN active AND room = A101, B201 WHERE active BETWEEN room A101 AND B201 WHERE status = active OR room IN A101, B201

8. Do duplicate values inside an IN list create duplicate result rows?

Yes, always Only on Windows Only with numbers No, but duplicates make the query less tidy

9. What is a good reason to use IN instead of many OR comparisons?

It changes the database structure It automatically creates an index It makes membership in a specific set concise and readable It removes all NULL values

10. What is the main idea of Module 9?

IN sorts rows IN matches exact values from a set, while NOT IN excludes the listed set IN only works with dates NOT IN creates a new database

0 / 10

Try It Live

🧪 Live SQL Playground

This browser playground uses SQLite through WebAssembly. It is designed to practice the set-matching ideas in this lesson; use your real MySQL server for MySQL-specific testing.

class_roomid INTEGER PK
student_name TEXT
grade_level INTEGER
score REAL
status TEXT
room TEXT

— Load an example query — List all students IN selected rooms NOT IN selected rooms IN exact grades 7, 9, 11 IN active or pending Active + IN rooms IN grades + score IN + OR + AND

SELECT * FROM class_room ORDER BY id;

Loading SQL engine…

Run a query to see results here.

Related Lessons

Continue Learning SQL — Modules 1 to 8

Module 1: Mastering the SQL SELECT Statement

Module 2: Selecting Specific Columns and Using Aliases

Module 3: DISTINCT — Deduplicating Results

Module 4: Filtering with the WHERE Clause

Module 5: Numeric Comparisons

Module 6: SQL Combining Conditions

Module 7: Logical Inversion with NOT

Module 8: Range Filtering with BETWEEN and NOT BETWEEN

FAQ

Frequently Asked Questions

1. What does IN do in SQL?

IN checks whether a column value matches one of the exact values in a specified list.

2. What does NOT IN do?

NOT IN checks for values that are not members of the listed set. NULL requires special consideration.

3. Is IN the same as BETWEEN?

No. IN matches a discrete list of exact values. BETWEEN describes a continuous inclusive range.

4. Can IN be used with numbers?

Yes. For example, grade_level IN (7, 9, 11) matches exactly 7, 9, or 11.

5. Can IN be used with text?

Yes. String values should normally be quoted, such as status IN ('active', 'pending').

6. Is IN better than many OR conditions?

When the same column is compared against several exact values, IN is usually more concise and easier to read. The optimizer can still choose an appropriate execution plan.

7. Why should I be careful with NOT IN and NULL?

SQL uses three-valued logic. Comparisons involving NULL can evaluate to UNKNOWN, so NULL rows may not be returned by NOT IN. Use explicit IS NULL handling when needed.

8. Can I combine IN with AND and OR?

Yes. You can combine set matching with other conditions. Use parentheses when several AND/OR conditions could be interpreted in different ways.

9. Can IN use a subquery?

Yes. A common pattern is WHERE department_id IN (SELECT id FROM departments ...). For exclusions with possible NULLs, NOT EXISTS may be a safer alternative.

10. Does IN remove duplicate rows?

No. IN only controls which rows qualify. If the underlying query produces duplicate rows, use an appropriate technique such as DISTINCT when deduplication is actually required.

Part of the CodeMend SQL Module series. Keep practicing exact set matching, exclusions, logical combinations, and NULL handling.