How to Use WHERE Clause in SQL

How to Use WHERE Clause in SQL

6 mins read2.5K Views Comment
clickHere
Vikram
Vikram Singh
Assistant Manager - Content
Updated on Jan 15, 2024 16:44 IST

The SQL WHERE clause is an optional clause that can be used with a SELECT statement. Its purpose is to filter out specific records from a dataset or table. In this article, we will be exploring how to use the WHERE clause with a set of examples.

2022_07_MicrosoftTeams-image-224.jpg

Table of Content

What is the Where Clause?

The WHERE clause filters out the records or specifies a condition while extracting the records from a single table or joining multiple tables. It follows the SELECT and FROM clause.

The conditions in the WHERE clause must be evaluated to TRUE for a row to be returned as a result.

Note: 

  1. WHERE clause is an optional clause to use with the SELECT clause, i.e., a SELECT clause can have an optional WHERE clause.
  2. WHERE clause is used with UPDATEDELETE, or SELECT a particular data set.

Must Read: How to Create, Insert, and Delete SQL views?

Must Read: SQL LIMITS

Syntax


 
SELECT column names
FROM table name
WHERE conditions;
Copy code

Now let’s have some examples to better understand the WHERE clause.

For example, we will use an Employee Dataset that contains Employee ID, Name, Gender, Department, Education, Month of Joining, and Salary (CTC).

Employee ID Name Gender Department Education Month of Joining CTC(in Lacs)
1001 Ajay M Engineering Doctoral January 25
1002 Babloo M Engineering UG February 23
1003 Chhavi F HR PG March 15
1004 Dheeraj M HR UG January 12
1005 Evina F Marketing UG March 16
1006 Fredy M Sales UG December 10
1007 Garima F Sales PG March 10
1008 Hans M Admin PG November 8
1009 Ivanka F Admin Intermediate April 7
1010 Jai M Peon   December 4
Employee Dataset

Example 1: Find the complete details of the employee named Fredy.

Query


 
SELECT *
FROM Employee
WHERE Name = ‘Fredy’;
Copy code

Output

Employee ID Name Gender Department Education Month of Joining CTC(in Lacs)
1006 Fredy M Sales UG December 10

Must Read: Introduction to SQL

Must Read: What is the difference between SQL and MySQL?

Example 2: Select all the Employee ID, Name, and Department who are female using the employee table.

Query


 
SELECT Employee ID, Name, Department
FROM Employee
WHERE Gender = ‘F’;
Copy code

Output

Employee ID Name Department
1003 Chhavi HR
1005 Evina Marketing
1007 Garima Sales 
1009 Ivanka Admin

Must Read: Types of Keys in Database

Must Read: Introduction to Normalization

WHERE with the LIKE operator

Example 3: Find the Details of all the employees where the department is ending with ing.

Query


 
SELECT *
FROM Employee
WHERE Department LIKE%ing’;
Copy code

Output

Employee ID Name Gender Department Education CTC(in Lacs)
1001 Ajay M Engineering Doctoral 25
1002 Babloo M Engineering UG 23
1005 Evina F Marketing UG 16

Must Read: SQL Joins

Must Read: Window Function in SQL

WHERE clause with Multiple Conditions

Example 4: Find the records of the employees who are working in marketing and are undergraduates.

Query


 
SELECT *
FROM Employee
WHERE department = ‘Marketing’ AND Education = ‘UG’;
Copy code

Output

Employee ID Name Gender Department Education CTC(in Lacs)
1005 Evina F Marketing UG 16

Must Read: Difference between SQL and NoSQL

Must Check: SQL Online Course and Certifications

WHERE clause with Subquery

Example 5: Find the Employee ID, Name, department, and CTC where CTC is greater than or equal to the average CTC.

Query


 
SELECT Employee ID, Name, Department, CTC
FROM Employee
WHERE CTC >= (SELECT AVG (CTC) FROM Employee);
Copy code

