A few example of the where clause

Following are a few examples of the where clause that are explained later:

A select – where example

Select – Where with AND operator

SQL where with delete statement

The Where Clause in SQL

A few main points about the SQL Where are:

• The Where clause is used to filter the results that meet the given criteria.
• The where clause can be used with the Select statement to fetch only the required rows.
• The Where clause is also used in the Update statement to modify data for the specific rows.
• The Where clause is also used in the Delete statement to remove only specific rows that meet the given criteria.
• The criteria or conditions are specified by using the operators e.g. =, <, >, Like, And, Or etc. which is explained below.

Syntax of the Where Clause

The general syntax of the Where Clause is:

Where col_name=value

Using in Select – Where statement:

Select * from table_name Where col_name = value

Using Where in Update statement

Update table table_name

Set col_name=value

Where col_name=value

Using Where in the SQL Delete statement

Delete from table_name

Where col_name=value

Using operators in the Where clause

You can use the operators in the Where SQL clause to specify the condition, that which rows to affect or return.

Following operators can be used in the Where clause.

1.    =: Equal to the given value.
2.    >: Grater than the given value.
3.    <: Less than the given value.
4.    != or <>: Not equal to given value.
5.    >=:  Greater than or equal to given value.
6.    <=: Less than or equal to given value.
7.    AND: Specifying multiple conditions that must be true.
8.    OR: Specifying multiple conditions, where any of condition may be true to perform the action.
9.    IN: Specifying multiple values.
10.    Between: values between an inclusive given range.
11.    Like

We already used few of the above operators in different examples of SQL tutorials.

Example of using Where clause with operators

Let us show you examples of Where clause with our created database and table. We will use same database, test_db and tbl_employee table, that we used in the Create table chapter, to show a few examples of the Where clause in Select, Update and Delete commands.

Just to remind, this is how our example table is created in test_db:

We also have saved some data in the example table, to be used in the Where clause examples.

SQL select with where example

This example shows you how to fetch only single row of data based on the emp_id column, from our example table tbl_employee. We will use Select – Where clause for that with the ‘=’ operator.

The query will be:

Demo Table of select statement with where

The Where clause with Greater than (>) operator

In the following example, we will use Where clause with the ‘>’ operator. As such, the ‘>’ operator will fetch data for greater than given value.

The Select – where query will be:

Demo Table of select with where and > operator 

SQL AND with where clause example

The SQL AND operator is used where you have more than one conditions and all conditions must be true in order to fetch data.

The statement for Select – where with SQL AND operator will be:

Demo table with select-where with AND operator

SQL OR with Where clause example

The OR operator is used where you have more than one conditions and if any condition is true it will fetch the records.

The Select query with SQL OR will be:

Demo Table after Select with Where and OR

Where clause example in Update statement

This example shows how to update only specific row data of the emp_salary column by using the Where clause.

Demo table after update with where clause

As you can see in the above graphic, all records are same except Ben’s salary column. In that case, we changed Ben’s salary column by using Ben’s emp_id column value, as this is unique in the table.

Where clause example in Delete statement with AND operator

Consider we have two employees with the same name ‘Mike’. Running the simple delete query with emp_name will remove both records in our tbl_employee table. In order to delete only one record of employee ‘Mike’ who joined 2009-01-14, this is how we can remove it by using the where clause of SQL with the delete command.

This is how delete query would look with SQL where:

Demo Table before and after delete statement

In the above query, we used SQL AND operator to add another condition inside the Where clause to differentiate records.