Module 2: Filter Data with WHERE Clause - Part 1

Previous Tutorial - Next Tutorial

So right now I’m in my SQL server management studio, as I said in another video that to learn the SQL server concept and T-SQL fundamentals, we will use this adventure work database. Now from which website to download this database and how to restore it for that, you need to watch the video of Module-1 of this course.

Filtering Data Explained

Now, let us focus on this query. Here I have written one SELECT statement in which I want to return some column values from this person’s table like title, last name, first name, modified date, etc. If I execute this query, I will get the data. Now from this person table, I want to filter out the person names whose last name is Galvin from this last name column.

So for that, I need to write the WHERE clause, which looks something like(WHERE LastName = 'galvin'). Now if I execute this query, I will get those records or the person whose last name is Galvin. Let's move on to another operator, which is not equal to the operator. Now this time I want to find those records whose title is not Mister(Mr). So for that, I need to redefine the WHERE clause something like(WHERE Title <> 'Mr.').

Now if I execute this query, I will get only those records whose title is not Mister(Mr). So as you can see that in the title column, I will not find Mister(Mr) value. This search condition would have evaluated to true for those rows, which has a title column value other than Mister (Mr) and those rows would have been returned. However, this search condition evaluates to unknown for rows with null in the title column and those rows also get discarded.

So you can see that we have no null value over here. If you run this query without the WHERE clause, then you can see that the title column also contains some null value. In SQL server the concept of null follows to deal with the missing information in the relational model.

The null means unknown or missing information in the SQL server. So to return those rows where the title column value is null, then generally we can think of something like(WHERE Title <>'Mr.'). So here to get the null value we need to use a logical operator.

Logical Operators

There are certain logical operators like End and OR. We will study that operator in the next slide. But for now, let’s use the OR operator. If OR means either this condition evaluates to true then the SQL server will return the record from the person table. So if I execute this query, but still when I’m not getting the null value from the title column. So the comparison with null using equality operator in the WHERE clause results in an unknown value. So that’s why you don’t get those rows that contain a null value. So basically comparing with null or unknown will result in a null.

How to Filter Data

Let's understand this concept by writing some simple SELECT statement. So, for example, write (SELECT 'return some value'), if I execute this SELECT statement, then I will get the data and get the result. Now if I use the WHERE clause and in the WHERE clause, I define a condition like(SELECT 'return some value' 1=0).

There's a universal truth that 1 is never equal to 0. Now if I execute this query, I will get nothing. But if I define 1 in the right-hand side expression. This 1 is equal to 1 evaluates to, If I execute this query, now I get the result. Now instead of 1, if I write null over here and execute this query, then I will get nothing. As I said earlier, using an equality operator or not equality operator with NULL will result in an unknown value or null.

So even if I write null on the left-hand side, I will also get to handle null in the T-SQL server. It will give us another operator, which is IS NULL. So this operator will check if a null value is present or not. If I re-define this query and write something like(WHERE Title <>'Mr.' or Title IS NULL) and I execute this query, this time I will get the title with the null as well as the original value other than Mister(Mr).

How to Filter Data with Where Clause

Range Operators

Let’s move on to the range operator. Sometimes you want to know whether this column value falls within a certain range or not. So there are actually a couple of ways to know it. But first, let us understand this between operators.

So let me open a new query window. Here I have written one normal query in which I want to return some product-related information from the product table. If I execute this query product-related information.

Now out of these records, I want to know about those products whose safety stock level is between 4 and 50. So let's see how we can use this between operators to get that data and it would define the WHERE clause in which I want to differentiate between operators for that safety level between 4 and 50.

Now if I execute this query, I will get only that product information whose safety stock level is in between 4 and like 15, 43, etc. So now let's move on to another operator, which is greater than the operator. This operator is used to test or return those rows from the table or specify column value is greater than the right-hand side expression.

Operator Examples

So for example, now this time I want to return those products whose safe stock level is greater than 500. Let’s see how we can use this greater than the operator. Now let us execute this query and we will get only the products whose safety stock level is greater than 500, like 1000, 800, etc.

Let's move on to another operator, which is less than the operator. This operator is used to test or return those rows from the table whose specified column value is less than the right-hand side expression. For example, now this time I want to get those products whose safety stock level is less than 500. So for that, I need to use less than the operator and execute the query. So now you can see that I get the products whose safety stock level is less than 500.

Filter Data

Now let's move on to another operator, which is greater than or equal to the operator. This operator is used to test or return those rows from the table whose specified column value is greater than or equal to the right-hand side expression.

So now in this query, this time, if I specified greater than or equal to the operator and execute this query. So this time I will get the product information whose safety stock level is greater than 500 as well as equal to 500. So as you can see that I will get the safety stock level 1000, 800 as well as 500. So the same way here you can use the less than or equal to an operator to get the safety stock level column value, which is less than or equal to 500.

Web API for developers Free Trial Offline SDK

Here's SQL video tutorial:

Other useful tutorials: