Learn SQL: Microsoft SQL Server - Episode 3: Where Clause - Part 2
Tags: sql, database, beginners, datascience
Published: 2021-10-27 05:09:39

![goran-where-2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w9wy51xg19gosx45cwm3.png)

WHERE is an incredible method to narrow down results from a database based on our specified criteria. But what if we wanted to select records based on a pattern?

In that case we can use the LIKE operator in the WHERE clause.

![like](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ncwu1kqwekcivbxni4lh.gif)

####Like Operator

The **LIKE** operator in **WHERE** allows you to match a pattern.

The syntax is as follows:

```
Select [Column Names | *]
Where [Column Name] Like [Criteria]
```
We can add additional options as well.

```
Select [Column Names | *]
Where [Column Name] Like [Criteria]
AND | OR [Column Name] [Operator] [Criteria]
```

We can see this in action inside <abbr title="SQL Server Management Studio">SSMS</abbr>.

![skid-7](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/olmxk8jm4qeoqka8ypho.png)

Let's start off with a _New Query_. Remember to make sure the AdventureWorks database is selected on the dropdown menu, otherwise the query statements will not work as expected.

This time we will be using a different table than before, instead of the **Person.Person** table, we will be using the **Production.Product** table.

![skid-7](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yu6zqrsa9skwxkszgdu8.gif)

Let's see what kind of records are available to us in the **Production.Product** table. We can use a simple select all to get all the data from this table.

![select-all-production-product](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m32o54rab91jnzcfop0q.gif)

We get the result set of this table as expected. But let's say we want to see all the products that start with the letter A. In this case we can use a **LIKE** statement.

![skid-19](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t3yum0pvto6faky6lig0.gif)

We construct this query by specifying the _Column name_ in the **Where clause** and the A in the **LIKE** clause, since we want to match all the products that start with the letter A. 

But we also want to match all the words that follow after A, to do this we put in a _**Wild-Card**_. A **Wild-Card** matches anything. How it will look is as follows:

For example
```
Select *
Where name Like 'A%'
```

The **%** is the **Wild-Card** character and it will match everything after A.

![where-in-like](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5u3gcqhbin1vnq2xk5yb.gif)

![skid-19](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t3yum0pvto6faky6lig0.gif)

If we wanted, for instance to find all the products names that ended with an A, then we can move the **%** sign to the front and the A to the back.

![where-in-like-percent](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cxlqs7udchsrpx5lk3n9.gif)

What if we wanted to find a _product name_ that has the word 'paint' in it and we do not care where in the product name 'paint' resides.

![skid-7](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yu6zqrsa9skwxkszgdu8.gif)

We could do this by adding the **Wild-Card** at the start of our criteria and at the end of our criteria.

![where-in-like-percent-on-both-sides](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/580s9lzfxkx9wgm3dgtl.gif)

This means that no matter what a paritcular word starts with and no matter what it ends with, it should have the word 'paint' in it.

>Remember the query window is case-incensitive

![skid-7](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/olmxk8jm4qeoqka8ypho.png)

####Multiple values in Where Clause

We have discussed how to select values from a table, using a **Where clause** and a specifed value from a column.

For example: If we wanted to select a value in a table where the ID is 123.

```
Select *
From [Table Name]
Where ID = 123
```
If we wanted to select values where ID is 123 or 234 we would construct it as follows.

```
Select *
From [Table Name]
Where ID = 123
Or ID = 234
```

When we need to add in more **OR** statements then we just need to specify it as we did before and we can do this as many times as we prefer.

![skid-19](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t3yum0pvto6faky6lig0.gif)

This is generally fine in a situation where we have a limited amount of values, two or maybe even ten values. But if we have hundreds of values for ID then it becomes cumbersome to manage and SQL becomes difficult to read and understand.

This is where another SQL clause comes into play, this clause is called **IN**. The syntax for using an **IN** query statement is similar and simple to what we are used to.

```
Select [Column Names | *]
From [Table Name]
Where ID in (criteria, criteria, criteria...)
```
For example:
```
Select [Column Names | *]
From [Table Name]
Where ID in (123, 434, 234, 233, 112, 431, 332...)
```

We enclose the criteria in parenthesis and separate them by a comma. We can see how this works in practice inside <abbr title="SQL Server Management Studio">SSMS</abbr>.

![skid-7](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yu6zqrsa9skwxkszgdu8.gif)

We are going to use the **Person.Person** table to demonstate **IN**.

Let's assume that we were given values from the sales department and they wanted to know the _first name_ and _last name_ of a particular group of people. The values they have provided are ID's.

The cumbersome way of performing this type of query would be as follows.

![where-or-statement](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mht9y31pdjwjaff41s1m.gif)

As we discussed we can perform the same query by using the **IN** query method.

![where-query-with-in](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d9ez1121tp8ksflhd55d.gif)

As we can see the same result set is returned if we run both queries simultaneously.

![skid-19](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t3yum0pvto6faky6lig0.gif)

If for instance you have to use sting values, then you need to enclose them in single quotes. For example we need to find the values for _firstName_. We would construct the query statement as follows:

![where-query-with-in-string](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d9ez1121tp8ksflhd55d.gif)

If we want to use **Date** values, then we would also use single quotes.

![skid-2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6vbn501o5gfnsie1rybp.png)

Thats it! Well done on making it this far, we have seen how we can narrow down our result sets even further with _Where_ and _Like_, so I hope you **Liked** this article. See you next time...

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1qno26o6c7f2e7hpczfr.gif)