WHERE Clause
The WHERE clause is used to filter records based on a specified condition.
When we want to retrieve only specific records from a table instead of all records, we use the WHERE clause.
What is WHERE Clause?
The WHERE clause specifies a condition that must be satisfied by the records returned by a SQL query.
It is commonly used with the SELECT, UPDATE and DELETE statements.
Syntax
SELECT column_name
FROM table_name
WHERE condition;
Example Table
| StudentId | StudentName | Age | City | Fees |
|---|---|---|---|---|
| 101 | Rahul | 22 | Pune | 35000 |
| 102 | Priya | 23 | Mumbai | 40000 |
| 103 | Amit | 21 | Nashik | 30000 |
| 104 | Neha | 24 | Pune | 45000 |
WHERE with Equal (=)
The equal operator is used when we want records having an exact value.
SELECT *
FROM Students
WHERE City = 'Pune';
This query returns students whose city is Pune.
WHERE with Greater Than (>)
SELECT *
FROM Students
WHERE Age > 22;
This returns students whose age is greater than 22.
WHERE with Less Than (<)
SELECT *
FROM Students
WHERE Age < 22;
This returns students whose age is less than 22.
Greater Than or Equal To (>=)
SELECT *
FROM Students
WHERE Age >= 22;
Less Than or Equal To (<=)
SELECT *
FROM Students
WHERE Fees <= 35000;
Not Equal To (<>)
The <> operator returns records where the specified value is different.
SELECT *
FROM Students
WHERE City <> 'Pune';
WHERE with AND
AND is used when all specified conditions must be true.
SELECT *
FROM Students
WHERE Age > 21
AND City = 'Pune';
Both conditions must be satisfied.
WHERE with OR
OR is used when at least one of the specified conditions should be true.
SELECT *
FROM Students
WHERE City = 'Pune'
OR City = 'Mumbai';
Multiple Conditions
Multiple conditions can be combined using AND and OR.
SELECT *
FROM Students
WHERE Age > 21
AND Fees > 30000
AND City = 'Pune';
WHERE with UPDATE
The WHERE clause can be used with UPDATE to modify only selected records.
UPDATE Students
SET Fees = 40000
WHERE StudentId = 101;
WHERE with DELETE
The WHERE clause can be used with DELETE to remove only selected records.
DELETE FROM Students
WHERE StudentId = 104;
Important
- WHERE filters records based on a condition.
- WHERE can be used with SELECT, UPDATE and DELETE.
- AND requires all conditions to be true.
- OR requires at least one condition to be true.
- Always check the WHERE condition before UPDATE or DELETE operations.
Summary
In this section, we learned how the WHERE clause is used to filter records in SQL Server. We covered comparison operators, AND, OR, and the use of WHERE with SELECT, UPDATE and DELETE statements.