SQL WHERE Clause

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:

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 condition limits the faster execution of SQL code as the number of records returned. SQL Query optimizer works first on the query using (table on which operation needs to be performed) to 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 the 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 the HAVING clause instead of WHERE because the 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 –

1. Types of Joins in SQL
2. Wildcards in MySQL
3. SQL Insert Query
4. Foreign Key in SQL