Filtering data

Filtering data is one of the most powerful features of SQL. In real-world applications, databases store thousands or even millions of records, but users rarely need all of them at once. The WHERE clause allows us to filter records and retrieve only the data that matches specific conditions. This makes queries more efficient, meaningful, and useful for reporting and decision-making.

---

WHERE Clause

The WHERE clause is used to filter rows based on one or more conditions. Only the rows that satisfy the condition will be included in the result set.

SELECT * FROM employees
WHERE department = 'Sales';

Explanation

This query retrieves only those employees whose department is Sales.

• The database checks each row in the employees table.
• If the department value is Sales, that row is returned.
• All other rows are ignored.

Key Points

• WHERE works row by row
• It is used with SELECT, UPDATE, and DELETE
• It reduces the size of the result set

---

Comparison Operators

Comparison operators are used in the WHERE clause to compare values.

Salary Greater Than a Value

SELECT first_name, last_name, salary
FROM employees
WHERE salary > 50000;

This query returns employees whose salary is more than 50,000.

---

Date Comparison

SELECT first_name, hire_date
FROM employees
WHERE hire_date < '2020-01-01';

This retrieves employees who joined the company before January 1, 2020.

---

Not Equal Condition

SELECT first_name, salary
FROM employees
WHERE salary != 60000;

This returns employees whose salary is not equal to 60,000.

---

List of Comparison Operators

Operator	Meaning
=	Equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
!= or <>	Not equal

---

Logical Operators (AND, OR, NOT)

Logical operators allow multiple conditions to be combined in a WHERE clause.

---

AND Operator

SELECT * FROM employees
WHERE department = 'Sales' AND salary > 50000;

Explanation

• Both conditions must be true
• Employee must be in Sales
• Salary must be greater than 50,000

---

OR Operator

SELECT * FROM employees
WHERE department = 'Sales' OR department = 'Marketing';

Explanation

• At least one condition must be true
• Employees from either Sales or Marketing are returned

---

NOT Operator

SELECT * FROM employees
WHERE NOT department = 'IT';

Explanation

• Excludes employees from the IT department
• Returns all other departments

---

BETWEEN Operator

The BETWEEN operator is used to filter values within a specific range.

SELECT first_name, last_name, salary
FROM employees
WHERE salary BETWEEN 40000 AND 60000;

Explanation

• BETWEEN is inclusive
• Includes both 40,000 and 60,000
• Makes range queries cleaner and more readable

Important Note

• Works with numbers, dates, and strings
• Order of values matters

---

IN Operator

The IN operator checks whether a value matches any value in a given list.

SELECT * FROM employees
WHERE department IN ('Sales', 'Marketing', 'HR');

Explanation

• Shorter and cleaner than multiple OR conditions
• Improves readability
• Useful for filtering fixed sets of values

---

LIKE Operator (Pattern Matching)

The LIKE operator is used for pattern-based searches, especially with text data.

---

Starts With a Letter

SELECT * FROM employees
WHERE first_name LIKE 'J%';

Returns employees whose first name starts with J.

---

Contains a Word

SELECT * FROM employees
WHERE email LIKE '%gmail%';

Returns employees whose email contains the word gmail.

---

Exact Length Match

SELECT * FROM employees
WHERE first_name LIKE '_____';

Returns employees whose first name has exactly five characters.

---

Wildcards Used in LIKE

Wildcard	Description
%	Matches zero or more characters
_	Matches exactly one character

---

IS NULL and IS NOT NULL

NULL represents missing or unknown data. It is not equal to zero or an empty string.

---

Checking for NULL Values

SELECT * FROM employees
WHERE email IS NULL;

Returns employees who do not have an email address.

---

Checking for NOT NULL Values

SELECT * FROM employees
WHERE email IS NOT NULL;

Returns employees who have an email address.

Important Rule

• You must use IS NULL or IS NOT NULL
• Using = NULL will not work

---

Order of Evaluation in WHERE Clause (Basic Understanding)

1. Comparison operators
2. NOT
3. AND
4. OR

Parentheses can be used to control execution order.

---

Real-World Use Cases

• Filtering job applicants by skill
• Finding high-salary employees
• Identifying inactive users
• Searching emails or usernames
• Generating department-wise reports

---

Common Beginner Mistakes

• Using = NULL instead of IS NULL
• Forgetting quotation marks for strings
• Confusing AND and OR logic
• Incorrect use of LIKE wildcards
• Case sensitivity issues

---

Best Practices

• Always use WHERE to limit data
• Test queries with SELECT before UPDATE or DELETE
• Use IN instead of multiple ORs
• Use BETWEEN for range queries
• Keep queries readable and well-formatted