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 in SQL Server.
4. Understanding where clause with Single and Multiple conditions using AND, OR Conditions.
5. How to use the 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 SQL Server Clauses along with the SQL Query. So, in simple words, we can say that SQL Server clauses are used to provide some additional functionalities.

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.

Example to Understand Where Clause in SQL Server:

We are going to use the following Gender, Department, and Employee tables to understand the WHERE clause in SQL Server with Examples.

Please use the below script to create the Gender, Department, and Employee tables with the required sample 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 Employee 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 data to Gender table
INSERT INTO Gender VALUES('Male')
INSERT INTO Gender VALUES('Female')
INSERT INTO Gender VALUES('Unknown')
GO

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

--Insert data into Employee 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

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

Note: The WHERE clause is not only used with the SELECT statement, but can also be used with the INSERT, UPDATE, or DELETE DML statements.

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 example, we used the WHERE clause to filter the results from the employee table. The below Select SQL 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 table.
SELECT * FROM Employee WHERE CITY = ‘MUMBAI’;
Once you execute the above query, you will get the following output. Please have a look at the City column and notice that the above query returns the employees whose City is MUMBAI.

Example of Where Clause Using AND Condition in SQL Server:

Let’s look at how to use the WHERE clause with the AND condition in SQL Server. In the below example, we use the WHERE 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;
When you execute the above SQL query, you will get the following output. Notice we only got the employees who satisfied the WHERE condition that is GenderId is 1 and Salary is greater than or equals to 27000.

Example of Where Clause using OR Condition in SQL Server:

Let’s look at how to use the WHERE clause with the OR condition in SQL Server. The below example uses the WHERE 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. That means if any of the condition is satisfied then also the data is going to return.
SELECT ID, Name, EmailID, CITY FROM Employee WHERE GenderID = 1 OR Salary >= 29000;
When you execute the above query, you will get the following output. Notice we only got the employees who satisfied the WHERE condition that is GenderId is 1 or Salary is greater than or equals to 29000.

Example of Where Clause using both AND & OR Conditions in SQL Server:

Let’s look at how to use the WHERE 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);
When you execute the above query, you will get the following output.

How to use the WHERE clause with Update Statement in SQL Server?

Let’s look at how to use the WHERE clause when we are working with the UPDATE SQL statement. In the below 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
Once you execute the above query, verify the Employee table by executing the below query and you will see that the Salary is updated to 37000 for all the Employees belonging to the DepartmentId 3.
SELECT * FROM Employee WHERE DepartmentId = 3;
Once you execute the above query you will get the following output and notice the salary is updated to 37000 as expected.

How to use the Where clause with Delete Statement in SQL Server?

Let’s look at how to use the WHERE clause when we are working with the DELETE SQL statement. In the following example, we use the Where clause to DELETE all the Employees where the CITY is MUMBAI.
DELETE FROM Employee WHERE CITY = ‘MUMBAI’
Once you execute the above query, verify the Employee table and you will see that all the Employees belonging to the CITY MUMBAI are deleted.

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 in SQL Server with Examples. I hope you enjoy this article.