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.
- What are clauses and its need?
- What are the different types of clauses supported by SQL Server Database?
- Understanding the Where clause in SQL Server.
- Understanding where clause with Single and Multiple conditions using AND, OR Conditions.
- 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
- Where (Filtering the records in a table)
- Order by clause (sorting the records in ascending or descending order)
- Top n clause (Fetching top n records)
- Group by clause (Grouping a set of rows)
- 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
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 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 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';
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;
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;
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);
How to use 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 some simple examples. I hope you enjoy this article.