WHERE in SQL

🔍 The WHERE clause is used to filter records in an SQL query. It returns only the rows that satisfy a specified condition, allowing you to retrieve exactly the data you need instead of all records in a table. The WHERE clause is commonly used with SELECT, UPDATE, DELETE, and other SQL statements.

📖 What is the WHERE Clause?

The WHERE clause specifies one or more conditions that rows must meet before they are included in the result. If a row satisfies the condition, it is returned; otherwise, it is ignored.

Information

Without a WHERE clause, SQL statements such as SELECT, UPDATE, and DELETE affect all rows in the table.

📝 Basic Syntax

General Syntax

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

📊 Sample Table

Assume the following Students table:

StudentIDNameDepartmentMarksCity
101AliceComputer Science92Chennai
102BobMathematics85Coimbatore
103CharliePhysics88Madurai
104DavidComputer Science95Chennai

đŸŽ¯ Filtering Records

Retrieve students who belong to the Computer Science department.

Filter by Department

SELECT *
FROM Students
WHERE Department = 'Computer Science';

⚖ī¸ Using Comparison Operators

The WHERE clause commonly uses comparison operators to compare values.

OperatorDescriptionExample
=Equal to Marks = 90
<> or !=Not equal to Department <> 'Physics'
>Greater than Marks > 80
<Less than Marks < 50
>=Greater than or equal to Marks >= 90
<=Less than or equal to Marks <= 60

Using a Comparison Operator

SELECT Name, Marks
FROM Students
WHERE Marks >= 90;

🔗 Using Logical Operators

Logical operators allow you to combine multiple conditions.

1ī¸âƒŖ AND

Using AND

SELECT *
FROM Students
WHERE Department = 'Computer Science'
AND Marks > 90;

2ī¸âƒŖ OR

Using OR

SELECT *
FROM Students
WHERE Department = 'Physics'
OR Department = 'Mathematics';

3ī¸âƒŖ NOT

Using NOT

SELECT *
FROM Students
WHERE NOT City = 'Chennai';

📌 Using Special Operators

1ī¸âƒŖ BETWEEN

Retrieves values within a specified range (inclusive).

Using BETWEEN

SELECT *
FROM Students
WHERE Marks BETWEEN 85 AND 95;

2ī¸âƒŖ IN

Checks whether a value matches any value in a list.

Using IN

SELECT *
FROM Students
WHERE Department IN ('Computer Science', 'Physics');

3ī¸âƒŖ LIKE

Searches for values using wildcard patterns.

Using LIKE

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

Tip

% matches zero or more characters, while _ matches exactly one character.

4ī¸âƒŖ IS NULL

Checks whether a column contains a NULL value.

Using IS NULL

SELECT *
FROM Students
WHERE City IS NULL;

✏ī¸ WHERE with UPDATE

The WHERE clause ensures that only the intended rows are updated.

Update Specific Records

UPDATE Students
SET Department = 'Information Technology'
WHERE StudentID = 101;

🗑ī¸ WHERE with DELETE

The WHERE clause ensures that only matching rows are deleted.

Delete Specific Records

DELETE FROM Students
WHERE StudentID = 104;

Warning

Omitting the WHERE clause in an UPDATE or DELETE statement affects every row in the table.

📊 Common WHERE Operators

OperatorPurpose
=Checks equality.
<> / !=Checks inequality.
>, <, >=, <=Compare numeric or date values.
BETWEENChecks whether a value falls within a range.
INMatches one of several values.
LIKEPerforms pattern matching.
IS NULLChecks for missing values.

đŸ’ŧ Real-World Example

Imagine a school management system where an administrator wants to display only students who scored more than 90 marks in the Computer Science department.

Real-World Query

SELECT StudentID,
       Name,
       Marks
FROM Students
WHERE Department = 'Computer Science'
  AND Marks > 90
ORDER BY Marks DESC;

⚠ī¸ Best Practices

Best Practice

Always use a WHERE clause when updating or deleting specific records. Use indexed columns for filtering whenever possible to improve query performance, combine conditions carefully with logical operators, and use parentheses to make complex conditions easier to read and maintain.

🚀 Key Points to Remember

  • 📌 WHERE filters rows based on specified conditions.
  • 📌 It can be used with SELECT, UPDATE, and DELETE.
  • 📌 Comparison, logical, and special operators make filtering more powerful.
  • 📌 Use IS NULL to check for missing values.
  • 📌 Omitting WHERE in UPDATE or DELETE affects all rows.
>>"The WHERE clause helps you retrieve and modify only the data you intend to work with."

Summary

✅ The WHERE clause is one of the most important features of SQL. It enables precise filtering of records using comparison, logical, and special operators, making queries more accurate, efficient, and reliable.