SQL WHERE Clause

The WHERE clause in SQL is used to filter records in a database query. It allows you to specify a condition so that only the rows that meet that condition will be retrieved, updated, or deleted.

Without a WHERE clause, SQL commands work on all rows in the table, which may not always be desired.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Key Points:

• WHERE works with SELECT, UPDATE, and DELETE statements.
• The condition can use operators like =, >, <, >=, <=, <>, LIKE, IN, and BETWEEN.
• The condition can also combine multiple criteria using AND, OR, and NOT.

---

Why Use the WHERE Clause?

Imagine you have a table with thousands of rows. If you want to find only specific data, you need a way to filter results.
For example:

• Find all students with marks greater than 80.
• Get a list of employees from the “Sales” department.
• Delete only the orders older than 1 year.

The WHERE clause makes this possible.

---

Example Table:

Let’s use an example table named Employees:

ID	Name	Department	Salary	City
1	Riya	Sales	50000	Delhi
2	Aarav	IT	60000	Mumbai
3	Meera	HR	45000	Chennai
4	Aditya	Sales	55000	Delhi
5	Priya	IT	70000	Bangalore

---

1. WHERE with SELECT Statement

Used to retrieve only the matching rows.

Example:

SELECT * 
FROM Employees
WHERE Department = 'Sales';

Result:

ID	Name	Department	Salary	City
1	Riya	Sales	50000	Delhi
4	Aditya	Sales	55000	Delhi

---

2. WHERE with Comparison Operators

Operator	Meaning
=	Equal to
<> or !=	Not equal to
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal

Example:

SELECT Name, Salary
FROM Employees
WHERE Salary > 55000;

Result:

Name	Salary
Aarav	60000
Priya	70000

---

3. WHERE with AND, OR, and NOT

• AND: All conditions must be true.
• OR: At least one condition must be true.
• NOT: Negates a condition.

Example (AND):

SELECT * 
FROM Employees
WHERE Department = 'IT' AND Salary > 65000;

Result:

ID	Name	Department	Salary	City
5	Priya	IT	70000	Bangalore

Example (OR):

SELECT * 
FROM Employees
WHERE City = 'Delhi' OR City = 'Mumbai';

Example (NOT):

SELECT *
FROM Employees
WHERE NOT Department = 'Sales';


---

4. WHERE with LIKE for Pattern Matching

Used to search for a specified pattern in a column.

Example:

SELECT * 
FROM Employees
WHERE Name LIKE 'A%';

Matches names starting with ‘A’. (% means any number of characters)

---

5. WHERE with IN

Matches any value in a list.

Example:

SELECT * 
FROM Employees
WHERE City IN ('Delhi', 'Chennai');

---

6. WHERE with BETWEEN

Filters results within a range (inclusive).

Example:

SELECT * 
FROM Employees
WHERE Salary BETWEEN 50000 AND 60000;

---

7. WHERE in UPDATE Statement

Updates only matching rows.

UPDATE Employees
SET Salary = 52000
WHERE Name = 'Riya';

---

8. WHERE in DELETE Statement

Deletes only matching rows.

DELETE FROM Employees
WHERE Department = 'HR';

---

Best Practices for WHERE Clause

• Always use WHERE when updating or deleting data to avoid accidental changes to all rows.
• Use indexes on columns in the WHERE clause for faster queries.
• Be careful with case sensitivity depending on the database system.
• Combine conditions smartly with AND, OR for precise filtering.

---

Conclusion

The SQL WHERE clause is an essential tool for filtering data in database operations. Whether you are retrieving, updating, or deleting records, WHERE helps you target only the rows that meet specific conditions. This makes your queries efficient, accurate, and safe from accidental changes.