.jpeg)
The WHERE clause in SQL is used to filter records from a table based on a specified condition. It allows us to retrieve only the rows that match the condition instead of returning all records. For example, if a Students table contains hundreds of records, we can use the WHERE clause to find students from a particular city, students with a specific age, or students who scored more than 80 marks.
In this article, we will learn how to use the SQL WHERE clause with comparison operators, logical operators, pattern matching, ranges, NULL values, dates, joins, subqueries, and other advanced SQL concepts.
What Is the WHERE Clause in SQL?
The WHERE clause is used with SQL statements to specify which records should be selected, updated, or deleted. It checks each row against a condition. If the condition is true, that row is included in the result.
Syntax
The basic syntax is:
SELECT column1, column2
FROM table_name
WHERE condition;
Here:
- SELECT specifies the columns we want to retrieve.
- FROM specifies the table from which the data is retrieved.
- WHERE specifies the condition used to filter the records.
- condition defines which rows should be returned.
Example
Suppose we have the following Students table:
| id | name | age | city |
| 1 | Rahul | 20 | Delhi |
| 2 | Priya | 22 | Noida |
| 3 | Amit | 21 | Delhi |
| 4 | Neha | 23 | Mumbai |
To find students who live in Delhi, we can use:
SELECT *
FROM Students
WHERE city = 'Delhi';
Output
| id | name | age | city |
| 1 | Rahul | 20 | Delhi |
| 3 | Amit | 21 | Delhi |
Comparison Operator
Comparison operators are used with the WHERE clause to compare a column value with a specific value. They help filter records based on conditions such as equal to, not equal to, greater than, or less than.
| Operator | Meaning |
|---|---|
| = | Equal to |
| <> | Not equal to |
| != | Not equal to |
| > | Greater than |
| < | Less than |
| >= | Greater than or equal to |
| <= | Less than or equal to |
Using Equal To (=)
The = operator checks whether two values are equal.
Consider the following table:
| id | name | age | city |
| 1 | Rahul | 20 | Delhi |
| 2 | Priya | 22 | Noida |
| 3 | Amit | 21 | Delhi |
| 4 | Neha | 23 | Mumbai |
Query:
SELECT *
FROM Students
WHERE age = 21;
Output
| id | name | age | city |
|---|---|---|---|
| 3 | Amit | 21 | Delhi |
The query returns the student whose age is exactly 21.
Using Not Equal To (<>)
The <> operator returns rows where the value is different from the specified value.
Using the same Students table:
| id | name | age | city |
|---|---|---|---|
| 1 | Rahul | 20 | Delhi |
| 2 | Priya | 22 | Noida |
| 3 | Amit | 21 | Delhi |
| 4 | Neha | 23 | Mumbai |
Query:
SELECT *
FROM Students
WHERE city <> 'Delhi';
Output
| id | name | age | city |
| 2 | Priya | 22 | Noida |
| 4 | Neha | 23 | Mumbai |
The query excludes students who live in Delhi.
The != operator can also be used for not equal in many SQL database systems:
SELECT *
FROM Students
WHERE city != 'Delhi';
It produces the same result in systems that support !=.
Using Greater Than (>)
The > operator is used to find values greater than a specified value.
Students table:
| id | name | age | city |
| 1 | Rahul | 20 | Delhi |
| 2 | Priya | 22 | Noida |
| 3 | Amit | 21 | Delhi |
| 4 | Neha | 23 | Mumbai |
Query:
SELECT *
FROM Students
WHERE age > 21;
Output
| id | name | age | city |
|---|---|---|---|
| 2 | Priya | 22 | Noida |
| 4 | Neha | 23 | Mumbai |
The query returns students whose age is greater than 21.
Using Less Than (<)
The < operator finds values smaller than a specified value.
Students table:
| id | name | age | city |
| 1 | Rahul | 20 | Delhi |
| 2 | Priya | 22 | Noida |
| 3 | Amit | 21 | Delhi |
| 4 | Neha | 23 | Mumbai |
Query:
SELECT *
FROM Students
WHERE age < 22;
Output
| id | name | age | city |
| 1 | Rahul | 20 | Delhi |
| 3 | Amit | 21 | Delhi |
Using Greater Than or Equal To (>=)
The >= operator returns values that are greater than or equal to the specified value.
Students table:
| id | name | age | city |
| 1 | Rahul | 20 | Delhi |
| 2 | Priya | 22 | Noida |
| 3 | Amit | 21 | Delhi |
| 4 | Neha | 23 | Mumbai |
Query:
SELECT *
FROM Students
WHERE age >= 21;
Output
| id | name | age | city |
| 2 | Priya | 22 | Noida |
| 3 | Amit | 21 | Delhi |
| 4 | Neha | 23 | Mumbai |
The query returns students who are 21 or older.
Using Less Than or Equal To (<=)
The <= operator returns values that are less than or equal to the specified value.
Students table:
| id | name | age | city |
| 1 | Rahul | 20 | Delhi |
| 2 | Priya | 22 | Noida |
| 3 | Amit | 21 | Delhi |
| 4 | Neha | 23 | Mumbai |
Query:
SELECT *
FROM Students
WHERE age <= 21;
Output
| id | name | age | city |
|---|---|---|---|
| 1 | Rahul | 20 | Delhi |
| 3 | Amit | 21 | Delhi |
The query returns students whose age is 21 or younger.
WHERE Clause with AND Operator
The AND operator is used when all specified conditions must be true. Suppose we want to find students who are older than 20 and live in Delhi.
Students table:
| id | name | age | city |
| 1 | Rahul | 20 | Delhi |
| 2 | Priya | 22 | Noida |
| 3 | Amit | 21 | Delhi |
| 4 | Neha | 23 | Mumbai |
Query:
SELECT *
FROM Students
WHERE age > 20 AND city = 'Delhi';
Output
| id | name | age | city |
| 3 | Amit | 21 | Delhi |
Both conditions must be true. Rahul lives in Delhi, but his age is not greater than 20, so he is not included.
WHERE Clause with OR Operator
The OR operator returns a row when at least one of the conditions is true.
Students table:
| id | name | age | city |
| 1 | Rahul | 20 | Delhi |
| 2 | Priya | 22 | Noida |
| 3 | Amit | 21 | Delhi |
| 4 | Neha | 23 | Mumbai |
Query:
SELECT *
FROM Students
WHERE city = 'Delhi' OR city = 'Noida';
Output
| id | name | age | city |
| 1 | Rahul | 20 | Delhi |
| 2 | Priya | 22 | Noida |
| 3 | Amit | 21 | Delhi |
The query returns students who live in either Delhi or Noida. WHERE Clause with NOT Operator The NOT operator reverses a condition.
Students table:
| id | name | age | city |
| 1 | Rahul | 20 | Delhi |
| 2 | Priya | 22 | Noida |
| 3 | Amit | 21 | Delhi |
| 4 | Neha | 23 | Mumbai |
Query:
SELECT *
FROM Students
WHERE NOT city = 'Delhi';
Output
| id | name | age | city |
| 2 | Priya | 22 | Noida |
| 4 | Neha | 23 | Mumbai |
The same condition can also be written as:
SELECT *
FROM Students
WHERE city <> 'Delhi';
Both queries exclude students from Delhi.
WHERE Clause with IN Operator
The IN operator is useful when we want to check a column against multiple possible values.
Instead of writing:
SELECT *
FROM Students
WHERE city = 'Delhi'
OR city = 'Noida'
OR city = 'Mumbai';
we can use:
SELECT *
FROM Students
WHERE city IN ('Delhi', 'Noida', 'Mumbai');
This makes the query shorter and easier to read.
Example
Students table:
| id | name | age | city |
| 1 | Rahul | 20 | Delhi |
| 2 | Priya | 22 | Noida |
| 3 | Amit | 21 | Delhi |
| 4 | Neha | 23 | Mumbai |
Query:
SELECT *
FROM Students
WHERE city IN ('Delhi', 'Noida');
Output
| id | name | age | city |
| 1 | Rahul | 20 | Delhi |
| 2 | Priya | 22 | Noida |
| 3 | Amit | 21 | Delhi |
The query returns students from Delhi or Noida.
WHERE Clause with NOT IN Operator
The NOT IN operator is used to exclude multiple values.
Students table:
| id | name | age | city |
| 1 | Rahul | 20 | Delhi |
| 2 | Priya | 22 | Noida |
| 3 | Amit | 21 | Delhi |
| 4 | Neha | 23 | Mumbai |
Query:
SELECT *
FROM Students
WHERE city NOT IN ('Delhi', 'Noida');
Output
| id | name | age | city |
| 4 | Neha | 23 | Mumbai |
The query excludes Delhi and Noida and returns the remaining records.
WHERE Clause with BETWEEN Operator
The BETWEEN operator is used to filter values within a specified range.
Students table:
| id | name | age | city |
| 1 | Rahul | 20 | Delhi |
| 2 | Priya | 22 | Noida |
| 3 | Amit | 21 | Delhi |
| 4 | Neha | 23 | Mumbai |
Query:
SELECT *
FROM Students
WHERE age BETWEEN 20 AND 22;
Output
| id | name | age | city |
| 1 | Rahul | 20 | Delhi |
| 2 | Priya | 22 | Noida |
| 3 | Amit | 21 | Delhi |
WHERE Clause with NOT BETWEEN
NOT BETWEEN is used when we want to exclude a range.
Employees table:
| employee_id | name | department | salary |
| 101 | Raj | IT | 28000 |
| 102 | Priya | HR | 35000 |
| 103 | Amit | IT | 45000 |
| 104 | Neha | Sales | 55000 |
Query:
SELECT *
FROM Employees
WHERE salary NOT BETWEEN 30000 AND 50000;
Output
| employee_id | name | department | salary |
| 101 | Raj | IT | 28000 |
| 104 | Neha | Sales | 55000 |
The query returns employees whose salary is outside the specified range.
WHERE Clause with LIKE Operator
The LIKE operator is used for pattern matching. It is useful when we want to find text values that follow a particular pattern.
Students table:
| id | name | age | city |
| 1 | Rahul | 20 | Delhi |
| 2 | Priya | 22 | Noida |
| 3 | Amit | 21 | Delhi |
| 4 | Neha | 23 | Mumbai |
| 5 | Anil | 20 | Lucknow |
Query:
SELECT *
FROM Students
WHERE name LIKE 'A%';
Output
| id | name | age | city |
| 3 | Amit | 21 | Delhi |
| 5 | Anil | 20 | Lucknow |
Here, % represents zero or more characters.
Common SQL LIKE Wildcards
SQL LIKE wildcards are special characters used with the LIKE operator to search for a specific pattern in text values. They allow us to find records when we know only part of the value.
| Wildcard | Meaning | Example |
| % | It matches zero, one, or multiple characters | LIKE 'A%' |
| _ | It matches exactly one character | LIKE 'A_' |
Students table:
| id | name | age | city |
| 1 | Rahul | 20 | Delhi |
| 2 | Priya | 22 | Noida |
| 3 | Amit | 21 | Delhi |
| 4 | Neha | 23 | Mumbai |
| 5 | Anil | 20 | Lucknow |
Query:
SELECT *
FROM Students
WHERE name LIKE 'A%';
Output
| id | name | age | city |
| 3 | Amit | 21 | Delhi |
| 5 | Anil | 20 | Lucknow |
This finds names that start with A.
Name Ending with a Specific Letter
Students table:
| id | name | age | city |
| 1 | Rahul | 20 | Delhi |
| 2 | Priya | 22 | Noida |
| 3 | Amit | 21 | Delhi |
| 4 | Neha | 23 | Mumbai |
| 5 | Anil | 20 | Lucknow |
Query:
SELECT *
FROM Students
WHERE name LIKE '%a';
Output
| id | name | age | city |
| 2 | Priya | 22 | Noida |
| 4 | Neha | 23 | Mumbai |
This finds names ending with a. Name Containing Specific Text
Students table:
| id | name | age | city |
| 1 | Rahul | 20 | Delhi |
| 2 | Priya | 22 | Noida |
| 3 | Amit | 21 | Delhi |
| 4 | Neha | 23 | Mumbai |
| 5 | Anil | 20 | Lucknow |
Query:
SELECT *
FROM Students
WHERE name LIKE '%it%';
Output
| id | name | age | city |
| 3 | Amit | 21 | Delhi |
This finds names containing it.
Matching One Character with _
The _ wildcard represents exactly one character.
Students table:
| id | name | age | city |
| 1 | Amit | 21 | Delhi |
| 2 | Anit | 22 | Noida |
| 3 | Anil | 20 | Lucknow |
| 4 | Rahul | 23 | Mumbai |
Query:
SELECT *
FROM Students
WHERE name LIKE 'A_it';
Output
| id | name | age | city |
| 1 | Amit | 21 | Delhi |
| 2 | Anit | 22 | Noida |
The pattern A_it means the name starts with A, has exactly one character in the middle, and ends with it.
WHERE Clause with NULL Values
NULL represents a missing or unknown value in SQL. Suppose we have an Employees table:
| employee_id | name | department | salary |
| 101 | Raj | IT | 40000 |
| 102 | Priya | HR | NULL |
| 103 | Amit | IT | 50000 |
| 104 | Neha | Sales | NULL |
We should not use = to check whether a value is NULL. We use the following commands
SELECT *
FROM Employees
WHERE salary IS NULL;
Output
| employee_id | name | department | salary |
| 102 | Priya | HR | NULL |
| 104 | Neha | Sales | NULL |
The query returns rows where salary does not contain a value.
Checking for NOT NULL
To find rows where a column contains a value, use IS NOT NULL.
Employees table:
| employee_id | name | department | salary |
| 101 | Raj | IT | 40000 |
| 102 | Priya | HR | NULL |
| 103 | Amit | IT | 50000 |
| 104 | Neha | Sales | NULL |
Query:
SELECT *
FROM Employees
WHERE salary IS NOT NULL;
Output
| employee_id | name | department | salary |
| 101 | Raj | IT | 40000 |
| 103 | Amit | IT | 50000 |
WHERE Clause with Dates
The WHERE clause can also be used to filter records based on dates.
Suppose we have an Orders table:
| order_id | customer | order_date | amount |
| 1 | Rahul | 2026-01-15 | 2500 |
| 2 | Priya | 2026-03-10 | 4000 |
| 3 | Amit | 2026-06-20 | 3500 |
| 4 | Neha | 2026-10-01 | 5000 |
To find orders placed on October 1, 2026:
SELECT *
FROM Orders
WHERE order_date = '2026-10-01';
Output
| order_id | customer | order_date | amount |
| 4 | Neha | 2026-10-01 | 5000 |
Filtering Dates with Greater Than
SELECT *
FROM Orders
WHERE order_date > '2026-01-01';
Output
| order_id | customer | order_date | amount |
| 2 | Priya | 2026-03-10 | 4000 |
| 3 | Amit | 2026-06-20 | 3500 |
| 4 | Neha | 2026-10-01 | 5000 |
This returns orders placed after January 1, 2026.
Filtering a Date Range
SELECT *
FROM Orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-03-31';
Output
| order_id | customer | order_date | amount |
| 1 | Rahul | 2026-01-15 | 2500 |
| 2 | Priya | 2026-03-10 | 4000 |
When working with date and time columns, the exact behavior can depend on the database system and data type, so date-range queries should be written carefully.
WHERE Clause with Multiple Conditions
We can combine multiple conditions in a single WHERE clause. Suppose we have:
| employee_id | name | department | salary | city |
| 101 | Raj | IT | 45000 | Delhi |
| 102 | Priya | HR | 55000 | Noida |
| 103 | Amit | IT | 65000 | Delhi |
| 104 | Neha | Sales | 70000 | Mumbai |
Query:
SELECT *
FROM Employees
WHERE department = 'IT'
AND salary > 50000
AND city = 'Delhi';
Output
| employee_id | name | department | salary | city |
| 103 | Amit | IT | 65000 | Delhi |
The query returns employees who work in the IT department, earn more than 50000, and live in Delhi.
Conclusion
The SQL WHERE clause is one of the most important parts of SQL because it allows us to filter data based on specific conditions. It can be used with SELECT, UPDATE, and DELETE statements and works with comparison operators, logical operators, IN, BETWEEN, LIKE, NULL checks, subqueries, joins, and other SQL features.