SQL LIKE,IN,BETWEEN

In SQL, filtering data is a very common task. While the WHERE clause allows us to filter rows based on certain conditions, there are special operators that make these conditions more flexible and powerful. Among them, LIKE, IN, and BETWEEN are widely used to perform specific types of searches in databases.

In this article, we will understand each of these operators in detail with syntax and examples.

---

1. SQL LIKE Operator

The LIKE operator is used when you want to search for a specified pattern in a column. Instead of checking for exact matches, it allows partial matches using wildcard characters.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;

Wildcards Used in LIKE

• % – Represents zero, one, or multiple characters.
• _ – Represents a single character.

Examples

1. Find names starting with ‘A’

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

This returns all records where the name begins with the letter A.

2. Find names ending with ‘n’

SELECT * FROM customers
WHERE name LIKE '%n';

3. Find names containing ‘an’

SELECT * FROM customers
WHERE name LIKE '%an%';

4. Find names with exactly 4 letters

SELECT * FROM customers
WHERE name LIKE '____';

When to Use LIKE

• Searching for partial matches.
• When the exact spelling is not known.
• In situations involving text pattern searches.

---

2. SQL IN Operator

The IN operator is used to check whether a value matches any value in a list of specified values. It works like multiple OR conditions but is easier and cleaner to write.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column_name IN (value1, value2, ...);

Examples

1. Find customers from certain countries

SELECT * FROM customers
WHERE country IN ('India', 'USA', 'Canada');

This returns customers whose country is either India, USA, or Canada.

2. Find orders with specific IDs

SELECT * FROM orders
WHERE order_id IN (101, 103, 107);

NOT IN

You can also use NOT IN to exclude certain values.

SELECT * FROM customers
WHERE country NOT IN ('India', 'USA');

When to Use IN

• When you need to match against multiple fixed values.
• To replace long OR statements.
• To make queries shorter and more readable.

---

3. SQL BETWEEN Operator

The BETWEEN operator is used to select values within a given range. It works for numbers, text, and dates.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Important Notes:

• BETWEEN is inclusive — it includes both the starting and ending values in the result.
• Works for numerical, date, and text ranges.

Examples

1. Find orders between two dates

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

2. Find products within a price range

SELECT * FROM products
WHERE price BETWEEN 100 AND 500;

3. Find names alphabetically between ‘A’ and ‘C’

SELECT * FROM customers
WHERE name BETWEEN 'A' AND 'C';

NOT BETWEEN

You can use NOT BETWEEN to exclude a range.

SELECT * FROM products
WHERE price NOT BETWEEN 100 AND 500;

When to Use BETWEEN

• When you want to find values in a range.
• For filtering by date intervals.
• For numeric filtering without using multiple AND conditions.

---

Comparison Table: LIKE vs IN vs BETWEEN

Feature	LIKE	IN	BETWEEN
Purpose	Pattern matching	Match multiple values	Match a range of values
Data Types	Mostly text	All data types	Numbers, dates, text
Syntax Simplicity	Moderate	Simple	Simple
Wildcards Support	Yes	No	No
Inclusive Search	Depends on pattern	Exact matches only	Always inclusive

---

Conclusion

The SQL operators LIKE, IN, and BETWEEN are essential tools for data filtering:

• LIKE is perfect for pattern-based searches in text.
• IN simplifies matching against multiple fixed values.
• BETWEEN makes it easy to filter data within a range.

By using these operators effectively, you can write cleaner and more efficient SQL queries, making data retrieval faster and more accurate.