IN Operator - LabsICT

If you ever find yourself writing city = 'X' OR city = 'Y' OR city = 'Z', stop right there. There is a better way — the IN operator.

Using IN

IN lets you check if a value matches any in a list:

-- Instead of this:
SELECT * FROM students WHERE city = 'Kano' OR city = 'Abuja' OR city = 'Lagos';

-- Do this:
SELECT * FROM students WHERE city IN ('Kano', 'Abuja', 'Lagos');

Try it Yourself →

Cleaner, shorter, and easier to read. You can have as many values as you want in that list.

NOT IN

Want everything except certain values? Use NOT IN:

-- Everyone except students from Kano
SELECT * FROM students WHERE city NOT IN ('Kano');

Try it Yourself →

Be careful with NOT IN though. If your list contains a NULL value, NOT IN will return zero rows. That is one of those SQL quirks that has confused everyone at least once.

IN with Numbers

Works with numbers too:

SELECT * FROM students WHERE age IN (21, 22, 23);

Try it Yourself →