DEV Community ๐Ÿ‘ฉโ€๐Ÿ’ป๐Ÿ‘จโ€๐Ÿ’ป

DEV Community ๐Ÿ‘ฉโ€๐Ÿ’ป๐Ÿ‘จโ€๐Ÿ’ป is a community of 966,155 amazing developers

We're a place where coders share, stay up-to-date and grow their careers.

Create account Log in
Cover image for Learn SQL: Microsoft SQL Server - Episode 3: Where Clause - Part 1
Goran Kortjie
Goran Kortjie

Posted on • Updated on

Learn SQL: Microsoft SQL Server - Episode 3: Where Clause - Part 1

goran-where?

Majority of the time when selecting data from the database, we want to narrow down the result set based on certain criteria. This is where a WHERE clause can help us.

We will be discussing the Where clause and the syntax to use it.

skid-4

Where Clause Statements

Where clause specifies the criteria to narrow down.

We use the following syntax to use a Where clause.

Select [Column Names | *]
Where [Column Name] [operator] [criteria]
Enter fullscreen mode Exit fullscreen mode

We narrow down our result set with the criteria.

skid-09

We can use any number of criteria to further narrow down our result set by using the AND or OR condition, with the following syntax.

Select [Column Names | *]
Where [Column Name] [operator] [criteria]
AND | OR [Column Name] [operator] [criteria]
Enter fullscreen mode Exit fullscreen mode

This will all make more sense when we actually make use of these query statements in SSMS.

skid-6

Operator

The operators that we can use are:

=
<, >, >=, <=
Between (inclusive) - for dates and numbers.
Enter fullscreen mode Exit fullscreen mode

An example of how we would construct a query statement using the Where clause is as follows:

Syntax:

Select firstName
From Person.Person
Where firstName = 'John'
Enter fullscreen mode Exit fullscreen mode

When we have a criteria that is a string we need to enclose it in quotation marks. This can be either double quotes or single quotes, however remember you cannot mix the two.

'John" - This will be invalid.

skid-09

In SSMS lets start using the Where clause in our query statements.

where-query-statement

As we can see we get a bunch of results back with people whose first name is John. But this of course is too much data if we were looking for a specific John. We can narrow down the query statement even further by using adding more criteria.

skid-11

This time we are looking for a person with the first name as John and the last name as Ford. We use the AND operator to help narrow down this distinction.

where-query-and-statement

We have narrowed down the query to find the person with the first name as John and the last name as Ford. But what if we wanted to find the person with the first name of John or the last name of Ford.

Image description

In this case we use the OR operator:

where-query-or-statement

This time we get the results back of every person with the first name of John or with the last name of Ford.

We can use as many criteria as we want.

where-query-or-and-many-statement

We can now look at the other operators available to us. Letโ€™s start with the More Than operator.

skid-09

Back in SSMS, letโ€™s create a new query window and experiment with the More Than operator. These operators are usually used with numbers and dates, however they can also be used with sting values.

where-query-more-than

Note that we pull all the specified data, however 2000 was not included in the result set. This is because we queried for more than 2000. If we want to include 2000 in the result set, we specify More Than or Equal to:

where-query-more-than-or-equal

We can also use this syntax to see the result set that has a criteria Less Than 2000:

where-query-less-than

As we expected to see, we get the results of every data point less than 2000. If we want to include 2000 in the result set then we use Less Than or Equal to:

where-query-less-than-or-equal

skid-4

Between

The between criteria uses the startValue and endValue and returns the result set that is between those two values, including those two values.

For instance we want to see the data of Person.Person table with the BusinessEntityID between 2000 and 2021, we use the following syntax:

Select *
From Person.Person
Where BusinessEntityID between startValue and endValue
Enter fullscreen mode Exit fullscreen mode

We can see this in action in SSMS.

where-query-between

We could have gotten the same result set by using the AND operator as well, lets see how we could have constructed it.

where-query-between-with-And

Between in a sense removes the need to use AND and it simplifies the query statement a bit.

skid-6

Thats the basics on how we can narrow things in SQL, I hope you enjoyed this discussion, and for more on SQL you know Where to find me.

meditation

Top comments (2)

Collapse
 
skozeniuk profile image
SashaKozeniuk

Thanks for this article! I correctly understood that you can edit SDF file using programs such as Microsoft SQL Server? I read about it on mypcfile.com.

Collapse
 
ifierygod profile image
Goran Kortjie Author

I am not to sure about that, I will need to look into that. The article seems to suggest it. Thanks for mentioning it

๐Ÿ‘€ Just want to lurk?

You can still create an account and turn on features like ๐ŸŒš dark mode.