SQL Server WHERE Clause

This SQL tutorial provides an introduction to the SQL Server WHERE clause with explanations, examples and exercises. For this lesson’s exercises, use this link.

This tutorial is a part of several posts describing how to use the WHERE clause in SQL Server. To read additional posts regarding this subject, please use the following links:

• Introduction – SQL Server WHERE clause
• Comparison operators
• IN operator
• BETWEEN operator
• LIKE operator
• IS NULL operator
• AND & OR operators

Introduction – SQL Server WHERE Clause

The SQL Server WHERE Clause is used to restrict the rows returned from a query. While the previous chapter (Basic SELECT Statements) explained how to extract the names of all employees from Employees table, using the SQL Server WHERE clause, you are able to restrict the query to rows that meet a condition. For example: extract the employees whose salary is higher than 5000, or the employees who work at the HR department, and so on.

• A SQL Server WHERE clause must be written after a SQL Server FROM clause (which in turn must be written after a SQL Server SELECT clause), this order cannot be changed.
• Unlike the SQL Server SELECT and FROM statements, which are necessary for creating a valid SQL query, the SQL Server WHERE clause is optional. An SQL query can function properly with or without a SQL Server WHERE clause.

SQL Server WHERE Clause Syntax

A SQL Server WHERE clause has the following structure:

WHERE  column_name operator value

SELECT *
FROM employees
WHERE salary = 3000

• Column_name – the name of the column. The query above retrieves all employees whose salary equals 3000, and therefore the Salary column was selected. If you would like to display all employees whose city of residence equals London, you need to select the City column.

SELECT *
FROM employees
WHERE city = 'London'

• Operator – there are two types of operators:
  • Comparison operators– represented by simple operators such as = , < , > , => , =<, <>
  • Logical operators– represented by operators such as IN, BETWEEN, LIKE.

• Comparison value – 
• Number – for example, a salary is greater than a certain number.

SELECT *
FROM employees
WHERE salary > 6000

• Date – for example, the transaction date is greater than a certain date.

SELECT *
FROM transactions
WHERE transaction_date > '2013-01-13'

• String – for example, a last name equals a certain last name.

SELECT *
FROM employees
WHERE last_name = 'Ram'

General Guidelines

• The value data type must match the column date type  (it makes no sense to try to display the employees whose first name is greater than 4503).

SELECT *
FROM employees
WHERE first_name > 6000
-- (error)

•  If the requested comparison does not match the table values (for example, the highest salary in the table is 12,000 and you would like to display the employees whose salary is greater than 200,000), no result will be retrieved but no error will be generated either (the table could not retrieve values that, based on its settings, do not exist).

SELECT *
FROM employees
WHERE salary > 200000
-- (valid SQL Server SELECT statement)
-- As described no error will be generated and no rows will be retrieved either