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 :

Examples

Consider a students table having different columns and values mentioned below:

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

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

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

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:

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