SQL NOT

-- NOT with equality
SELECT id FROM users WHERE NOT country = 'AU';
-- Equivalent and more readable:
SELECT id FROM users WHERE country <> 'AU';

-- NOT IN
SELECT id FROM users
WHERE country NOT IN ('AU', 'NZ', 'US');

-- NOT IN gotcha: a NULL inside the list makes EVERY row not match
SELECT id FROM users WHERE country NOT IN ('AU', NULL);
-- Returns ZERO rows (because anything compared to NULL is unknown)

-- Safer: NOT EXISTS
SELECT u.id
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM bans b WHERE b.user_id = u.id
);

-- NOT LIKE
SELECT email FROM users WHERE email NOT LIKE '%@spammy.com';

-- NOT BETWEEN
SELECT id FROM orders WHERE total NOT BETWEEN 100 AND 500;

-- IS NOT NULL - the only way to test for non-null
SELECT id FROM users WHERE deleted_at IS NOT NULL;

-- Combining with AND/OR
SELECT id, email
FROM users
WHERE active = TRUE
  AND NOT (country = 'AU' AND tier = 'free');

-- Equivalent via De Morgan's law
SELECT id, email
FROM users
WHERE active = TRUE
  AND (country <> 'AU' OR tier <> 'free');

-- NOT in CHECK constraints
CREATE TABLE orders (
  id    SERIAL PRIMARY KEY,
  total INT NOT NULL CHECK (total >= 0),
  status TEXT NOT NULL CHECK (status IN ('open','paid','shipped','cancelled'))
);

-- NOT EXISTS for anti-joins (users with no orders)
SELECT u.id
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

SELECT * FROM customers
WHERE NOT country = 'US';