How to Combine LIKE and IN in SQL Statement

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.

SQL Combine 'LIKE' and 'IN' Operator

The 'LIKE' operator searches for a specified pattern in a column within a string, and the 'IN' operator allows users to filter data based on a set of specified values.

By combining LIKE and IN, users can filter results based on patterns and specific values simultaneously, allowing users to create more complex and precise search conditions in SQL statements.

Syntax to combine SQL LIKE and IN Operator

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

Demo SQL Database

Before learning how to combine LIKE and IN in SQL, first we will create a table named "product".

SQL

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category_name VARCHAR(50),
    price DECIMAL(10, 2)
);
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);

These queries will create the following SQL table:

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

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.

Conclusion

In conclusion, combining the 'LIKE' and 'IN' operators in an SQL statement offers a powerful and flexible approach to filter 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.

Article Tags :

Databases

SQL

SQL-Query