SQL Server WHERE

Learning Objective

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.

  • Arithmetic operators – + (addition), – (subtraction), * (multiplication), / (division), % (remainder).
  • 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.

idnamehead
301IT SupportClaudia Schiffer
302IS AuditNorman Bates
Table: divisions
idnamesexagedojsalarydiv
201Alicia WellsF239/1/201727000301
202Patrick ChristM249/9/201728000301
203Patricia SmithF2911/11/201855000302
204Lauren SmithF253/23/201926000301
205Richard AndersonM253/13/201926000301
206David MathewsM274/24/201928000301
207Jeff GossardM311/12/201766000302
208Melissa MckaganF3312/12/201865000302
209Jack CrowM311/10/201858000302
210Samuel KnightM297/14/201858000302
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.

idnamesexagedojsalarydiv
203Patricia SmithF2911/11/201855000302
204Lauren SmithF253/23/201926000301
205Richard AndersonM253/13/201926000301
206David MathewsM274/24/201928000301
208Melissa MckaganF3312/12/201865000302
209Jack CrowM311/10/201858000302
210Samuel KnightM297/14/201858000302

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.

idnamesexagedojsalarydiv
203Patricia SmithF2911/11/201855000302
204Lauren SmithF253/23/201926000301

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.

idnamedivisionsalary
203Patricia SmithIS Audit55000
208Melissa MckaganIS Audit65000

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;
idnamesexagedojsalarydiv
204Lauren SmithF253/23/201930000301
205Richard AndersonM253/13/201930000301
206David MathewsM274/24/201930000301
201Alicia WellsF239/1/201730000301
202Patrick ChristM249/9/201730000301
203Patricia SmithF2911/11/201855000302
209Jack CrowM311/10/201858000302

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.

idnamesexagedojsalarydiv
203Patricia SmithF2911/11/201855000302
207Jeff GossardM311/12/201766000302
208Melissa MckaganF3312/12/201865000302
209Jack CrowM311/10/201858000302
210Samuel KnightM297/14/201858000302

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).

idnamesexagedojsalarydiv
201Alicia WellsF239/1/201730000301
202Patrick ChristM249/9/201730000301
204Lauren SmithF253/23/201930000301
205Richard AndersonM253/13/201930000301
206David MathewsM274/24/201930000301

Advertisement