SQL Server: WHERE Clause

In this post explains how to use the WHERE clause in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) WHERE clause is used to filter the results from a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the WHERE clause in SQL Server (Transact-SQL) is:

WHERE conditions;

Parameters or Arguments

conditions

The conditions that must be met for records to be selected.

Example - With Single condition

It is difficult to explain the syntax for the SQL Server WHERE clause, so let's look at some examples.

We'll start by looking at how to use the WHERE clause with only a single condition.

For example:

SELECT *
FROM employees
WHERE first_name = 'Jane';

In this SQL Server WHERE clause example, we've used the WHERE clause to filter our results from the employees table. The SELECT statement above would return all rows from the employees table where the first_name is 'Jane'. Because the * is used in the SELECT, all fields from the employees table would appear in the result set.

Example - Using AND condition

Let's look at how to use the WHERE clause with the AND condition.

For example:

SELECT *
FROM employees
WHERE last_name = 'Mark'
AND employee_id >= 3000;

This SQL Server WHERE clause example uses the WHERE clause to define multiple conditions. In this case, this SELECT statement uses the AND condition to return all employees that have a last_name of 'Mark' and the employee_id is greater than or equal to 3000.

Example - Using OR condition

Let's look at how to use the WHERE clause with the OR condition.

For example:

SELECT employee_id, last_name, first_name
FROM employees
WHERE last_name = 'Johnson'
OR first_name = 'Danielle';

This SQL Server WHERE clause example uses the WHERE clause to define multiple conditions, but instead of using the AND condition, it uses the OR condition. In this case, this SELECT statement would return all employee_id, last_name, and first_name values from the employees table where the last_name is 'Johnson' or the first_name is 'Danielle'.

Example - Combining AND & OR conditions

Let's look at how to use the WHERE clause when we combine the AND & OR conditions in a single SQL statement.

For example:

SELECT *
FROM employees
WHERE (state = 'California' AND last_name = 'Smith')
OR (employee_id = 82);

This SQL Server WHERE clause example uses the WHERE clause to define multiple conditions, but it combines the AND condition and the OR condition. This example would return all employees that reside in the state of 'California' and whose last_name is 'Smith' as well as all employees whose employee_id is equal to 82.

The parentheses determine the order that the AND and OR conditions are evaluated. Just like you learned in the order of operations in Math class!

Example - Joining Tables

Let's look at how to use the WHERE clause when we join multiple tables together.

For example:

SELECT employees.employee_id, contacts.last_name
FROM employees
INNER JOIN contacts
ON employees.employee_id = contacts.contact_id
WHERE employees.first_name = 'Julie';

This SQL Server WHERE clause example uses the WHERE clause to join multiple tables together in a single SELECT statement. This SELECT statement would return all rows where the first_name in the employees table is 'Julie'. And the employees and contacts tables are joined on the employee_id from the employees table and the contact_id from the contacts table.