The objective of this SQL Server tutorial is to teach you how to use the WHERE clause in SQL to filter the resultset and retain only required records.
What is WHERE clause in SQL Server?
The WHERE clause restricts the scope of operation of a SQL query on the basis of a condition specified with it. By default, the scope of operation of a SQL query is the entire table. For e.g. An update query without a WHERE condition updates on or more columns in all records of the table. Similarly, a delete query without WHERE condition will delete all records from the table. This is a high-risk situation which can have disastrous consequences. To avoid this almost every SQL query is followed by a WHERE condition which restricts the scope of operation of the query to only those records which match the WHERE condition.
The condition specified with the WHERE clause can be one or more logical expressions which are also called predicates. The logical expression might be a literal (numeric or string) value, table column, parameter, variable, subquery, mathematical operation or function that returns a unique numeric or string value. It can use any of the available operators (arithmetic, comparison and logical) to evaluate the condition. The list of different operators is as below.
Comparison operators – = (equal to). != and <> (not equal to). > (less than), < (greater than), >= (greater than equal to), <= (less than equal to).
Logical operators – LIKE, BETWEEN, IN, NOT, EXISTS, ANY, ALL, AND, OR.
Operation
The WHERE clause can be used with SELECT, INSERT, UPDATE and DELETE to restrict the resultset to display or act upon. Multiple WHERE conditions can be stringed together using the AND or OR operators so that the resultset returned satisfies a set of conditions.
SQL Server WHERE Syntax
The basic syntax of SQL Server WHERE clause is as follows.
Query WHERE condition;
or
Query WHERE condition1 AND | OR condition2 AND | OR condition3………………;
In this syntax,
Query – the main query which can be a SELECT, INSERT, UPDATE or DELETE query.
WHERE condition(s) – This is used to specify some conditions while selecting data. In case you are not using WHERE clause all the rows available will be selected.
OR – logical operator used to specify one or more conditions with the where clause such that at least one of them must be satisfied.
AND –logical operator used to specify one or more conditions with the where clause such that all conditions must be satisfied.
SQL Server WHERE Clause examples
Let us consider a few examples to understand the practical usage of the WHERE clause in the different query types and with different operators. Suppose we have 2 tables called employees and divisions. The employees table holds the company employee information and the divisions table holds the information of the different divisions in the IT department of the company. We will use these sample tables as the reference tables in the examples that follow.
id
name
head
301
IT Support
Claudia Schiffer
302
IS Audit
Norman Bates
Table: divisions
id
name
sex
age
doj
salary
div
201
Alicia Wells
F
23
9/1/2017
27000
301
202
Patrick Christ
M
24
9/9/2017
28000
301
203
Patricia Smith
F
29
11/11/2018
55000
302
204
Lauren Smith
F
25
3/23/2019
26000
301
205
Richard Anderson
M
25
3/13/2019
26000
301
206
David Mathews
M
27
4/24/2019
28000
301
207
Jeff Gossard
M
31
1/12/2017
66000
302
208
Melissa Mckagan
F
33
12/12/2018
65000
302
209
Jack Crow
M
31
1/10/2018
58000
302
210
Samuel Knight
M
29
7/14/2018
58000
302
Table: employees
1) SQL Server WHERE – SELECT query example
Let us see a few examples of WHERE in a SELECT query to filter and fetch the desired resultset as per requirement. The following query uses a single WHERE condition to retrieve the list of employees who joined the company after January 2018 (2018-01-01).
SELECT * FROM employees WHERE doj > '2018-01-01';
The above query will generate the following output.
id
name
sex
age
doj
salary
div
203
Patricia Smith
F
29
11/11/2018
55000
302
204
Lauren Smith
F
25
3/23/2019
26000
301
205
Richard Anderson
M
25
3/13/2019
26000
301
206
David Mathews
M
27
4/24/2019
28000
301
208
Melissa Mckagan
F
33
12/12/2018
65000
302
209
Jack Crow
M
31
1/10/2018
58000
302
210
Samuel Knight
M
29
7/14/2018
58000
302
The following query uses two conditions with the where clause to fetch the list of female employees who have “Smith” in their name.
SELECT * FROM employees WHERE sex='F' AND name like '%Smith%';
The above query will generate the following output.
id
name
sex
age
doj
salary
div
203
Patricia Smith
F
29
11/11/2018
55000
302
204
Lauren Smith
F
25
3/23/2019
26000
301
2) SQL Server WHERE – SELECT with JOIN example
The WHERE clause can also be used in a SELECT query which fetches records from two or more related tables with the help of a JOIN. The below query does the same. It fetches the employee details of female employees who have a salary of $ 50000.00 or more. The letters e and d in the query are aliases for the tables employees and divisions respectively and division is the column name alias d.name (i.e. division name from divisions table).
SELECT e.id, e.name, d.name division, e.salary
FROM employees e
INNER JOIN divisions d ON e.div=d.id
WHERE e.sex='F' AND e.salary>=50000.00;
The above query will generate the following output.
id
name
division
salary
203
Patricia Smith
IS Audit
55000
208
Melissa Mckagan
IS Audit
65000
3) SQL Server WHERE – UPDATE query example
Suppose we want to update the salary of employees after an appraisal. The following query does so for employees whose current salary is less than or equal to $ 30000.00.
UPDATE employees
SET salary=30000
WHERE salary<=30000;
We can check the update by running the following SELECT query which will show the updated salary figure.
SELECT * FROM employees ORDER BY salary;
id
name
sex
age
doj
salary
div
204
Lauren Smith
F
25
3/23/2019
30000
301
205
Richard Anderson
M
25
3/13/2019
30000
301
206
David Mathews
M
27
4/24/2019
30000
301
201
Alicia Wells
F
23
9/1/2017
30000
301
202
Patrick Christ
M
24
9/9/2017
30000
301
203
Patricia Smith
F
29
11/11/2018
55000
302
209
Jack Crow
M
31
1/10/2018
58000
302
4) SQL Server WHERE – INSERT query example
Suppose the IS Audit function has been outsourced and its employees have been transferred to the service provider. We create a table called vendor_employees to maintain the IS audit employee data separately with the following create query.
CREATE TABLE contract_employees
(id TINYINT,
Name VARCHAR(30),
sex CHAR(1),
age TINYINT,
doj DATE,
salary DECIMAL(10,2),
div INT);
After table creation, we need to populate it with the employee data. We do the same with the following WHERE condition in the INSERT query. this will insert all records from the employees table into the vendor_employees table for all employees of the IS Audit division i.e. employees with div 302.
INSERT INTO contract_employees
SELECT * FROM employees
WHERE div=302;
After the insert we can check by running a SELECT query on the newly created vendor_employees table and it will show all the records.
id
name
sex
age
doj
salary
div
203
Patricia Smith
F
29
11/11/2018
55000
302
207
Jeff Gossard
M
31
1/12/2017
66000
302
208
Melissa Mckagan
F
33
12/12/2018
65000
302
209
Jack Crow
M
31
1/10/2018
58000
302
210
Samuel Knight
M
29
7/14/2018
58000
302
5) SQL Server WHERE – DELETE query example
After copying the employee data into the new table we will delete the duplicate data from the employees table with a WHERE condition in the following DELETE query which will delete all employee records with div 302 (i.e. the IS Audit employees).
DELETE FROM employees
WHERE div=302;
We can check the result by running a select query on the employees table which will only show records of employees with div 301 (i.e. IT Support).