How to Use WHERE Clause in SQL
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.
Table of Content
Best-suited Database and SQL courses for you
Learn Database and SQL with these high-rated online courses
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:
- WHERE clause is an optional clause to use with the SELECT clause, i.e., a SELECT clause can have an optional WHERE clause.
- WHERE clause is used with UPDATE, DELETE, or SELECT a particular data set.
Must Read: How to Create, Insert, and Delete SQL views?
Must Read: SQL LIMITS
Syntax
SELECT column namesFROM table nameWHERE conditions;
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 |
Example 1: Find the complete details of the employee named Fredy.
Query
SELECT *FROM EmployeeWHERE Name = ‘Fredy’;
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, DepartmentFROM EmployeeWHERE Gender = ‘F’;
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 EmployeeWHERE Department LIKE ‘%ing’;
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 EmployeeWHERE department = ‘Marketing’ AND Education = ‘UG’;
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, CTCFROM EmployeeWHERE CTC >= (SELECT AVG (CTC) FROM Employee);
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, CTCFROM EmployeeWHERE CTC > AVG (CTC);
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".
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
Top Picks & New Arrivals
