The WHERE clause in SQL Server is used to filter records from the table. It is an optional clause that is used to limit the number of rows returned by the query. We can also say that this clause specifies a condition to return only those records that fulfill the defined conditions. The WHERE clause can also work with the UPDATE and DELETE query.
Need of WHERE Clause
When we perform an operation on the table using the SELECT statement, it will return all records from that table. If our table has many records, the SELECT statement can return unnecessary data because the application process only the set of rows at a time. In such a case, we need some conditions that return only those records that we want to see in the output table. Thus, we use the WHERE clause to restrict the query from fetching unnecessary records from a table and return only those rows that meet the condition mentioned in the WHERE clause.
Syntax
The following are the syntax that illustrates the WHERE clause in SQL Server:
SELECT column_list
FROM table_name
WHERE search_condition;This syntax has the following parameters:
Column_list: It is the name of the columns that we want to retrieve from the table in the result set.
Table_name: It is the table name from which columns will be selected.
Search_condition: It is a condition that returns only those records that fulfill this condition. It performs filtration on the table. It also indicates the logical expression or a combination of multiple logical expressions that evaluates the result: TRUE, FALSE, or UNKNOWN. If it evaluates to FALSE or UNKNOWN, the WHERE clause will not return any record.
The WHERE clause also supports the following operators to filter the records:
| Operator Name | Operator Symbol |
|---|---|
| Equal | = |
| Less Than | < |
| Greater Than | > |
| Less Than or Equal | <= |
| Greater Than or Equal | >= |
| Not Equal | <> |
| Search for a specific pattern | LIKE |
| Find records within given range | BETWEEN |
| Used to specify multiple values | IN |
Example
Let us understand the working of WHERE clause in SQL Server with several examples. We will table the employee_info table to demonstrate all examples of the WHERE clause. The employee_info table has the following data:
Example1: This example will use the simple equality operator to filter rows. The following statement will retrieve all employees whose working hour is equal to 12 and then sort them based on their name:
SELECT name, occupation, working_hours, salary
FROM employee_info
WHERE working_hours = 12
ORDER BY name;Executing the statement will return only those employees whose working hour is equal to 12:
Example2: This example retrieves those rows that meet two conditions in the WHERE clause. The following example returns the employee information whose working hour is greater than 10 and salary is less than 50000. Here we use the logical operator AND to combine the two conditions in the WHERE clause.
SELECT name, occupation, working_hours, salary
FROM employee_info
WHERE working_hours>10 AND salary<50000
ORDER BY name;Executing the statement will return all those employees whose working hour is greater than 10 and salary is less than 50000:
Example3: This example retrieves those rows that meet any of the two conditions in the WHERE clause. The following example returns the employee information whose occupation is writer and salary is greater than 35000. Here we use the OR operator to combine the conditions in the WHERE clause.
SELECT name, occupation, working_hours, salary
FROM employee_info
WHERE working_hours>10 AND salary<50000
ORDER BY name;Executing the statement will return all those employees that meet one of these conditions included in the query.
Example4: This example will retrieve those rows that belong to the range of two values. The following statement finds the employee information whose salaries are between 35000 and 60000. Here we use the BETWEEN operator to get the desired result.
SELECT name, occupation, working_hours, salary
FROM employee_info
WHERE salary BETWEEN 35000 AND 60000
ORDER BY name;Executing the statement will return all those employees whose salary is in the range of 35000 to 60000.
Example5: This example retrieves the records that have a value in a list of values. The following statement will use the IN operator to find employee information whose occupation is in Writer, Manager, or HR.
SELECT name, occupation, working_hours, salary
FROM employee_info
WHERE occupation IN ('HR', 'Writer', 'Manager')
ORDER BY name;Executing the query will return all employee information whose occupation is Writer, Manager, or HR:
Example6: This example retrieves the records whose values contain a string. The following statement will use the LIKE operator to find an employee’s information whose occupation contains the ‘er’ string.
SELECT name, occupation, working_hours, salary
FROM employee_info
WHERE occupation LIKE '%er%'
ORDER BY name;Executing the query will return all employee information whose occupation contains the ‘er’ string:
Example7: This example retrieves the records whose values do not satisfy the condition. The following statement will use the NOT EQUAL operator to find an employee’s information whose occupation does not contain the ‘Writer’ occupation.
SELECT name, occupation, working_hours, salary
FROM employee_info
WHERE occupation <> 'Writer'
ORDER BY name;Executing the query will return all employee information whose occupation does not have the ‘Writer’ occupation:
This article will explain all the information related to the WHERE clause, such as the introduction of the WHERE clause, its need in SQL Server, and how to use it to filter records based on single or multiple conditions.
Leave a Reply