The IN queries a list, the BETWEEN queries a range, and the LIKE queries a pattern. These three filters read almost like the English, because they answer the three shapes that most of the real-world questions take. Which group, what band, and what the shape of the text is. All three of them have an edge case that is worth rehearsing before the production meets it.
The previous lesson in this module covered "How Do AND, OR and NOT Combine Conditions?", and the next lesson in this module is about "How Do NULLs Behave in Comparisons and Filters?".
What does the IN match, and why do you reach for it over a chain of ORs?
The IN tests the membership in an explicitly written list. The two departments that were invited in the last lesson collapse from a two-branched OR into one readable clause. You get the same four people, with half the text.
SELECT name, department FROM staff WHERE department IN ('Buying', 'Cafe');name department
------------- ----------
Priya Raman Buying
Dana Kowalski Buying
Aisha Zahra Cafe
Joe Naylor CafeThe readability is the first win. The second one is that the list can arrive later from somewhere else, such as a subquery, which lesson 27 builds exactly, or a code-driven parameter list. The filters in the application code very often take the shape WHERE id IN (...) because the user picked three rows from a screen.
The IN also works on the numbers anywhere. The query WHERE id IN (1, 5, 8) is the classic quick way to do a lookup.
How does the BETWEEN set a range, and are the endpoints inside or out?
The BETWEEN takes a low value and a high value and it includes both of them. The salary band of 25000 to 32000 keeps the barista on 25500 and the café lead on the very edge value of 32000.
SELECT name, salary FROM staff WHERE salary BETWEEN 25000 AND 32000;name salary
------------- ------
Marcus Bell 28000
Aisha Zahra 32000
Tom Whitfield 27500
Joe Naylor 25500The inclusive edges are the part that the reviewers debate the most, so you either keep the BETWEEN for the self-explanatory ranges, such as the whole bands and the whole dates, or you write the explicit form when a boundary means just before it. The dates reward the extra care. A deadline at the end of the day is a value such as BETWEEN '2025-01-01' AND '2025-01-31 23:59:59' in the engines that timestamp, and the off-by-one seconds at the midnight is a quietly classic report bug.
What do the LIKE wildcards match, character by character?
The LIKE is a pattern language with exactly two wildcards. The percent matches any run of the characters, including none at all, and the underscore matches exactly one. You pin down an email that starts with tom., and then you isolate one character in the middle of the pattern. Both of the filters find their intended person.
SELECT name FROM staff WHERE email LIKE 'tom.%';name
-------------
Tom Whitfield-- the underscore pins position 7: priya._aman@ versus priya.raman@
SELECT name FROM staff WHERE email LIKE 'priya._aman@%';name
------------
Priya RamanThe patterns that start with the percent, such as %@example.org, filter by the domain. The patterns that have the percent on both sides scan every row, and they cannot lean on an index. That is a cost fact that returns in the performance module.
When a value genuinely contains a percent or an underscore, such as the product codes SALE_2025, you write an ESCAPE clause so that the wildcard is treated as the literal text. Forgetting it is the reason why the pattern filters over the stock codes occasionally match a thousand harmless rows.
The placement of the anchor decides how much of the table it must scan. A prefix pattern such as tom.% can use any index on the column and it exits early, while %ton% forces a full sweep. That single line of the theory maps directly onto the dashboards that hammer your indices at the month end. The leading wildcards are the single most common accidental cost in the reports that grew one carelessly-typed condition at a time.
Is the LIKE case-sensitive?
Not here, and the difference from the equality is worth asserting. The equality sign from lesson 5 on this database refused the lower-case london, yet the LIKE folds the ASCII case for every stored value.
SELECT name FROM staff WHERE email LIKE 'PRIYA%';name
------------
Priya RamanThe answer in the other engines differs, and those are the dialects rather than the syntax. SQLite ignores the case for the ASCII letters. PostgreSQL keeps the LIKE strict and it offers the ILIKE for the folding behaviour. MySQL folds the case when the collation of the column spells it that way, which is typically the case.
You never publish a filter whose brittleness you have not tested on the engine that you will actually schedule it against.
What happens when the NOT IN meets a NULL?
The NOT IN already quietly drops the unknown districts from this roster. Asking for the people outside the two cities yields three, because the NULL city of Ruth fails the membership in the usual way.
SELECT name, city FROM staff WHERE city NOT IN ('London', 'Leeds');name city
------------- ----------
Dana Kowalski Manchester
Aisha Zahra Leicester
Joe Naylor ManchesterNow the trap. You put a NULL inside the list that you compare against. A row survives only if its value differs from every one of the list members, and nothing differs from an unknowable value.
SELECT name, city FROM staff WHERE city NOT IN ('London', NULL);(no rows)Eight rows go in and zero come out, with no error raised at all. Every NOT IN should be trained never to accept a list that might contain a NULL. You filter the list, or you switch the shape to the NOT EXISTS once the tools of lesson 28 arrive.
Quick reference
- The IN is the membership in a written list, and it beats the OR ladders for three or more alternatives.
- The BETWEEN is inclusive at both of the endpoints, so you say so in the code comments when the edges are delicate.
- The LIKE has the percent for any run and the underscore for one character, and the ESCAPE protects the literal wildcards.
- A NULL hiding inside a NOT IN silently starves the result to zero rows.
Set work: count the staff whose role contains the word Bookseller with one LIKE query, name everyone in the salary band of 27000 to 30000 inclusive with the BETWEEN, and list everyone whose department is not the Cafe, the Sales or the Buying. Then you explain in one line what happened to the row with no city, and you patch that one-line gap with a NULL-aware rewrite.