MySQL: Advanced Filtering and Regular Expression Queries in…

Q: Can a LIKE '%keyword%' query use an index?

If the prefix contains % , it cannot use an index and will perform a full-table scan. For large datasets, consider using a full-text index (FULLTEXT).

Q: Is there a NULL trap with NOT IN?

Yes. NOT IN (1, 2, NULL) always returns NULL because a comparison involving NULL results in NULL. Use NOT EXISTS instead.

Last updated: 2026-08-26

When basic filtering isn't enough, advanced filtering lets you pinpoint specific data.

This lesson provides an in-depth explanation of complex conditional combinations and regular expressions.

graph TB
    A[WHERE Combinations of Conditions] --> B[AND Logic and]
    A --> C[OR Logical OR]
    A --> D[NOT Negate]
    B --> E[All conditions are met simultaneously]
    C --> F[Meets any one of the conditions]
    A --> G[IN List Matching]
    A --> H[BETWEEN Scope]
    A --> I[LIKE Wildcard]
    A --> J[REGEXP Regular]
    A --> K[EXISTS Subquery]

1. What You'll Learn

AND/OR condition combinations
IN/NOT IN List Query
BETWEEN Range Query
LIKE wildcard matching
REGEXP Regular Expressions

2. A True Story About a Search Feature

(1) Pain Point: Inaccurate Search Results

A user searches for "john" and requests a match for:

John Smith
JOHNSON
john@example.com Oh, John

The simple LIKE '%john%' can only match one scenario.

(2) The REGEXP Solution

SQL

SELECT * FROM users 
WHERE first_name REGEXP '^john|john$|john' 
   OR email REGEXP 'john';

3. AND/OR In-Depth

▶ Example: Complex Combinations of Conditions

TEXT 📖 Display only

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

SQL

-- Search: Engineering or Sales department, and salary > 5000
SELECT * FROM employees 
WHERE (department = 'Engineering' OR department = 'Sales')
  AND salary > 5000;

-- Search: Salary 5000-8000, not HR department
SELECT * FROM employees 
WHERE salary BETWEEN 5000 AND 8000
  AND department != 'HR';

-- Search: Hired after 2024, or salary > 10000
SELECT * FROM employees 
WHERE hire_date >= '2024-01-01' OR salary > 10000;

TEXT 📖 Display only

Output displayed

4. IN/NOT IN

▶ Example: List Query

TEXT 📖 Display only

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

SQL

-- IN Search
SELECT * FROM products WHERE category_id IN (1, 3, 5, 7);

-- NOT IN Search
SELECT * FROM products WHERE category_id NOT IN (2, 4, 6);

-- Subquery IN
SELECT * FROM employees 
WHERE department_id IN (SELECT id FROM departments WHERE location = 'Beijing');

-- NOT EXISTS replacement for NOT IN (Better performance)
SELECT * FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

TEXT 📖 Display only

Output displayed

5. Advanced BETWEEN

▶ Example: Range Query

SQL

-- Value Range
SELECT * FROM products WHERE price BETWEEN 100 AND 500;

-- Date Range
SELECT * FROM orders WHERE order_date BETWEEN '2026-01-01' AND '2026-06-30';

-- String Range
SELECT * FROM employees WHERE last_name BETWEEN 'A' AND 'M';

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

▶ Try it Yourself

TEXT 📖 Display only

Output displayed

6. The LIKE Wildcard

▶ Example: Fuzzy Matching

TEXT 📖 Display only

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

SQL

-- % Match any number of characters (including 0)
SELECT * FROM users WHERE name LIKE 'J%';        -- Starts with J
SELECT * FROM users WHERE name LIKE '%son';      -- Ends with son
SELECT * FROM users WHERE name LIKE '%john%';    -- Contains john

-- _ Matches exactly one character
SELECT * FROM users WHERE name LIKE 'J_hn';      -- J_hn (John, Johan)

-- Used in combination
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- ESCAPE escaping special characters
SELECT * FROM files WHERE name LIKE '%\_%' ESCAPE '\\';  -- Contains _
SELECT * FROM files WHERE name LIKE '%%' ESCAPE '\\';    -- Contains %

TEXT 📖 Display only

Output displayed

7. REGEXP Regular Expressions

(1) Common Regular Expression Metacharacters

