MySql - BETWEEN, IN, LIKE, IS NULL

BETWEEN, IN, LIKE, IS NULL IN MySQL

MySQL provides several powerful conditional operators that are commonly used to filter and manipulate data during retrieval. Among these, BETWEEN, IN, LIKE, and IS NULL are frequently used in WHERE clauses to control query results. This document provides a detailed explanation of each, with syntax, examples, and best practices.

1. BETWEEN Operator

1.1 Overview

The BETWEEN operator is used to filter the result set within a certain range. It is inclusive, meaning it includes the boundary values specified in the query.

1.2 Syntax

SELECT column_name
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

1.3 Example with Numeric Values

SELECT * FROM employees
WHERE salary BETWEEN 30000 AND 50000;

1.4 Example with Dates

SELECT * FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-06-30';

1.5 Important Notes

• BETWEEN includes both start and end values.
• You can use it with numbers, dates, or even text.

1.6 Example with Strings

SELECT * FROM products
WHERE product_name BETWEEN 'Laptop' AND 'Smartphone';

2. IN Operator

2.1 Overview

The IN operator allows you to specify multiple values in a WHERE clause. It’s a concise alternative to using multiple OR conditions.

2.2 Syntax

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

2.3 Example

SELECT * FROM students
WHERE grade IN ('A', 'B', 'C');

2.4 Alternative Using OR

SELECT * FROM students
WHERE grade = 'A' OR grade = 'B' OR grade = 'C';

2.5 Subquery in IN Clause

SELECT * FROM employees
WHERE department_id IN (
  SELECT id FROM departments WHERE location = 'New York'
);

2.6 NOT IN Clause

Use NOT IN to exclude values:

SELECT * FROM students
WHERE grade NOT IN ('F', 'D');

2.7 NULL Caution with IN

If any value in the list is NULL, the IN condition may return unexpected results if NULL comparisons are not handled.

3. LIKE Operator

3.1 Overview

The LIKE operator is used to search for a specified pattern in a column, particularly useful with text data. It supports wildcard characters:

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

3.2 Syntax

SELECT column_name
FROM table_name
WHERE column_name LIKE pattern;

3.3 Example - Starts With

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

This returns all customers whose names start with 'A'.

3.4 Example - Ends With

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

3.5 Example - Contains

SELECT * FROM products
WHERE product_name LIKE '%phone%';

3.6 Example - Single Character Wildcard

SELECT * FROM users
WHERE username LIKE 'j_hn';

3.7 Case Sensitivity

LIKE is case-insensitive for non-binary strings unless configured otherwise.

3.8 NOT LIKE

SELECT * FROM products
WHERE product_name NOT LIKE '%laptop%';

4. IS NULL Operator

4.1 Overview

NULL represents missing or unknown values. To test for NULL values, use the IS NULL or IS NOT NULL operator.

4.2 Syntax

SELECT column_name
FROM table_name
WHERE column_name IS NULL;

4.3 Example - IS NULL

SELECT * FROM employees
WHERE manager_id IS NULL;

4.4 Example - IS NOT NULL

SELECT * FROM employees
WHERE manager_id IS NOT NULL;

4.5 Common Mistake

Never use = NULL or <> NULL; always use IS NULL or IS NOT NULL.

5. Combining Operators

5.1 BETWEEN with IN

SELECT * FROM orders
WHERE status IN ('shipped', 'delivered')
AND order_date BETWEEN '2025-01-01' AND '2025-03-31';

5.2 LIKE with IS NOT NULL

SELECT * FROM customers
WHERE email LIKE '%@gmail.com'
AND phone IS NOT NULL;

5.3 Complex Combination

SELECT * FROM employees
WHERE department_id IN (1, 2, 3)
AND hire_date BETWEEN '2020-01-01' AND '2025-01-01'
AND status IS NOT NULL
AND name LIKE 'A%';

6. Performance Tips

• Use indexes on columns involved in BETWEEN and IN for faster lookups.
• LIKE with wildcards at the beginning (e.g., '%term') cannot use indexes efficiently.
• Consider using full-text search for complex LIKE patterns.
• Use NOT IN cautiously with subqueries; if NULLs are involved, results can be misleading.

7. Real-World Use Cases

7.1 Retrieve Active Subscriptions Expiring Soon

SELECT * FROM subscriptions
WHERE status = 'active'
AND expiry_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY);

7.2 Users With Gmail or Yahoo Addresses

SELECT * FROM users
WHERE email LIKE '%@gmail.com'
OR email LIKE '%@yahoo.com';

7.3 Orders Without Delivery Date

SELECT * FROM orders
WHERE delivery_date IS NULL;

7.4 Employees in Specific Departments

SELECT * FROM employees
WHERE department_id IN (101, 102, 103);

The BETWEEN, IN, LIKE, and IS NULL operators are essential tools for querying data in MySQL effectively. Each operator serves a specific purpose:

• BETWEEN: Filters values within a range.
• IN: Filters values that match one or more options.
• LIKE: Searches patterns in string fields using wildcards.
• IS NULL: Tests whether a column value is NULL.

Using these operators wisely can lead to concise, efficient, and expressive SQL queries. Whether you're filtering records by date ranges, pattern-matching text, checking for missing data, or working with a list of values, these operators are foundational to writing effective SQL in MySQL.