Introduction to WHERE Clause
As we all know that SQL(Structured Query Language) is one of the most commonly used languages for relational databases (database in which records are saved in the form of rows and columns). In SQL queries are fired to hit the database to perform desired operations be it DML (Data Manipulation Language), DDL (Data Definition Language) or DCL (Data Control Language) operations. SQL uses some clauses like WHERE, GROUP BY, HAVING, ORDER BY which perform specific operations. The WHERE clause is used to apply conditions and filter out results while retrieving or manipulating any data from the database. It is used with the SELECT, UPDATE and DELETE statement also the WHERE clause is optional to be used with them.
In general, terms, WHERE clause,
- Used to filter the rows according to the given criteria.
- Limits the number of rows returned.
- Followed by a logical condition that returns either true or false.
- Works only if the mentioned condition returns true.
- It can be used with SELECT, UPDATE or DELETE statements.
1. Syntax with SELECT
SELECT column1, column2, column3… from table_name WHERE condition;
Here SELECT will retrieve all the data of column1, column2, column3 from the table (named as table_name) and the WHERE clause applies the conditions to the data retrieved by SELECT statement and filter that according to the condition mentioned in the statement.
2. Syntax with UPDATE
UPDATE table_name SET column_name = value WHERE condition;
Here Update will update the value for the column_name with provided where condition.
Comparison and Logical Operators can also be used with the WHERE condition like and, or, not, LIKE, <, =, etc.
3. Syntax with DELETE
DELETE from table_name WHERE condition;
In the above Syntax :
| table_name | Name of the table on which operations need to be performed. |
| condition | Specifies the filter condition on which records needs to be filtered |
| column_list | Name of the columns of the table |
Examples
Consider a students table having different columns and values mentioned below:
| Stu_id | Stu_name | Stu_address | Stu_phno | Stu_percentage |
| 1 | Rahul | Agra | 9557806625 | 85 |
| 2 | Ankit | Delhi | 8855664471 | 75 |
| 3 | Shailendra | Noida | 7213457896 | 92 |
Scenario #1
Retrieve student id, student name, address, and percentage of all those students who scored more than 80 percent.
Query
SELECT Stu_id, Stu_name, Stu_address from students WHERE Stu_percentage > 80;
Result:
Number of Records: 2
| Stu_id | Stu_name | Stu_address | Stu_percentage |
| 1 | Rahul | Agra | 85 |
| 3 | Shailendra | Noida | 92 |
Scenario #2
Update the percentage of Rahul by 2 percent.
Query
UPDATE students SET Stu_percentage = Stu_percentage+2 WHERE Stu_name =‘Rahul’;
Result:
Rows affected: 1
If we hit the query to view the updated fields:
4.5 (3,177 ratings)
View Course
Query
SELECT * from students WHERE Stu_name =’Rahul’;
Result:
Number of Records: 1
| Stu_id | Stu_name | Stu_address | Stu_percentage |
| 1 | Rahul | Agra | 87 |
Scenario #3
Student Ankit has left the school so delete the whole record of him from the table.
Query
DELETE from students WHERE Stu_name = ’Ankit’;
Result:
Rows affected: 1
To view the updated table student:
Query
SELECT * from students;
Result:
Rows affected: 2
| Stu_id | Stu_name | Stu_address | Stu_phno | Stu_percenta ge |
| 1 | Rahul | Agra | 9557806625 | 87 |
| 3 | Shailendra | Noida | 7213457896 | 92 |
WHERE clause Operations
The WHERE clause holds the conditions on which database values are filtered. Various operators can be used with the WHERE clause. Some of them are given below in the table with an example:
| S.No. | Operator | Description | Example |
| 1. | AND | Returns true if both the condition matches | SELECT * from students WHERE Stu_name =’Rahul’ and Stu_percentage = 85; |
| 2. | OR | Returns true if either of the
condition matches |
SELECT * from students WHERE Stu_name =’Rahul’ or Stu_name = ‘Shalendra’; |
| 3. | IN | The value matches any of the multiple values specified | SELECT * from students WHERE Stu_city IN (‘AGRA’, ‘NOIDA’); |
| 4. | NOT IN | The value does not match any of the multiple values specified | SELECT * from students WHERE Stu_city NOT IN (‘AGRA’, ‘NOIDA’); |
| 5. | = | Equal | SELECT * from students WHERE
Stu_name=’Rahul’; |
| 6. | > | Greater than | SELECT * from students WHERE Stu_percentage > 80; |
| 7. | < | Less than | SELECT * from students WHERE Stu_percentage < 78; |
| 8. | >= | Greater than or equal to | SELECT * from students WHERE Stu_percenetage >=70; |
| 9. | <= | Less than or equal to | SELECT * from students WHERE Stu_percenetage <=70; |
| 10. | <> | Not equals to | SELECT * from students WHERE Stu_percentage <> 75; |
| 11. | BETWEEN | The value lies between a certain range | SELECT * from students WHERE
Stu_percentage BETWEEN 70 AND 85; |
| 12. | LIKE | Values match a certain pattern. Used to perform wildcard searches | SELECT * from students WHERE Stu_city LIKE ‘AG%’; |
Note: There is one thing that needs to be kept in mind while working with WHERE clause is that while specifying the condition, Numeric values are not quoted in single quotes (‘ ‘) whereas text values (varchar) needs to be quoted in single quotes (‘ ‘).
How does the WHERE Clause work in SQL?
Though the above examples clearly show how WHERE clause is used to filter the data according to the condition specified by the user and allows the faster execution of SQL code as the number of records returned is limited by the condition. SQL Query optimizer works first on the query using FROM (table on which operation needs to be performed) to either retrieve, delete or update and then apply WHERE clause to the results.
WHERE clause can only be used when we need to filter the results in a single table or join of tables as it works on rows data but when in case of the Aggregate functions, WHERE cannot be used to apply conditions to the query.
Let’s consider the scenario where WHERE clause can’t be used:
Scenario: In a ‘movies’ table, retrieve all the details of movies that earn more than 10 crores in particular years like (2000, 2010,2012..etc)
Query:
SELECT * from movies GROUP BY Year_released HAVING earnings > 10;
The above example uses HAVING clause instead of WHERE, because WHERE clause cannot be used in the aggregate functions whereas HAVING can, and this is also one of the major differences between WHERE and HAVING clause
Conclusion – SQL WHERE Clause
The above explanation clearly shows the use of the WHERE clause and its implementation for different scenarios in SQL queries. Before writing any query, one must keep in mind the use of every clause and the scenario in which that particular clause or keyword should be used.
Recommended Articles
This is a guide to SQL WHERE Clause. Here we discuss the use of the WHERE clause and its implementation for different scenarios. You can also go through our other suggested articles –