Output

Employee ID Name Department CTC(in Lacs)
1001 Ajay Engineering 25
1002 Babloo Engineering 23
1003 Chhavi HR 15
1005 Evina Marketing 16

Must Read: Subqueries in SQL

Must Read: Aggregate and Scalar Function in SQL

Operators in the WHERE clause

Here is the list of operators that can be used with the WHERE clause.

Operator Description
= Equal
> Greater than
< Less than
>= Greater than or Equal
<= Less than or Equal
< > Not equal
BETWEEN Between a certain range
LIKE Search for a pattern
IN To specify multiple possible values in a particular column

Limitation of Where Clause

It can’t be used with an aggregate function directly. Because where clauses are evaluated row-by-row, where an aggregate function works on multiple rows to return a single result. 

Let’s take an example to check this:

Example 6: Without using the sub-query, find the employee ID, name, department, and CTC where CTC is greater than AVG CTC.

Query


 
SELECT Employee ID, Name, Department, CTC
FROM Employee
WHERE CTC > AVG (CTC);
Copy code

The above query will throw an Error.

Conclusion

The WHERE clause in SQL is a powerful tool that filters data in database queries. It specifies the conditions for rows to be included in the result set. The syntax is simple yet versatile, allowing for various conditions. We explored different types of operators like comparison, logical, and range operators. The WHERE clause has limitations, like its inability to filter aggregated data. Mastering the WHERE clause is vital for anyone working with SQL to retrieve required data precisely and efficiently.

Hope you will like the article.
Keep Learning!!

FAQs on How to Use WHERE Clause in SQL?

How do I use comparison operators in a WHERE clause?

You can use operators like =, !=, <, <=, >, >= to compare column values. For instance, SELECT * FROM salespeople WHERE salary > 50000; fetches all salespeople with a salary greater than 50000.

Can I use the WHERE clause with string values?

Yes, comparison operators work with strings, but they are ordered and compared alphabetically. Remember to use quotation marks around string literals in conditions.

What is the BETWEEN operator, and how do I use it?

The BETWEEN operator is used to filter the result set within a certain range. It is inclusive, meaning it includes the boundary values. For example, SELECT * FROM table WHERE age BETWEEN 25 AND 30; retrieves records where age is between 25 and 30, including both 25 and 30.

How can I combine multiple conditions in a WHERE clause?

Use the AND, OR operators to combine multiple conditions. For instance, SELECT * FROM employees WHERE department = 'Sales' AND salary > 50000; gets all sales employees with salaries above 50000.

Can I use the WHERE clause with the IN operator?

Yes, the IN operator allows you to specify a list of values to compare in the WHERE clause. For example, SELECT * FROM employees WHERE department IN ('Sales', 'HR'); fetches employees in either Sales or HR departments.

What is the EXISTS operator, and how is it used?

EXISTS is used to test for the existence of any record in a subquery. It’s useful for checking if rows in a table match rows from another table based on certain criteria. For example, to check if there are any sales in a specific region, you might use: SELECT * FROM sales WHERE EXISTS (SELECT * FROM regions WHERE sales.region_id = regions.id);.

How do I handle NULL values in a WHERE clause?

Use IS NULL or IS NOT NULL to filter records with or without NULL values in a specific column. For instance, SELECT * FROM employees WHERE commission IS NULL; retrieves all employees who do not have a commission value.

Can the WHERE clause be used with Joins?

Yes, you can filter records based on conditions that involve multiple tables when using JOINs. For instance, SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id WHERE customers.country = 'USA'; retrieves orders from U.S. customers.

How do I use the LIKE operator in a WHERE clause?

LIKE is used for pattern matching in string columns. For example, SELECT * FROM products WHERE name LIKE 'Apple%'; fetches products whose names start with "Apple".

About the Author
author-image
Vikram Singh
Assistant Manager - Content

Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio

Comments