Metacharacter	Description	Example
`^`	Beginning	`'^John'` — Starts with John
`$`	End	`'son$'` — Ends with son
`.`	Any single character	`'J.hn'` — John, Johan
`[...]`	Character set	`'[abc]'` — one of a/b/c
`[^...]`	Exclude character set	`[^abc]` — Not a/b/c
`*`	0 times or more	`'ab*'` — a, ab, abb
`+`	one time or more	`'ab+'` — a, ab
`?`	0 times or 1 time	`'ab?'` — a, ab
`{n}`	exactly n times	`'a{3}'` — aaa
`{n,m}`	n to m times	`'a{2,4}'` — aa~aaaa
`\|`	OR	`'cat\|dog'` — cat or dog

▶ Example: REGEXP Query

TEXT 📖 Display only

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

SQL

-- Starts with J or j
SELECT * FROM users WHERE first_name REGEXP '^[Jj]';

-- Contains numbers
SELECT * FROM users WHERE username REGEXP '[0-9]';

-- Email format validation (Simple)
SELECT * FROM users WHERE email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';

-- Mobile phone number format (Mainland China)
SELECT * FROM users WHERE phone REGEXP '^1[3-9][0-9]{9}$';

-- Contains CJK characters
SELECT * FROM users WHERE name REGEXP '[\\u4e00-\\u9fff]';

TEXT 📖 Display only

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

▶ Example: REGEXP vs LIKE

SQL

-- LIKE matches must be at the beginning and end only
SELECT * FROM users WHERE name LIKE '%john%';  -- Contains john

-- REGEXP allows for precise control
SELECT * FROM users WHERE name REGEXP '^john$'; -- Exactly equal to john (Case-insensitive)
SELECT * FROM users WHERE name REGEXP 'john|jane'; -- john or jane
SELECT * FROM users WHERE name REGEXP '^[A-Z]'; -- Starts with a capital letter

▶ Try it Yourself

TEXT 📖 Display only

Output displayed

8. EXISTS Subqueries

❓ FAQ

Q Can a LIKE '%keyword%' query use an index?

A If the prefix contains %, it cannot use an index and will perform a full-table scan. For large datasets, consider using a full-text index (FULLTEXT).

Q Which is faster, REGEXP or LIKE?

A LIKE is usually faster (it can use indexes). REGEXP is more flexible but cannot use indexes (partially supported in MySQL 8.0).

Q Which performs better, IN or OR?

A They perform similarly when there is little data. When the IN list is long, MySQL optimizes it to a sorted binary search, which is faster than OR.

Q Is there a NULL trap with NOT IN?

A Yes. NOT IN (1, 2, NULL) always returns NULL because a comparison involving NULL results in NULL. Use NOT EXISTS instead.

📖 Summary

AND/OR combined conditions; use parentheses to specify the order of operations
IN/NOT IN list queries: Watch out for NULL traps
BETWEEN range query, including both endpoints
LIKE wildcard: % any number of characters, _ a single character
REGEXP Regular expressions; supports complex pattern matching
EXISTS subquery existence check; performance is generally better than IN

📝 Exercises

Basic Question (Difficulty: ⭐): Use a LIKE query to find users whose email addresses contain '@gmail.com' and whose usernames begin with 'J'.
Advanced Problem (Difficulty ⭐⭐): Use REGEXP to match mainland China cell phone numbers (starting with 1, with the second digit being 3–9, and a total of 11 digits).
Challenge Question (Difficulty: ⭐⭐⭐): Compare the differences in query results between NOT IN and NOT EXISTS when NULL values are present.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

MySQL: Advanced Filtering and Regular Expression Queries in…

1. What You'll Learn

2. A True Story About a Search Feature

(1) Pain Point: Inaccurate Search Results

(2) The REGEXP Solution

3. AND/OR In-Depth

▶ Example: Complex Combinations of Conditions

4. IN/NOT IN

▶ Example: List Query

5. Advanced BETWEEN

▶ Example: Range Query

6. The LIKE Wildcard

▶ Example: Fuzzy Matching

7. REGEXP Regular Expressions

(1) Common Regular Expression Metacharacters

▶ Example: REGEXP Query

▶ Example: REGEXP vs LIKE

8. EXISTS Subqueries

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好