Using the IN, BETWEEN, and LIKE Operators in SQL

Table of Contents

1. Introduction
2. Why Use Conditional Operators?
3. The Role of IN, BETWEEN, and LIKE
4. Basic Syntax Recap
5. The IN Operator
6. IN with Numbers
7. IN with Strings
8. IN with Subqueries
9. NOT IN Clause
10. Performance Tips for IN
11. The BETWEEN Operator
12. BETWEEN with Numbers
13. BETWEEN with Dates
14. NOT BETWEEN Clause
15. The LIKE Operator
16. Using % and _ in LIKE
17. Case Sensitivity in LIKE
18. Real-World Examples
19. Best Practices
20. Summary and What’s Next

1. Introduction

SQL offers several powerful comparison operators that go beyond =, >, or <. Among them, IN, BETWEEN, and LIKE are highly useful for writing more readable and flexible conditional logic.

2. Why Use Conditional Operators?

These operators allow you to:

• Match multiple values with ease
• Filter based on ranges
• Perform pattern-based text searches

All three are especially useful in filtering rows with specific rules in the WHERE clause.

3. The Role of IN, BETWEEN, and LIKE

4. Basic Syntax Recap

sqlCopyEditSELECT * FROM table_name
WHERE column IN (value1, value2, ...);

SELECT * FROM table_name
WHERE column BETWEEN value1 AND value2;

SELECT * FROM table_name
WHERE column LIKE 'pattern';

5. The IN Operator

The IN operator checks whether a value exists in a list or a subquery result.

sqlCopyEditSELECT * FROM employees
WHERE department IN ('HR', 'Sales', 'IT');

6. IN with Numbers

sqlCopyEditSELECT * FROM orders
WHERE status_id IN (1, 2, 5);

7. IN with Strings

sqlCopyEditSELECT * FROM products
WHERE category IN ('Books', 'Electronics', 'Toys');

8. IN with Subqueries

sqlCopyEditSELECT * FROM users
WHERE id IN (
  SELECT user_id FROM orders
  WHERE total > 1000
);

This checks if a user has made high-value purchases.

9. NOT IN Clause

Negates the IN condition:

sqlCopyEditSELECT * FROM customers
WHERE country NOT IN ('India', 'Nepal');

⚠️ Caution: If any value in the list is NULL, it may yield no results.

10. Performance Tips for IN

• Avoid large IN lists (consider a join instead)
• IN is faster and cleaner than multiple OR conditions
• Use indexed columns in the IN list for better performance

11. The BETWEEN Operator

The BETWEEN operator matches values inclusively within a given range.

sqlCopyEditSELECT * FROM products
WHERE price BETWEEN 100 AND 500;

This returns products priced from 100 to 500 inclusive.

12. BETWEEN with Numbers

sqlCopyEditSELECT * FROM students
WHERE marks BETWEEN 40 AND 75;

13. BETWEEN with Dates

sqlCopyEditSELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

Great for monthly reports or time-bound queries.

14. NOT BETWEEN Clause

sqlCopyEditSELECT * FROM inventory
WHERE quantity NOT BETWEEN 10 AND 50;

Excludes values within the specified range.

15. The LIKE Operator

LIKE is used for pattern matching in string fields. It uses:

• %: matches any number of characters
• _: matches a single character

sqlCopyEditSELECT * FROM customers
WHERE name LIKE 'A%';

Finds names starting with “A”.

16. Using % and _ in LIKE

Examples:

sqlCopyEditSELECT * FROM emails WHERE email LIKE '%@gmail.com';
SELECT * FROM cities WHERE name LIKE '_elhi'; -- matches 'Delhi'

17. Case Sensitivity in LIKE

• MySQL: case-insensitive by default (on most collations)
• PostgreSQL: case-sensitive
• Use ILIKE in PostgreSQL for case-insensitive match:

sqlCopyEditSELECT * FROM names WHERE name ILIKE 'a%';

18. Real-World Examples

1. Active Regions:

sqlCopyEditSELECT * FROM sales
WHERE region IN ('North', 'West');

2. High Scores:

sqlCopyEditSELECT name FROM students
WHERE marks BETWEEN 85 AND 100;

3. Gmail Users:

sqlCopyEditSELECT name, email FROM users
WHERE email LIKE '%@gmail.com';

19. Best Practices

• Use IN over multiple OR conditions for readability
• Use BETWEEN only for inclusive boundaries
• Use % carefully with LIKE; avoid starting with % if index performance matters
• Avoid LIKE on very large unindexed text fields
• Use aliases for cleaner queries when combining with SELECT, JOIN, or subqueries

20. Summary and What’s Next

The IN, BETWEEN, and LIKE operators enhance your ability to write clear and concise filtering conditions in SQL. They are powerful tools for conditional logic, especially in WHERE clauses, enabling more expressive queries.