Using the WHERE Clause for Filtering

Table of Contents

1. Introduction
2. What is the WHERE Clause?
3. Basic Syntax of WHERE
4. Filtering with Equality Conditions
5. Using !=, <>, and NOT
6. Greater Than and Less Than Comparisons
7. Filtering Text with LIKE
8. Wildcards in LIKE: % and _
9. Using IN to Match Multiple Values
10. Filtering Between Ranges with BETWEEN
11. Handling NULL Values
12. Logical Operators: AND, OR, and NOT
13. Precedence in Conditional Expressions
14. Combining Multiple Conditions
15. WHERE Clause in Numeric Fields
16. WHERE Clause in Date Fields
17. WHERE Clause in String Fields
18. Using WHERE with Subqueries
19. Common Mistakes and Troubleshooting
20. Summary and What’s Next

1. Introduction

The WHERE clause is one of the most important tools in SQL. It allows you to filter records in a table by specifying one or more conditions. This means instead of retrieving every row, you can retrieve just the data you need.

2. What is the WHERE Clause?

The WHERE clause is used in SQL to filter records before they are selected, updated, or deleted. It is applicable in:

• SELECT
• UPDATE
• DELETE

3. Basic Syntax of WHERE

sqlCopyEditSELECT column1, column2
FROM table_name
WHERE condition;

Example:

sqlCopyEditSELECT * FROM employees WHERE age > 30;

4. Filtering with Equality Conditions

The simplest use case of WHERE is to check equality:

sqlCopyEditSELECT * FROM users WHERE country = 'India';
SELECT * FROM orders WHERE status = 'delivered';

5. Using !=, <>, and NOT

You can use either != or <> for “not equal to”:

sqlCopyEditSELECT * FROM products WHERE category != 'Electronics';
SELECT * FROM students WHERE grade <> 'A';

Or use NOT:

sqlCopyEditSELECT * FROM customers WHERE NOT city = 'Delhi';

6. Greater Than and Less Than Comparisons

sqlCopyEditSELECT * FROM sales WHERE amount > 1000;
SELECT * FROM inventory WHERE quantity < 50;

Also:

• >= greater than or equal to
• <= less than or equal to

7. Filtering Text with LIKE

The LIKE operator is used to match text patterns:

sqlCopyEditSELECT * FROM users WHERE name LIKE 'A%';   -- starts with A
SELECT * FROM users WHERE email LIKE '%@gmail.com'; -- ends with

8. Wildcards in LIKE: % and _

Example:

sqlCopyEditSELECT * FROM books WHERE title LIKE '_ar%';  -- Matches 'Harry', 'Dark', etc.

9. Using IN to Match Multiple Values

sqlCopyEditSELECT * FROM employees WHERE department IN ('HR', 'Finance', 'Sales');

This is cleaner than using multiple OR conditions.

10. Filtering Between Ranges with BETWEEN

sqlCopyEditSELECT * FROM products WHERE price BETWEEN 100 AND 500;

This is inclusive — both 100 and 500 are part of the result.

11. Handling NULL Values

Use IS NULL and IS NOT NULL:

sqlCopyEditSELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

NULL cannot be compared using = or !=.

12. Logical Operators: AND, OR, and NOT

You can combine multiple conditions:

sqlCopyEditSELECT * FROM orders 
WHERE status = 'shipped' AND payment_method = 'Credit Card';

sqlCopyEditSELECT * FROM users 
WHERE age > 30 OR city = 'Mumbai';

sqlCopyEditSELECT * FROM employees 
WHERE NOT department = 'Sales';

13. Precedence in Conditional Expressions

Parentheses help control evaluation order:

sqlCopyEditSELECT * FROM users 
WHERE (age > 25 AND city = 'Pune') OR is_active = TRUE;

Always group related conditions to avoid logical errors.

14. Combining Multiple Conditions

sqlCopyEditSELECT * FROM events
WHERE city = 'Delhi' AND category IN ('Music', 'Art') AND date BETWEEN '2024-01-01' AND '2024-12-31';

You can build powerful filters using combinations of:

• IN
• BETWEEN
• LIKE
• AND/OR logic

15. WHERE Clause in Numeric Fields

sqlCopyEditSELECT * FROM invoices WHERE total_amount > 10000;

Use numeric filtering for salaries, scores, stock counts, etc.

16. WHERE Clause in Date Fields

sqlCopyEditSELECT * FROM reservations 
WHERE reservation_date >= '2025-01-01' AND reservation_date <= '2025-01-31';

SQL supports date comparisons directly. Use the proper format (YYYY-MM-DD).

17. WHERE Clause in String Fields

sqlCopyEditSELECT * FROM customers WHERE name = 'Ravi Kumar';
SELECT * FROM customers WHERE email LIKE '%@yahoo.com';

Always enclose string values in single quotes (').

18. Using WHERE with Subqueries

sqlCopyEditSELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

Subqueries help filter based on the result of another query.

19. Common Mistakes and Troubleshooting

• Using = instead of IS NULL
• Forgetting quotes around string values
• Mixing AND/OR without parentheses
• Using incorrect operators (=> instead of >=)
• Not checking for case sensitivity in string filters

20. Summary and What’s Next

The WHERE clause is essential for filtering data in SQL. It supports comparisons, pattern matching, range checks, null checks, and more — making your queries precise and powerful.