Combining ‘LIKE’ and ‘IN’ in SQLite
Combining ‘LIKE’ and ‘IN’ operators in an SQLite statement allows for more intricate querying, especially when dealing with pattern matching and multiple value comparisons.
To understand Combining ‘like’ and ‘in’ in an SQLite statement we need a table on which we will perform various operations and queries. Here we will consider a table called products which contains product_id and product_name as Columns.
Example 1: Matching Products with Similar Names
Suppose we want to retrieve products whose names contain either ‘apple‘ or ‘banana‘. We can achieve this using the following query:
SELECT *
FROM products
WHERE product_name LIKE '%apple%' OR product_name LIKE '%banana%';
Output:
Explanation: In the above query, we retrieves all records from the products
table where the product_name
column contains the words “apple” or “banana” using the LIKE
operator with wildcard characters %
.
Example 2: Filtering Products by Multiple Names
If we want to filter products by specific names, such as ‘Apple’, ‘Banana’, and ‘Laptop’, we can use the ‘IN’ operator:
SELECT *
FROM products
WHERE product_name IN ('Apple', 'Banana', 'Laptop');
Output:
Explanation: In the above query, we retrieves all records from the products
table where the product_name
column matches ‘Apple‘, ‘Banana‘, or ‘Laptop‘ using the IN
operator.
Example 3: Combining ‘LIKE’ and ‘IN’ for Complex Queries
We can combine ‘LIKE’ and ‘IN’ operators for more complex queries. For example, to retrieve products with names containing ‘phone’ or ‘tablet’ and also having ‘a’ or ‘e’ in their names, we can write:
SELECT *
FROM products
WHERE (product_name LIKE '%phone%' OR product_name LIKE '%tablet%')
AND product_name LIKE '%a%' OR product_name LIKE '%e%';
Output:
Explanation:This query retrieves all records from the products
table where the product_name
column contains ‘phone’ or ‘tablet’ and also contains the letters ‘a’ or ‘e’.
Example 4: Combining ‘LIKE’ and ‘IN’ for Complex Queries for Products with Names Containing ‘a’ or ‘o’.
SELECT *
FROM products
WHERE product_name LIKE '%a%'
OR
product_name LIKE '%o%' ORDER BY product_id;
Output:
How to Combine LIKE and IN an SQLite
SQLite a lightweight and versatile relational database management system offers powerful querying capabilities through its support for various SQL operators. Two commonly used operators, ‘LIKE‘ and ‘IN‘, enable developers to write flexible and efficient queries for data retrieval.
In this article, we’ll explore how to combine ‘LIKE‘ and ‘IN‘ operators in SQLite statements with the help a sample table to demonstrate their practical applications.