In this SQL Server tutorial, you will explain the WHERE condition in SQL Server to filter the rows from the table.
First, you will understand the ‘What is WHERE condition in SQL Server’ with syntax. Then, you will use different operators like equality, comparison, AND, OR, BETWEEN, and IN to filter the rows based on one or multiple conditions.
WHERE Condition in SQL Server
While retrieving information from the table, you often require some rows based on specific conditions or criteria. In other words, If you need to filter the rows based on the particular criteria or the condition, use the WHERE condition or clause.
The syntax is given below.
SELECT column_name1, column_name2, ...
FROM table_name
WHERE condition;
In the above syntax, WHERE is a keyword followed by the condition that must met for a row to be returned.
With different examples, let’s see how to use the WHERE clause in a query to filter the data.
WHERE Condition in SQL Server with Equality Operator
You can use the equal operator (=) in the WHERE condition to filter the rows, and it only shows the rows containing the value equal to the value in the condition. For example, you have a Sales_By_Region table shown below.
Using the above table, suppose you want to know the sale amount of the North region only, or you need to filter rows based on the North region. For that, use the query below, which shows how to use the equality operator to filter rows.
SELECT * FROM Sales_By_Region
WHERE region = 'North';
From the output of the above query, the result set contains the rows based only on the North region. The result set contains only the sale amount related to the North region; here, you have filtered rows using the equality (=) operator in the WHERE condition.
Instead of ‘North’, you can specify the other region name, and you can specify all the values in the region column; when you execute the above query, it compares the word ‘North’ with all the values in the region column and returns the rows for the matching region.
This is how to use the equality operator in WHERE condition in SQL Server.
WHERE Condition in SQL Server with Comparision Operator
In the previous section, you have used the equality operator and are not limited to only that operator. You can use any comparison operator in WHERE conditions such as less than (<), greater than (>), less than equal to (<=), and greater than equal to (>=).
Consider that you need to find the sale amount greater than a certain amount in the SaleAmount column. For example, find regions with a sale amount greater than 1300$. Use the below query.
SELECT region, SaleAmount FROM Sales_By_Region
WHERE SaleAmount > 1300;
As you can see, only one region, ‘East’, contains a sale amount of more than 1300$. But here, you need to notice the query part WHERE SaleAmount > 1300, which includes the greater than (>) operator in the WHERE condition.
You can use other comparison operators such as <, <=, and >=, so in the above query, the value 1300 is compared with each value in the SaleAmount column, and the greater value than 1300 is included in the result with the region.
This is how to use the comparison operation in WHERE condition in SQL Server.
Filtering rows using the WHERE Condition in SQL Server Based on Multiple Conditions
In the WHERE, you can specify the multiple conditions, and based on both conditions, rows are filtered from the table. But how do you specify the multiple conditions? You can use the AND and OR operator.
First, let’s use the AND operator to specify two conditions; here, you can specify two conditions that should be met using the AND operator. Otherwise, it is going to filter any rows from the table.
For example, you need to find the number of sale amounts greater than 400$ in the West region. Here you have the condition. First, the region should be ‘West’ and second, the amount must be over 400$.
For that, use the below query.
SELECT region, SaleAmount FROM Sales_By_Region
WHERE region = 'West' AND SaleAmount > 400;
After executing the above query, the result set contains the two columns region containing the West region and SaleAmunt containing all the sale amounts greater than 400$.
Here, the query part WHERE region = ‘West’ AND SaleAmount > 400 contains the two conditions: the region = ‘West’ and the SaleAmount > 400. Both conditions are combined using the AND operator between them.
You can even combine multiple conditions using the AND operator, but remember all the conditions must met; if any of the conditions aren’t satisfied, then it doesn’t return any rows based on the specified conditions.
Next, let’s see how to use the OR operator to filter the rows in a table based on multiple conditions.
But in the case of the OR operator, if any conditions or criteria are met, then rows or results are returned based on those criteria.
For example, you want to find the number of sales greater than 900 in the North and East regions. We have three conditions: the sale amount must exceed 900, and the region can be North and East.
Look at the query below in the WHERE clause to see how conditions are specified.
SELECT region, SaleAmount FROM Sales_By_Region
WHERE region = 'North' OR region = 'East' OR SaleAmount > 900;
After executing the above query, it returns the sale amount greater than 900$ in regions North, South, and East.
But we haven’t specified the South region in the condition, although it also shows the sale amount from that region. That is because the OR operator, let’s understand the query part region = ‘North’ OR region = ‘East’ OR SaleAmount > 900.
Here, the conditions combined with the OR operator work independently, which means conditions aren’t dependent on each other. So here, SaleAmount > 900 means return the records containing the sales amount greater than 900. For this condition, the South region is included in the result.
region = ‘North’ OR region = ‘East’ means it also returns the records from the region North or East.
So, using the OR operator, you can specify multiple conditions in the WHERE clause; in the case of the OR operator, any conditions should match.
This is how to filter the rows using the WHERE condition in SQL Server.
Retrieving rows using BETWEEN in the WHERE Condition in SQL Server
If you want to filter or retrieve the rows between two values, you can use the BETWEEN operator to specify those two values. For example, the query below filters all the records between the North and South regions.
SELECT * FROM Sales_By_Region
WHERE region BETWEEN 'North' AND 'South';
As shown in the above result set, the sales amount between the two regions, such as North and South, is returned.
Here, in the query part, region BETWEEN ‘North’ AND ‘South’ means finding the records with the region value either North or South and including those records in the result set. Also, you can see that the two regions are combined with the AND operator.
This is how to retrieve the rows between two values using the BETWEEN operator in the WHERE condition in SQL Server.
WHERE Condition in SQL Server with IN Operator
You can filter the rows or records from the list of values using the IN operator in the WHERE condition in SQL Server. For example, look at the below query.
SELECT * FROM Sales_By_Region
WHERE region IN ('South','East','West');
After executing the above query, the result only contains the records from the South, East, and West regions. Using the IN operator, multiple values are specified for the region. So, if you have a list of values and, based on those values, want to filter the data, then use the IN operator.
This is how to use the IN operator with WHERE condition in SQL Server.
Conclusion
In this SQL Server tutorial, you learned how to filter or find the records from the table based on the conditions using the WHERE clause. Also learned how to use the different types of operators like AND, OR, BETWEEN, and IN to specify multiple conditions or values in the WHERE clause.
You may like to read:
After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.