SQL · Theory

IN

Theory

51/439

IN

IN is OR for a list. WHERE city IN ('Pune','Goa','Delhi'); NOT IN is the opposite — beware NULL inside NOT IN (the whole test can become unknown).

IN (SELECT city FROM allowed) is a subquery form. For a joinable list, a table + JOIN is often clearer.

Trap — IN vs =. = is one value. Trap: NOT IN (1, NULL) surprises.

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

Exam tip

WHERE city IN ('Pune','Goa');

IN — sample query

-- IN
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;

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

Short notes

DefIN (a,b,c) = equal to any listed value.
RulePrefer IN over a long OR chain.
TrapNOT IN with NULL in the list.

-- IN 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;

IN

IN — sample query

Short notes

Questions

IN

IN — sample query

Short notes

Questions