WHERE Clause in SQL

quiz
Challenge Inside! : Find out where you stand! Try quiz, solve problems & win rewards!

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.

Scope

  1. In this article, we shall be discussing the use-cases of the WHERE clause.
  2. We shall deal with the various operators of the WHERE clause followed by exemplar code snippets and outputs.

Introduction to the SQL WHERE Clause

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 of SQL WHERE Clause

SELECT column_name
FROM table_name
WHERE condition;

Operators in The WHERE Clause

Operators in WHERE clause helps 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 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. The Equal Operator

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

SELECT ID, NAME 
FROM EMPLOYEES 
WHERE AGE=32;

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. 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.

SELECT ID, NAME 
FROM EMPLOYEES 
WHERE SALARY<20000;

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:

SELECT ID, NAME 
FROM EMPLOYEES 
WHERE SALARY>20000;

Then, the output must be like this

Greater than Operators

3. 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:

SELECT ID, NAME 
FROM EMPLOYEES 
WHERE SALARY>=25000;

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:

SELECT ID, NAME 
FROM EMPLOYEES 
WHERE SALARY<=25000;

The output for this would be:

Less than Equal to Operators

4. BETWEEN Operator

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

SELECT ID, NAME 
FROM EMPLOYEES 
WHERE SALARY BETWEEN 11000 and 20000;

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

BETWEEN Operator

5. 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.

SELECT ID, NAME 
FROM EMPLOYEES 
WHERE ADDRESS LIKE '%Chennai';

The output for the code is:

LIKE Operator

6. 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.

SELECT ID, NAME 
FROM EMPLOYEES 
WHERE ADDRESS IN ('Chennai, TN');

The output for this would be:

IN Operator

7. Not Equal To

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:

SELECT ID, NAME
FROM EMPLOYEES
WHERE AGE <> 32;

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.
Free Courses by top Scaler instructors
certificate icon
Certificates
SQL Tutorial
This program includes modules that cover the basics to advance constructs of SQL Tutorial. The highly interactive and curated modules are designed to help you become a master of this language.'
If you’re a learning enthusiast, this is for you.
Module Certificate
Criteria
Upon successful completion of all the modules in the hub, you will be eligible for a certificate.
You need to sign in, in the beginning, to track your progress and get your certificate.