IN and NOT IN operators in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the IN operator do in SQL?

The IN operator checks if a value matches any value in a list or subquery. It helps filter rows where a column's value is in a specified set.

beginner

How does the NOT IN operator work?

The NOT IN operator filters rows where a column's value is NOT in the specified list or subquery. It excludes those values.

beginner

Write a simple SQL query using IN to find employees in departments 1, 2, or 3.

SELECT * FROM employees WHERE department_id IN (1, 2, 3);

intermediate

What happens if the list in NOT IN contains NULL?

If the list contains NULL, NOT IN may return no rows because comparisons with NULL are unknown. It's safer to avoid NULL in NOT IN lists.

intermediate

Can IN and NOT IN be used with subqueries? Give an example.

Yes. Example: SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments WHERE active = 1);

Which SQL operator checks if a value is inside a list of values?

A BETWEEN

B NOT IN

C IN

D LIKE

The IN operator checks if a value matches any value in a list.

What does NOT IN do in a SQL query?

A Includes rows with values in the list

B Excludes rows with values in the list

C Checks for NULL values only

D Sorts the results

NOT IN excludes rows where the column's value is in the specified list.

What is the result if the list in NOT IN contains NULL?

A All rows are returned

B Only rows with NULL values are returned

C It causes a syntax error

D No rows are returned

NOT IN with NULL in the list returns no rows because comparisons with NULL are unknown.

Which of these is a valid use of IN with a subquery?

A SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments WHERE active = 1);

B SELECT * FROM employees WHERE department_id NOT IN (SELECT id FROM departments WHERE active = 1);

C SELECT * FROM employees WHERE department_id = IN (1, 2, 3);

D SELECT * FROM employees WHERE department_id IN 1, 2, 3;

Option A correctly uses IN with a subquery to filter employees by active departments.

Which operator would you use to find rows where a column's value is NOT in a list?

A NOT IN

B IN

C BETWEEN

D LIKE

NOT IN filters out rows where the column's value is in the list.

Explain how the IN operator works and give a simple example.

Think about filtering rows by matching values.

Describe a potential issue when using NOT IN with NULL values in the list.

Consider how SQL treats NULL in comparisons.