SQL Server - WHERE Clause

In SQL Server, the SELECT statement can have an optional WHERE clause to filter the data. The WHERE clause can include one or more boolean conditions to filter out data of the tables.

The WHERE clause always comes after the FROM clause and before GROUP BY, HAVING, and ORDER BY clauses.

Syntax:

SELECT column1, column2,...columnN 
FROM table_name
WHERE boolean_expression;

For the demo purpose, we will use the following Employee and Department tables in all examples.

+ operator in select query

The WHERE clause can contain one or more conditions that can use conditional operators to filter out the result data. Consider the following query.

SQL Server: SELECT Query with WHERE Clause
SELECT * FROM Employee 
WHERE Salary > 25000;

In the above query, the condition Salary > 25000 returns rows where the value in the Salary column is greater than 25000. The above query will return the following result.

+ operator in select query

The following query uses the BETWEEN operator in the WHERE clause.

SQL Server: BETWEEN Operator in WHERE Clause
SELECT * FROM Employee 
WHERE Salary BETWEEN 17000 AND 25000;

In the above query, the condition Salary BETWEEN 17000 AND 25000 returns rows where the value in the Salary column is between 17000 and 25000 (including both values). The above query will get the following records.

+ operator in select query

Multiple Conditions in WHERE Clause

The WHERE clause can contain multiple conditions using AND and OR operators. The following query uses logical operator AND to specify two conditions to filter out data.

SQL Server: Multiple Conditions in WHERE Clause
SELECT * FROM Employee 
WHERE DeptId = 1 AND Salary > 20000;

In the above query, the WHERE condition DeptId = 1 AND Salary > 20000 specifies two conditions separated by AND operator. This will return rows from the Employee table where the value of DeptId is 1 and Salary is greater than 20000.

+ operator in select query

Subquery in WHERE Clause

The WHERE condition can also use the resulted value of a subquery, as shown below.

SQL Server: Subquery in WHERE Clause
SELECT * FROM Employee 
WHERE DeptId = (SELECT DeptId FROM Department WHERE DeptName = 'HR');

In the above query, the WHERE condition is WHERE DeptId = (SELECT DeptId FROM Department WHERE DeptName = 'HR'). So, first, the subquery SELECT DeptId FROM Department WHERE DeptName = 'HR' would be executed, and the resulted DeptId value will be used to filter out the rows.

+ operator in select query

Conditional Operators

The following operators can be used in the WHERE conditions.

Operator Description
= Equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
<> or != Not equal. In some databases, the != is used to compare values which are not equal.
BETWEEN Between some range
LIKE Search for a pattern
IN To specify multiple possible values for a column