Home » SQL Server WHERE Clause

SQL Server WHERE Clause

by Online Tutorials Library

SQL Server WHERE Clause

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:

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:

SQL Server WHERE Clause

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:

Executing the statement will return only those employees whose working hour is equal to 12:

SQL Server WHERE Clause

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.

Executing the statement will return all those employees whose working hour is greater than 10 and salary is less than 50000:

SQL Server WHERE Clause

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.

Executing the statement will return all those employees that meet one of these conditions included in the query.

SQL Server WHERE Clause

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.

Executing the statement will return all those employees whose salary is in the range of 35000 to 60000.

SQL Server WHERE Clause

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.

Executing the query will return all employee information whose occupation is Writer, Manager, or HR:

SQL Server WHERE Clause

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.

Executing the query will return all employee information whose occupation contains the ‘er’ string:

SQL Server WHERE Clause

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.

Executing the query will return all employee information whose occupation does not have the ‘Writer’ occupation:

SQL Server WHERE Clause

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.


You may also like