Basic NOT Operator

basic

Find non-cancelled orders

SELECT order_id, customer_id, status, total

FROM orders

WHERE NOT status = 'cancelled'

ORDER BY order_id;

order_id | customer_id | status | total

101 | 1 | completed | 150.00

102 | 2 | pending | 200.00

103 | 1 | shipped | 75.00

NOT negates the condition. Equivalent to status != 'cancelled'. Returns all non-cancelled orders.

All

NOT EXISTS for Missing Relationships

intermediate

Find customers without orders

SELECT c.customer_id, c.name, c.email

FROM customers c

WHERE NOT EXISTS (

SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id

)

ORDER BY c.name;

customer_id | name | email

3 | Charlie | charlie@email.com

4 | Dave | dave@email.com

NOT EXISTS finds customers with no matching orders. NULL-safe and efficient for finding missing relationships.

All

NOT IN Pitfall with NULL

advanced

Demonstrate NOT IN danger with NULL values

-- WRONG: Returns no rows if list contains NULL

SELECT * FROM products

WHERE category_id NOT IN (SELECT category_id FROM archived_categories);

-- CORRECT: Filter out NULL

SELECT * FROM products

WHERE category_id NOT IN (

SELECT category_id FROM archived_categories WHERE category_id IS NOT NULL

);

-- BETTER: Use NOT EXISTS

SELECT * FROM products p