Where Clause in SQL Server

Back to: SQL Server Tutorial For Beginners and Professionals

Where Clause in SQL Server with Examples

In this article, I am going to discuss the Where Clause in SQL Server with examples. Please read our previous article where we discussed the Select Statement in SQL Server in detail. As part of this article, we are going to discuss the following important pointers related to clauses in SQL Server.

  1. What are clauses and its need?
  2. What are the different types of clauses supported by SQL Server Database?
  3. Understanding the Where clause
  4. Understanding where clause with Single and Multiple conditions using AND, OR Conditions.
  5. How to use Where clause with Update and Delete Statement?
What are clauses and their need 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.

Types of Clauses in SQL Server:

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)

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

Please use the below script to create the necessary tables and populate them 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 the Employee table

SELECT * FROM Employee

Understanding SQL Server Where Clause

Understanding 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 the number of 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 of Where clause with a Single condition

We will start by looking at how to use the Where 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';

Where clause with a Single condition

Example of Where Clause 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;

Where Clause Using AND Condition in SQL Server

Example of Where Clause 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;

Where Clause using OR Condition in SQL Server

Example of Where Clause using both 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 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);

Where Condition using both AND & OR in SQL Server

How to use the 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
How to use the Where clause with Delete Statement?

Let’s look at how to use this clause when we are working with the DELETE SQL statement. In the following 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 am going to discuss the Order by clause in SQL Server with examples. Here, in this article, I try to explain Where Clause step by step with Examples. I hope you enjoy this article.

Leave a Reply

Your email address will not be published. Required fields are marked *