SQL WHERE Clause

Video Tutorial
FREE
 WHERE, Comparison operators, NULL thumbnail
This video belongs to
DBMS Course - Master the Fundamentals and Advanced Concepts
16 modules
Certificate

Overview

Let us assume that you are navigating through a voluminous database and you wish to display only those records that adhere to certain conditions. Not only is carrying out this search increasingly tedious, but it is also time-consuming.

To aid your searches, the WHERE clause is used to explicitly filter out only those records that meet the conditions specified by you. This saves time, effort, and complexity.

Introduction to WHERE Clause in SQL

Querying databases to retrieve information is challenging in itself. Using the WHERE clause makes specified data aggregation a lot easier. Fetching these relational data items, the WHERE Clause in SQL is used. This is responsible for selecting only those data items that correspond to the mentioned conditions.

This is done to specifically filter out particular columns.

Syntax

Operators in the WHERE Clause

Operators in WHERE clause help you to specify the various conditions that you would like to query from a database.

Here is a list of popular operators that are used in WHERE to select desired columns for a record.

OperatorDescription
=Equals
>Greater than
<Less than
>=Greater than or equal
<=Less than or equal
<> or !=Not equal
BETWEENBetween a certain specified range
LIKESearching for a pattern
INTo specify multiple possible values for a column

Examples of WHERE Clause Operators

Let us assume that we have a sample table EMPLOYEES containing the information of employees working in a firm distributed across India.

Example of WHERE Operators

We shall now be using the WHERE clause to carry out operations on these and query certain records.

1. Where Clause with the EQUAL Operator

This shall be used to display only those records which have values equal to the values specified.

This code is used to fetch the ID and NAME values for those employees wherein the age of the employee is equal to 32. From the table, you can see that employees Seema Kapoor and Raj Singh meet the given criterion.

Therefore the output is: Equal Operator

2. Where Clause with the LESS THAN and GREATER THAN Operators

These shall be used to display only those records which have values lesser than or greater than the values specified respectively.

This code is used to fetch the ID and NAME values for those employees wherein the salaries of the employees are less than Rs. 20,000. From the table, you can see that employees Seema Kapoor and Abhi Kumar meet the given criterion.

Therefore the output is: Less than Operators

Similarly, if we alter the code accordingly to select only those employees with salaries larger than Rs. 20,000, the new code shall be:

Then, the output must be like this Greater than Operators

3. Where Clause with the Less Than Equal To and Greater Than Equal To Operators

Similarly, we can specify the equal to sign with either the less than or the greater than operator.

For example:

This code would display all the records with salaries greater than or equal to Rs 25,000.

The output is: Greater than Equal to Operators

Similarly, if we tweak this code slightly to display only those records with salaries lesser than or equal to Rs. 25,000, the code would resemble this:

The output for this would be: Less than Equal to Operators

4. Where Clause with the BETWEEN Operator

This operator is used when you intend to display records with values between two specified points.

The output for this code is only the ID and NAME values for employee Abhi Kumar.

BETWEEN Operator

5. Where Clause with the LIKE Operator

The LIKE Operator is used to find patterns in the tables and find correlations. For example, if you intend to view the IDs and NAMES of those employees who stay in Chennai, you can write the following code.

The output for the code is:

LIKE Operator

6. Where Clause with the IN Operator

The IN operator is used to query those records that have certain specific characteristics. For example, an alternative method for printing the ID and NAME values for employees residing in Chennai could be by using the IN operator.

The output for this would be: IN Operator

7. Where Clause with the NOT EQUAL TO Operator

Sometimes, we will have to select those records which don't adhere to certain conditions. The Not Equal to operators are used to fetch records not complying with the conditions.

Let us assume that you have to display the NAME and ID values for those employees who are not 32 years of age.

Query:

The output for this code would be: Not Equal To Operator

Conclusion

  1. The WHERE Clause is used to fetch records from tables that adhere to certain conditions.
  2. A number of operators can be used to specify the conditionals.
  3. Operators include equals, less than, greater than, greater than equal to, less than equal to, not equal to, BETWEEN, IN and LIKE.
  4. WHERE can act as a conditional filter, or to find matching patterns.

See More