IN, BETWEEN and LIKE: which filter to choose?

List, interval or pattern?

IN checks for membership in a closed list, BETWEEN tests for an inclusive interval and LIKE searches for a shape in a text.

Translating the request into a sentence first avoids stacking ORs or using a pattern that is too broad and picks up unexpected lines.

The query explained

Start by defining what a row of the result should represent. This decision determines the necessary columns, groupings, and controls.

The following example isolates the main mechanism. Run it, observe the result, then change one clause at a time to understand its effect.

SELECT produit, categorie, prix
FROM produits
WHERE categorie IN ('Office', 'IT')
  AND prix BETWEEN 20 AND 100
  AND produit LIKE '%Pro%';

Read the result like an analyst

The range includes 20 and 100. The pattern %Pro% accepts Pro anywhere in the label; Pro% would impose it at the beginning.

With AND, a line must meet all three conditions. Add parentheses whenever OR enters the query.

• Test both terminals of BETWEEN.
• Check which text should match and which should be excluded.
• Count the lines after each filter.
• Document the expected breakage depending on the engine.

Errors that distort the analysis

A query can be valid without correctly answering the question. The most costly errors are often silent: wrong perimeter, multiplied line or missing value interpreted as zero.

Before sharing the result, compare it to a small hand-calculated sample and keep the scope definition with the query.

• Believing BETWEEN excludes terminals.
• Use % on both sides and overly broaden the search.
• Mix AND and OR without parentheses.
• Using IN with a value NULL without understanding the three-value logic.

Get into practice

Complete the price range exercise, then replace BETWEEN with two comparisons to confirm equivalence.

The SQL simulator of SQL.tn only runs read queries on dummy data in your browser. You can try multiple writes and compare their results without installing any software.