Combine ‘LIKE’ and ‘IN’ Operators Examples

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');

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 |
+------------+------------------+---------------+-------+

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.

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');

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 |
+------------+------------------+---------------+-------+

Explanation:

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.

Combining ‘LIKE‘ and ‘IN‘ operators in SQL can greatly enhance users’ ability to retrieve specific data from a database. By Learning how to use these operators together users can perform more complex queries and efficiently filter data based on multiple criteria, making queries more precise and targeted.

This article explores how the LIKE and IN operators work together and help users navigate complex data collection scenarios. Learn how to precisely filter data, creating focused queries that boost your SQL skills.