How to Combine LIKE and IN an SQL statement?

In SQL, combining the ‘LIKE‘ and ‘IN‘ operators can greatly enhance your ability to retrieve specific data from a database. This article will explore how to effectively use these operators together to perform more complex queries. By combining these operators, we can efficiently filter data based on multiple criteria, making our queries more precise and targeted.

In this article, we’ll explore how they work together seamlessly, helping users navigate complex data collection scenarios. Learn how to filter data with precision, creating focused queries that boost your SQL skills.

Combining ‘LIKE’ and ‘IN’ in an SQL Statement

The ‘LIKE’ operator is used to search for a specified pattern in a column within a string, while the ‘IN’ operator allows you to filter data based on a set of specified values, and also allows multiple values in a WHERE clause.

By combining them, you can filter results based on patterns and specific values simultaneously, we can create more complex and precise search conditions in our SQL statements.

Syntax for the above-mentioned concept:

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

Setup Environment

Before demonstrating the example for this concept first, we will create a table named “product”.

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category_name VARCHAR(50),
    price DECIMAL(10, 2)
);

Then let us insert some data into ‘procudct’ table:

INSERT INTO products (product_id, product_name, category_name, price)
VALUES
    (1, 'Apple iPhone', 'Electronics', 999.99),
    (2, 'Book: SQL Mastery', 'Books', 29.99),
    (3, 'Blue T-Shirt', 'Clothing', 19.99),
    (4, 'Banana', 'Groceries', 0.99),
    (5, 'Bluetooth Speaker', 'Electronics', 49.99),
    (6, 'Black Jeans', 'Clothing', 39.99);

Output:

+------------+---------------------+---------------+-------+
| product_id |    product_name     | category_name | price |
+------------+---------------------+---------------+-------+
|     1      |    Apple iPhone     |   Electronics | 999.99|
|     2      |   Book: SQL Mastery |     Books     | 29.99 |
|     3      |    Blue T-Shirt     |    Clothing   | 19.99 |
|     4      |        Banana       |   Groceries   |  0.99 |
|     5      | Bluetooth Speaker  |   Electronics | 49.99 |
|     6      |     Black Jeans     |    Clothing   | 39.99 |
+------------+---------------------+---------------+-------+

Using ‘LIKE’ and ‘IN’ operators

Now let’s use the ‘like’ and ‘in’ operators in sql to show the above discussed concept.

Example 1: Filtering Products with Names Starting with ‘A’ or ‘B’ in Specific Categories

Query:

SELECT *
FROM products
WHERE product_name LIKE 'A%' OR product_name LIKE 'B%'
AND category_name IN ('Electronics', 'Clothing');

Explanation:

This query retrieves all products whose names start with ‘A’ or ‘B’ and belong to the categories ‘Electronics’ or ‘Clothing’.

• The ‘SELECT *‘ statement retrieves all columns from the ‘products’ table.
• The ‘WHERE‘ clause specifies the conditions for filtering the data:
• The ‘product_name LIKE ‘A%’‘ condition filters products whose names start with ‘A’.
• The ‘OR’ operator allows us to include products whose names start with ‘B’ as well.
• The ‘category_name IN (‘Electronics’, ‘Clothing’)’ condition filters products belonging to the specified categories.

Output:

+------------+------------------+---------------+-------+
| product_id |   product_name   | category_name | price |
+------------+------------------+---------------+-------+
|     1      |   Apple iPhone   |   Electronics | 999.99|
|     3      |   Blue T-Shirt   |    Clothing   | 19.99 |
|     5      | Bluetooth Speaker|   Electronics | 49.99 |
|     6      |   Black Jeans    |    Clothing   | 39.99 |
+------------+------------------+---------------+-------+

Example 2: Filtering Electronics and Accessories with Product Names Including ‘Phone’ or ‘Speaker’

SQL Query:

SELECT *
FROM products
WHERE product_name LIKE '%Phone%' OR product_name LIKE '%Speaker%'
AND category_name IN ('Electronics', 'Accessories');

Explanationfiltering:

This query retrieves all products whose names contain the word ‘Phone’ or ‘Speaker’ and belong to the categories ‘Electronics’ or ‘Accessories’.

• The ‘SELECT *‘ statement retrieves all columns from the ‘products’ table.
• The ‘WHERE‘ clause specifies the conditions for filtering the data:
• The ‘product_name LIKE ‘A%’‘ condition filters products whose names start with ‘A’.
• The ‘OR‘ operator allows us to include products whose names start with ‘B’ as well.
• The ‘category_name IN (‘Electronics’, ‘Accessories’)‘ condition filters products belonging to the specified categories.

Output:

+------------+------------------+---------------+-------+
| product_id |   product_name   | category_name | price |
+------------+------------------+---------------+-------+
|     1      |   Apple iPhone   |   Electronics | 999.99|
|     3      |   Blue T-Shirt   |    Clothing   | 19.99 |
|     5      | Bluetooth Speaker|   Electronics | 49.99 |
|     6      |   Black Jeans    |    Clothing   | 39.99 |
+------------+------------------+---------------+-------+

Conclusion

In conclusion, combining the ‘LIKE’ and ‘IN’ operators in an SQL statement offers a powerful and flexible approach to filtering data based on both specific patterns and predefined categories. This versatile combination enhances query capabilities, allowing for precise and efficient retrieval of relevant information from the database.