Using the WHERE SQL clause

WHERE clause is used to specify/apply any condition while retrieving, updating or deleting data from a table. This clause is used mostly with SELECT, UPDATE and DELETEquery.

When we specify a condition using the WHERE clause then the query executes only for those records for which the condition specified by the WHERE clause is true.


Syntax for WHERE clause

Here is how you can use the WHERE clause with a DELETE statement, or any other statement,

DELETE FROM table_name WHERE [condition];

The WHERE clause is used at the end of any SQL query, to specify a condition for execution.


Time for an Example

Consider a table student,

s_idnameageaddress
101Adam15Chennai
102Alex18Delhi
103Abhi17Banglore
104Ankit22Mumbai

Now we will use the SELECT statement to display data of the table, based on a condition, which we will add to our SELECT query using WHERE clause.

Let's write a simple SQL query to display the record for student with s_id as 101.

SELECT s_id, 
    name, 
    age, 
    address 
    FROM student WHERE s_id = 101;

Following will be the result of the above query.

s_idnameageaddress
101Adam15Noida

Applying condition on Text Fields

In the above example we have applied a condition to an integer value field, but what if we want to apply the condition on name field. In that case we must enclose the value in single quote ' '. Some databases even accept double quotes, but single quotes is accepted by all.

SELECT s_id, 
    name, 
    age, 
    address 
    FROM student WHERE name = 'Adam';

Following will be the result of the above query.

s_idnameageaddress
101Adam15Noida

Operators for WHERE clause condition

Following is a list of operators that can be used while specifying the WHERE clause condition.

OperatorDescription
=Equal to
!=Not Equal to
<Less than
>Greater than
<=Less than or Equal to
>=Greate than or Equal to
BETWEENBetween a specified range of values
LIKEThis is used to search for a pattern in value.
INIn a given set of values