SQL · Theory

NOT

Theory

54/439

NOT

NOT flips a test. WHERE NOT (marks < 40) is marks >= 40, but NULL still fails the test (unknown). WHERE city NOT IN ('Pune') vs WHERE NOT city = 'Pune' — NULL city is not ‘Pune’ and not ‘not Pune’ in three-valued logic.

NOT LIKE, NOT EXISTS, != / <> are cousins. Prefer readable: WHERE marks >= 40 instead of NOT (marks < 40) when you can.

Trap — NOT IN with NULL. Trap: assuming NOT makes NULL into true.

On the example next to this theory: NOT: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Exam tip

WHERE marks >= 40 rather than NOT (marks < 40).

NOT — sample query

-- NOT
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

NOT: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Short notes

DefNOT inverts a boolean test.
RulePrefer positive conditions when they read better.
TrapNOT IN + NULL. NOT does not turn NULL into true.

SQL · Theory

NOT

← All stacks

Theory

54/439

NOT

NOT LIKE, NOT EXISTS, != / <> are cousins. Prefer readable: WHERE marks >= 40 instead of NOT (marks < 40) when you can.

Trap — NOT IN with NULL. Trap: assuming NOT makes NULL into true.

On the example next to this theory: NOT: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Exam tip

WHERE marks >= 40 rather than NOT (marks < 40).

NOT — sample query

-- NOT
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

NOT: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Short notes

DefNOT inverts a boolean test.
RulePrefer positive conditions when they read better.
TrapNOT IN + NULL. NOT does not turn NULL into true.

NOT

NOT — sample query

Short notes

Questions

NOT

NOT — sample query

Short notes

Questions