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 w3wiki 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 w3wiki;
USE w3wiki;
-- 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:
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 w3wiki;
USE w3wiki;
-- 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:
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.
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.