WHERE with NOT operator in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the NOT operator do in a SQL WHERE clause?

The NOT operator reverses the condition in the WHERE clause. It selects rows where the condition is false.

beginner

Write a SQL query to select all employees who are NOT in the 'Sales' department.

SELECT * FROM employees WHERE NOT department = 'Sales';

intermediate

Can the NOT operator be combined with other conditions in the WHERE clause?

Yes, NOT can be combined with AND, OR, and parentheses to create complex conditions.

advanced

How does NOT work with NULL values in SQL WHERE clauses?

NOT with NULL returns UNKNOWN, so rows with NULL in the condition are not selected unless IS NULL or IS NOT NULL is used.

intermediate

Rewrite this query using NOT: SELECT * FROM products WHERE category <> 'Books';

SELECT * FROM products WHERE NOT category = 'Books';

What does this query return? SELECT * FROM users WHERE NOT active = 1;

A Users who are not active

B Users who are active

C All users

D No users

NOT active = 1 means selecting users where active is not 1, so users who are not active.

Which is equivalent to: WHERE NOT (age > 30)?

A WHERE age <= 30

B WHERE age = 30

C WHERE age < 30

D WHERE age > 30

NOT (age > 30) means age is less than or equal to 30.

What happens if you use NOT with a NULL value in a condition?

A Returns TRUE

B Returns UNKNOWN (no rows selected)

C Returns FALSE

D Returns an error

NOT with NULL results in UNKNOWN, so the row is not selected.

How to select rows where status is NOT 'completed' or 'pending'?

A WHERE NOT status = 'completed' OR status = 'pending'

B WHERE status != 'completed' AND status != 'pending'

C WHERE NOT (status = 'completed' OR status = 'pending')

D WHERE status = 'completed' AND status = 'pending'

NOT applies to the whole condition inside parentheses to exclude both 'completed' and 'pending'.

Which query uses NOT correctly to exclude 'admin' users?

A SELECT * FROM users WHERE NOT role = 'admin'

B SELECT * FROM users WHERE role != 'admin'

C SELECT * FROM users WHERE NOT role != 'admin'

D SELECT * FROM users WHERE role = 'admin'

NOT role = 'admin' and role != 'admin' both select users whose role is not 'admin'.

Explain how the NOT operator changes the meaning of a condition in a SQL WHERE clause.

Think about what happens when you say NOT true or NOT false.

Describe how to combine NOT with other conditions to filter data in SQL.

Consider how NOT affects multiple conditions together.