How to Combine MySQL IN and LIKE Operators?

In database management, MySQL stands as one of the most popular relational database management systems. As developers strive to extract specific data from their databases, the judicious use of SQL operators becomes imperative. Among these operators, the IN and LIKE operators play crucial roles in facilitating flexible and precise data retrieval.

This article aims to provide a comprehensive guide on combining the MySQL IN and LIKE operators. By understanding the nuances and syntax of these operators, developers can enhance their proficiency in crafting SQL queries that meet the demands of complex data filtering scenarios.

IN Operator

The IN operator in MySQL is a powerful tool for filtering data based on a predefined set of values. It allows developers to specify a list of values and retrieve records where a particular column matches any of those values. This operator is particularly useful when dealing with scenarios where data needs to be filtered against multiple possibilities.

Syntax:

SELECT column1, column2, …

FROM table_name

WHERE column_name IN (value1, value2, …);

LIKE Operator

The LIKE operator in MySQL is employed for pattern matching within a specified column. It enables developers to retrieve records where a particular column’s value matches a specified pattern. This pattern can include wildcard characters such as % (matches any sequence of characters) and _ (matches any single character), offering a versatile means of searching for data.

Syntax:

SELECT column1, column2, …

FROM table_name

WHERE column_name LIKE pattern;

IN and LIKE Operators

Combining the IN and LIKE operators in MySQL can be especially beneficial in scenarios where data needs to be filtered based on multiple values and a flexible pattern. This combination empowers developers to construct intricate queries that cater to nuanced search criteria.

Syntax:

SELECT column1, column2, …

FROM table_name

WHERE column_name IN (value1, value2, …)

AND another_column_name LIKE pattern;

Examples of MySQL IN and LIKE Operators

Example 1: Combining IN and LIKE for Flexible Filtering

Consider a scenario where we created the DB as GeeksforGeeks you have a ‘GFG’ table, and you want to retrieve users who belong to a specific department (IN operator) and whose usernames start with a particular pattern (LIKE operator).

-- SQL Code 

CREATE DATABASE GeeksforGeeks;
USE GeeksforGeeks;

-- Create a sample 'users' table
CREATE TABLE GFG (
  id INT PRIMARY KEY,
  username VARCHAR(50),
  department VARCHAR(50)
);

-- Insert corrected sample data
INSERT INTO GFG VALUES
  (1, 'john_doe', 'HR'),
  (2, 'jane_smith', 'IT'),
  (3, 'sam_jones', 'Finance'),
  (4, 'mary_white', 'HR'),
  (5, 'jennifer_davis', 'HR'); -- Additional user with HR department

-- Query to retrieve users from HR department whose usernames start with 'j'
SELECT id, username, department
FROM GFG
WHERE department IN ('HR', 'Finance') 
  AND username LIKE 'j%';

Output:

output

Explanation:

The output includes users from both the ‘HR‘ and ‘Finance‘ departments whose usernames start with the letter ‘j‘. In this case, it retrieves ‘john_doe‘ and ‘jennifer_davis‘ from the ‘HR’ department and ‘sam_jones’ from the ‘Finance’ department. The condition username LIKE 'j%' ensures that only usernames starting with ‘j’ are included in the result.

Example 2: Using IN and LIKE with Multiple Conditions

Let’s consider a more complex scenario where you have a ‘products‘ table, and you want to retrieve products with specific categories (IN operator) and whose names contain a certain keyword (LIKE operator).

-- SQL Code 

CREATE DATABASE GeeksforGeeks;
USE GeeksforGeeks;

-- Create a sample 'products' table
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  category VARCHAR(50)
);
-- Insert sample data
INSERT INTO products VALUES
  (1, 'Laptop', 'Electronics'),
  (2, 'Desk Chair', 'Furniture'),
  (3, 'Notebook', 'Stationery'),
  (4, 'Camera', 'Electronics'),
  (5, 'Office Chair', 'Furniture');

-- Query to retrieve Electronics and Furniture products with names containing 'a'
SELECT id, name, category
FROM products
WHERE category IN ('Electronics', 'Furniture') 
  AND name LIKE '%a%';

Output:

output

Explanation:

The output includes products from both the ‘Electronics‘ and ‘Furniture‘ categories whose names contain the letter ‘a’. The conditions category IN (‘Electronics’, ‘Furniture’) and name LIKE ‘%a%‘ filter the results based on the specified criteria.

Conclusion

The combination of MySQL `IN` and `LIKE` operators allows for sophisticated data querying in relational databases. The `IN` operator is efficient for predefined values, while the `LIKE` operator offers flexibility for intricate patterns. By combining these operators, developers can create queries that cater to complex search criteria, enabling them to navigate diverse scenarios. Mastering these operators enhances database operations efficiency and contributes to the development of robust applications that rely on seamless interaction with MySQL databases.