Back to: SQL Server Tutorial For Beginners and Professionals

Where Clause in SQL Server

In this article, I am going to discuss the Where Clause in SQL Server with examples. But before understanding the  Where Clause in SQL Server, let us first understand what is a clause in SQL Server and why do we need clauses in SQL Server.

If you want to provide the SQL query with some additional functionalities such as filtering the records, sorting the records, fetching the records and grouping the records then you need to use the Clause with the query.

The SQL Server supports the following clauses

1. Where (Filtering the records in a table)
2. Order by clause (sorting the records in ascending or descending order)
3. Top n clause (Fetching top n records)
4. Group by clause (Grouping a set of rows)
5. Having Clause (Filtering the data like where clause)

In this article, we are going to discuss the Where Clause in SQL Server, The rest of the clauses are going to discuss in our upcoming articles.

Use below script to create the necessary tables and populate with some test data.

--Create Gender table
CREATE TABLE Gender
(
  ID INT PRIMARY KEY IDENTITY(1,1),
  Gender VARCHAR(50)
)
GO

--Create Department table
CREATE TABLE Department
(
  ID INT PRIMARY KEY IDENTITY(1,1),
  Name VARCHAR(100)
)

-- Create Person table
CREATE TABLE Employee
(
  ID INT PRIMARY KEY IDENTITY(1,1),
  Name VARCHAR(100),
  EmailID VARCHAR(100),
  GenderID INT,
  DepartmentID INT,
  Salary INT,
  Age INT,
  CITY VARCHAR(100)
)
GO

--Add the foreign key for GenderID Column
ALTER TABLE Employee 
ADD CONSTRAINT Employee_GenderID_FK FOREIGN KEY (GenderID) 
REFERENCES Gender(ID)
GO

--Add foreign key for DepartmentID Column
ALTER TABLE Employee 
ADD CONSTRAINT Employee_DepartmentID_FK FOREIGN KEY (DepartmentID) 
REFERENCES Department(ID)
GO

--Insert some test data to Gender table
INSERT INTO Gender VALUES('Male')
INSERT INTO Gender VALUES('Female')
INSERT INTO Gender VALUES('Unknown')
GO

--Insert some test data to Gender table
INSERT INTO Department VALUES('IT')
INSERT INTO Department VALUES('HR')
INSERT INTO Department VALUES('Payroll')
GO

--Insert some test data into Person table
INSERT INTO Employee VALUES('PRANAYA','PRANAYA@G.COM',1, 1, 25000, 30,'MUMBAI')
INSERT INTO Employee VALUES('TARUN','TARUN@G.COM',1, 2, 30000, 27,'ODISHA')
INSERT INTO Employee VALUES('PRIYANKA','PRIYANKA@G.COM',2, 3, 27000, 25,'BANGALORE')
INSERT INTO Employee VALUES('PREETY','PREETY@G.COM',2, 3, 35000, 26,'BANGALORE')
INSERT INTO Employee VALUES('RAMESH','RAMESH@G.COM',3,2, 26000, 27,'MUMBAI')
INSERT INTO Employee VALUES('PRAMOD','PRAMOD@G.COM',1, 1, 29000, 28,'ODISHA')
INSERT INTO Employee VALUES('ANURAG','ANURAG@G.COM',1, 3, 27000, 26,'ODISHA')
INSERT INTO Employee VALUES('HINA','HINA@G.COM',2,2, 26000, 30,'MUMBAI')
INSERT INTO Employee VALUES('SAMBIT','HINA@G.COM',1, 1, 30000, 25,'ODISHA')
GO

Fetch all the records from Employee table

SELECT * FROM Employee

The Where Clause in SQL Server:

The SQL Server WHERE clause is not a mandatory clause of SQL DML statements, but if you want to limit the number of rows to be affected by your DML query or number rows to return from your select statement, then you need to use the Where Clause in SQL Server. That means this clause is used to extract only those results from a SQL statement (such as SELECT, INSERT, UPDATE, or DELETE statement) that fulfill a specified condition.

Syntax: WHERE conditions;

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

Example – With a Single condition

We will start by looking at how to use this clause with only a single condition.

In the below SQL Server WHERE clause example, we used this clause to filter the results from the employee table. The below SELECT statement will return all the rows from the employee table where the CITY is ‘MUMBAI’. Because the * is used in the SELECT statement which will return all the columns from the employee.

SELECT *
FROM Employee
WHERE CITY = 'MUMBAI';

Example – Using AND condition

Let’s look at how to use this clause with the AND condition.

In the below SQL Server WHERE clause example, we use this clause to define multiple conditions. In this case, the SELECT statement uses the AND condition to return all the employees from the Employee table whose GenderID is 1 and the Salary is greater than or equal to 27000.

SELECT *
FROM Employee
WHERE GenderID = 1
AND Salary >= 27000;

Example – Using OR condition

Let’s look at how to use this clause with the OR condition.

The below SQL Server WHERE clause example uses this clause to define multiple conditions, but instead of using the AND condition, it uses the OR condition. In this case, the SELECT statement will return all ID, Name, EmailID, and CITY column values from the Employee table where the GenderID is 1 or the Salary is greater than 29000.

SELECT ID, Name, EmailID, CITY
FROM Employee
WHERE GenderID = 1
OR Salary >= 29000;

Example – Combining AND & OR Conditions

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

The below SQL Server example uses the WHERE clause to define multiple conditions, but it combines the AND condition and the OR condition. The below example will return all employees that reside in the state of MUMBAI and whose GenderID is 1 as well as all employees whose DepartmentID is 3.

SELECT *
FROM Employee
WHERE (CITY = 'MUMBAI' AND GenderID = 1)
OR (DepartmentID = 3);

Example – Where clause with the Update Statement

Let’s look at how to use this clause when we are working with the UPDATE SQL statement.

In the below SQL Server example, we use the Where clause to update all the Employee Salary to 37000 where the DepartmentID is 3.

UPDATE Employee
SET Salary = 37000 
WHERE DepartmentID = 3

Example – Where clause with the Delete Statement

Let’s look at how to use this clause when we are working with the DELETE SQL statement.

In the below SQL Server example, we use the Where clause to DELETE all the Employee where the CITY is ‘MUMBAI’.

DELETE FROM Employee
WHERE CITY = 'MUMBAI'

In the next article, I will discuss Order by clause in SQL Server with examples.

SUMMARY

In this article, I try to explain Where Clause in SQL Server steps by step with some simple examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.