Introducing the WHERE clause - SQL Server Tutorial

- In this lesson, we're going to introduce you to the WHERE clause in SQL Server. Now, prior to using the WHERE clause in all of the queries that we've executed so far, we've restricted the values that are being returned from the database, but only at the column level, or at the attribute level. As an example, let's take a look at this query. Select login ID, job title, and marital status from the human resources schema in the employee table. If we run that, we can see that we will return a certain amount of records or rows, and in this case, 290. But we have restricted the columns that we have actually returned. So the human resources data employee table actually has more columns than that, but we've chosen to restrict the return values by only specifying the column names that we wanted. So again, all we're doing here really, is just restricting the number of columns that we're seeing. But what if we want to restrict the number of records that get returned? In our second query, we're going to use the WHERE clause, in this case, to return only the records in the employee table where the employees are actually married. So now what we're going to do, is we're actually going to restrict the row as well. So, our select clause will be focusing on these three columns. We're not going to return all of the columns, just three. But we're also going to restrict the number of rows returned. And we do that with this WHERE clause. We specify the marital status equaled M. We'll dissect that in just a moment. Let's run it first. So again, the same three columns that we had before, that's not changed. The number of rows has. 146 versus 290 in the previous query. So how did we restrict the rows, or how do we reduce the number? The key is in this statement here, which is the WHERE clause, the field name, what we call the conditional, or a relational operator and then a value. The WHERE is a way of saying, okay SQL, I want you to return values for me from the database, but only if they match a certain thing, and that certain thing is found in the marital status column. And the value that I care about, is M. Which indicates the marital status. M for married. Now, we can go in and take a look at our database table, so if we were to come over here and expand the database, and go to adventure works, expand the tables and find human resources employee, and go under our columns, then we can actually find that we have a marital status column. And again, our database designers have decided that we're going to save space with the data, and we're going to set this up as an nchar data type with only a length of one. In other words, we're just using a single character. That's it. It saves space in the database, and of course, it's relatively easy for us to understand that M would be married, S would be single, W would be widowed. We're interested in filtering by using the WHERE clause. So that's a first example. We've basically limited the number of rows that are returned by filtering a specific field, based on a value. We can do the same thing with the gender column, so that we can say that I want to see login ID, job title and gender for all employees that are male. So, we use the WHERE clause, we use the gender field, and we use the M for the male. And if we go ahead and run that query, we find that out of 290 rows, we've got 206 rows where we have male employees. It's also important to note as well, that we don't have to include that column in our results set in order for it to still filter. So if we run this query, you'll notice that we've dropped of the gender column, but we still only return 206 rows and the reason being is because the gender column exists in the employee table. Just because we're not outputting in the results set doesn't mean that we can't include it in a WHERE clause or in a filter condition. We can also take a look at numeric values, and in this case, we'll use the WHERE clause to bring back only the records of employees that have only 99 hours of vacation time. We might want to do this because we'll need to send them a letter to say that if you don't use some vacation time, perhaps you're going to lose it by the end of the year. So let's run this query. And we see that because I said star, we're bringing back all of the columns, but we're restricting the results to only employees that have 99 hours of vacation time. And in this case, there's only three rows. So the select star was on purpose, because, again, what I wanted to show from that was we're restricting columns here, using our select statement. The differentiator is that we're restricting rows here, by using the WHERE clause. So that's a quick introduction to filtering results using the WHERE clause. And then, in the next lesson, we'll take a look at some of the relational operators that we can use in our WHERE clauses, so you can see how you can manipulate, or do different types of comparisons. We may want to look for people that have more than 99 hours of vacation, or less than 99 hours of vacation, or maybe we're not quite sure when we're searching on a text field what all of the text might be, so we can use certain wild card characters. So that's coming up in a future lesson. Again, this was just focusing on introducing you to the WHERE clause and how you can restrict the numbers of rows coming back by setting up filtered conditions within the WHERE.