Read this article to understand the Some important “Like Between IN Clause” keywords along with the examples. We have shared few examples and we have shared basic as well as advance level common interview questions also.

In this post, I will provide you a short overview of Where clause with Like, Between and IN keyword. In addition, we have covered some questions which we receive from the audience. 

First, let’s talk about the theory of Like Between IN clause / keyword and why we should use it.

LIKE

Definition

Like keyword is used to match the pattern. We can use the like keyword with WHERE clause. Like is used for applying such constraints where we have to match either partially or fully. There are mainly two symbols which we can use with LIKE operator for creating pattern. Lets learn something about the patterns.

What is a pattern?

When we have to match some strings with the actual data. Below are the few examples of different matching criteria’s.

Lets assume we have name “John” into database and we have to select it.

We can create below patterns for fetching the data where name is John.

1. Fetch the records that start with J.
2. Fetch the records that end with n.
3. Fetch the records that having ‘oh’.
4. Fetch the records that ends with hn or ohn.
5. Fetch the records that have only one character before ohn.
6. Fetch the records that have only two character after Jo.

It means we can create any number of combinations for fetching the records using pattern. Now, lets convert above pattern into SQL format.

There are two symbols which we can use with Like operator i.e _ (underscore) and % (percentage). When we have to match only single characters before or after then we use _ (underscore). When we have to match any number of characters before or after then we use % (percentage).

Pattern Example

Lets convert above sentences one by one into SQL Like format.

Sr . No	Pattern	Description	SQL Conversion	Comment
1	Fetch the records that start with J.	It means Start with J and then any number of characters	SELECT * FROM Customers WHERE first_name LIKE ‘J%’;	% means any number of characters after J.
2	Fetch the records that end with n.	It means start with any characters and any number of characters before	SELECT * FROM Customers WHERE first_name LIKE ‘%n’;	% means any number of characters before n.
3	Fetch the records that having ‘oh’.	It means any number of characters before oh and any number of characters after oh.	SELECT * FROM Customers WHERE first_name LIKE ‘%oh%’;	% means any number of characters before oh and any number of characters after oh.
4	Fetch the records that ends with hn or ohn.	It means start with any characters and any number of characters before hn or ohn.	SELECT * FROM Customers WHERE first_name LIKE ‘%hn’;	% means any number of characters before hn.
5	Fetch the records that have only one character before ohn.	Only one character before ohn means 4 character word only.	SELECT * FROM Customers WHERE first_name LIKE ‘_ohn’;	_ (underscore) means only one character before ohn.
6	Fetch the records that have only two character after Jo.	It means two characters after Jo that means 4 characters word.	SELECT * FROM Customers WHERE first_name LIKE ‘Jo__’;	two _ (underscore) means only two characters after Jo.

Reasons to Use

The LIKE keyword is used in a WHERE clause to perform pattern matching on string values. Below are the some key areas where we need to use Like keyword.

1. Show the all messages that contain INDIA keyword.
2. Show the names that has the ‘oh’ keyword.
3. Show the records that partially matches with ‘%2023% year pattern’.

Syntax

SELECT column1, column2
FROM table_name
WHERE column1 LIKE 'abc%'   -- Match strings that start with 'abc'
AND column2 LIKE '%xyz'   -- Match strings that end with 'xyz'
AND column3 LIKE '%p_q%'  -- Match strings that have 'p' followed by any character, then 'q'

The condition can be a combination of one or more column values compared to a specific value. Pattern can be the combination of %(percentage) for any number of characters and _(underscore) for single character.

BETWEEN Keyword

The BETWEEN keyword in SQL is used to filter rows in a query based on a range of values for a particular column. It allows you to specify a lower and an upper bound, and retrieve rows where the column value falls within that range. We can use between for matching the date range, number range or even we can use text.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN start_value AND end_value;

Example

Example 1:

Select records where age is between 10 to 20. Both start and end value will be included in the result. Between is inclusive operator.

Example 2:

If we have to skip records for a certain range then we can use between along with the NOT operator. Below is the example that skip records between 10 to 20 age range.

customer_id	first_name	last_name	age	country
1	John	Doe	31	USA
2	Robert	Luna	22	USA
3	David	Robinson	32	UK
4	John	Sinha	22	UK

Great!! I hope you are able to use the between keywords for any range constraint. If we have to select the records whose ages are like 11,13,15,17 etc. Can we select it using Between?

No! The answer is No because here we don’t have any range. ohh!! Then how can we solve such scenarios?

We can use multiple OR conditions or we can use IN keyword.

IN Keyword

The IN keyword in SQL is used to filter rows in a query based on a list of values for a particular column. It allows you to specify a set of values and retrieve rows where the column value matches any of the values in the set. IN keyword is a alternative of multiple OR.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column_name IN (value1, value2, ..., valueN);

Example

Example 3:

SELECT * FROM Customers  
WHERE age IN ( 11,13,15,17);

Above example print the records where age is 11 or 13 or 15 or 17. Instead of using multiple OR conditions we have used IN keyword to match those.

Example 4:

Lets understood the below example with SQL.

customer_id	first_name	last_name	age	country
2	Robert	Luna	22	USA

How the 2nd customer printed? Lets think and let me know if any concern in the comment section.

Linking

Where clause is used in Select , Update, Delete statement. We can use Where with the Like , Between, IN keywords. You can refer all the interview questions. Please refer all the links for understanding the Where in depth with all advanced keywords.

SQL Like, Between, IN Clause with Latest SQL Interview Questions 2023

Basic Like, Between and IN keyword SQL Interview questions

1. What is the “LIKE” keyword in SQL used for?
2. How is the “LIKE” keyword used in SQL?
3. What are some examples of using the “LIKE” keyword in SQL?
4. How can I use the “BETWEEN” keyword to filter records based on a range of values?
5. Can I use the “BETWEEN” keyword with dates and timestamps?
6. Can I use the “IN” keyword with a subquery in SQL?

Intermediate Like, Between and IN keyword SQL Interview questions

1. How can I use multiple wildcard characters in a “LIKE” pattern?
2. How can I escape wildcard characters in a “LIKE” pattern?
3. Can I use “BETWEEN” with multiple ranges in a single query?

Advanced Like, Between and IN keyword SQL Interview questions

1. How can I perform case-insensitive “LIKE” searches?
2. Can I use “BETWEEN” with non-numeric columns that have custom ordering rules?
3. Can I use the “IN” keyword with a subquery that returns a large number of values?

Questions covered from comment section

In this section we will cover the new questions as well as answers. Please comment the questions into comment section so we will include it here. Below are few questions which we thought should be covered.

Que : How can I escape wildcard characters in a “LIKE” pattern?

Ans : We can match the special characters using Like keyword. We need to use a escape characters while matching that character. Below is the example that match special character.

SELECT * FROM my_table WHERE column1 LIKE '%\%';

Above statement will match the column1 value that ends with % symbol.

Quiz

Click on this link to solve few MCQ questions on Where clause with Like, Between and IN keyword.

I hope you enjoyed this session. Happy offline learning… Lets meet in next session.