What is the difference between IN, BETWEEN, and LIKE in WHERE clauses?

Q: What is the difference between IN, BETWEEN, and LIKE in WHERE clauses?

In MySQL, the **WHERE clause** can use various operators to filter rows. Among them, `IN`, `BETWEEN`, and `LIKE` are commonly used for different types of conditions. **IN** – Checks if a value matches any value in a list. Example: `WHERE department_id IN (1, 2, 3)` returns rows where department_id is 1, 2, or 3. **BETWEEN** – Filters values within a range, inclusive of the boundary values. Example: `WHERE salary BETWEEN 50000 AND 100000` returns rows with salary from 50000 to 100000. **LIKE** – Performs pattern matching on strings using `%` (any sequence of characters) or `_` (single character). Example: `WHERE name LIKE 'A%'` returns rows where the name starts with 'A'. These operators help simplify queries and make filtering conditions more readable and efficient. `IN` is ideal for discrete sets, `BETWEEN` for ranges, and `LIKE` for flexible string patterns. SELECT name, department_id, salary FROM employees WHERE department_id IN (1, 2, 3) AND salary BETWEEN 50000 AND 100000 AND name LIKE 'A%';

Difficulty: 3/10

set membership, range filtering, pattern matching

Understanding IN, BETWEEN, and LIKE in MySQL WHERE Clauses

In MySQL, the WHERE clause can use various operators to filter rows. Among them, IN, BETWEEN, and LIKE are commonly used for different types of conditions.

Key Differences and Usage

1
IN – Checks if a value matches any value in a list. Example: WHERE department_id IN (1, 2, 3) returns rows where department_id is 1, 2, or 3.
2
BETWEEN – Filters values within a range, inclusive of the boundary values. Example: WHERE salary BETWEEN 50000 AND 100000 returns rows with salary from 50000 to 100000.
3
LIKE – Performs pattern matching on strings using (any sequence of characters) or (single character). Example: returns rows where the name starts with 'A'.

These operators help simplify queries and make filtering conditions more readable and efficient. IN is ideal for discrete sets, BETWEEN for ranges, and LIKE for flexible string patterns.

Example Using IN, BETWEEN, and LIKE

Scenario Questions

0-2 years experience

Follow-up Questions