Filtering Data

#Introduction

Spring Boot APIs, JPA queries, Hibernate, reports, dashboards, admin portals — everything revolves around filtering data.

#Sample Table

We'll use this employees table throughout the chapter:

#WHERE Clause

Without WHERE, SELECT * FROM employees; returns all employees. With it:

SELECT *
FROM employees
WHERE department = 'IT';

Rahul
Neha
Ankit

Think Like Java

WHERE salary > 50000

if (employee.getSalary() > 50000) {
    // include employee
}

A WHERE clause is essentially a filter condition.

#Comparison Operators

SELECT *
FROM employees
WHERE salary > 70000;

Neha
Ankit

#Logical Operators

AND — All Conditions Must Be True

SELECT *
FROM employees
WHERE department = 'IT'
  AND salary > 70000;

Neha
Ankit

Java equivalent: if (department.equals("IT") && salary > 70000).

OR — Either Condition Can Be True

SELECT *
FROM employees
WHERE city = 'Delhi'
   OR city = 'Mumbai';

Amit
Neha

Java equivalent: if (city.equals("Delhi") || city.equals("Mumbai")).

NOT — Reverse the Condition

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

Everyone except HR employees.

Combining AND and OR

SELECT *
FROM employees
WHERE department = 'IT'
  AND city = 'Bangalore';      -- Rahul

SELECT *
FROM employees
WHERE department = 'IT'
   OR salary > 80000;          -- Rahul, Neha, Ankit

Parentheses Matter

-- ❌ Ambiguous
SELECT *
FROM employees
WHERE department = 'IT'
   OR city = 'Delhi'
  AND salary > 70000;

SQL evaluates AND before OR, so this means IT OR (Delhi AND salary > 70000).

-- ✅ Correct
SELECT *
FROM employees
WHERE (department = 'IT' OR city = 'Delhi')
  AND salary > 70000;

#BETWEEN

Instead of salary >= 50000 AND salary <= 70000:

SELECT *
FROM employees
WHERE salary BETWEEN 50000 AND 70000;

Much cleaner — and inclusive on both ends.

Dates

SELECT *
FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31';

Very common in reporting.

#IN and NOT IN

Instead of city = 'Delhi' OR city = 'Mumbai' OR city = 'Bangalore':

SELECT *
FROM employees
WHERE city IN ('Delhi', 'Mumbai', 'Bangalore');

SELECT *
FROM employees
WHERE city NOT IN ('Delhi', 'Mumbai');

Cleaner and easier to maintain.

#LIKE

Pattern matching with wildcards:

SELECT *
FROM employees
WHERE name LIKE 'R%';    -- starts with R: Rahul, Raj

#REGEXP

Much more powerful than LIKE:

Mostly used for validations and advanced searches. Use a binary/case-sensitive comparison (e.g. REGEXP BINARY or REGEXP_LIKE(col, pattern, 'c')) if case matters.

#NULL Handling

Suppose manager_id is 10, 20 or NULL. This is wrong:

WHERE manager_id = NULL      -- returns nothing

Correct:

WHERE manager_id IS NULL;
WHERE manager_id IS NOT NULL;

Why? NULL means unknown, not a value. Any comparison with NULL evaluates to UNKNOWN, not TRUE.

#LIMIT and OFFSET

-- First five employees
SELECT *
FROM employees
LIMIT 5;

-- Pagination: skip the first 20 rows, return the next 10
SELECT *
FROM employees
LIMIT 10 OFFSET 20;

In Spring Boot, PageRequest.of(2, 10) (page index 2, size 10) produces SQL similar to LIMIT 10 OFFSET 20.

#Logical Execution Order

Many developers think SQL executes top to bottom. Actually:

SELECT name
FROM employees
WHERE salary > 70000
ORDER BY salary;

This explains why aliases from the SELECT list usually can't be referenced in WHERE — WHERE is evaluated before SELECT.

#Real Spring Boot Example

List<Employee> findByDepartmentAndSalaryGreaterThan(String department, Double salary);

Generated SQL:

SELECT *
FROM employees
WHERE department = ?
  AND salary > ?;

JPQL:

@Query("""
        SELECT e
        FROM Employee e
        WHERE e.salary > :salary
        """)

becomes SQL similar to SELECT * FROM employees WHERE salary > ?. Understanding SQL makes debugging JPA much easier.

#Performance Tips

✅ WHERE salary = 50000 uses an index efficiently (if one exists).

❌ Avoid wrapping indexed columns in functions:

WHERE YEAR(joining_date) = 2026

This often prevents index usage. Prefer:

WHERE joining_date >= '2026-01-01'
  AND joining_date <  '2027-01-01'

(More on this in the indexing chapter.) And avoid SELECT * — fetch only the needed columns.

#Hands-On Assignment

Using the employees table, write queries to:

1. Find employees in the IT department.
2. Find employees with salary greater than 70,000.
3. Find employees in Bangalore with salary above 60,000.
4. Find employees whose names start with "R".
5. Find employees whose names contain "ah".
6. Find employees without a manager.
7. Find employees from Bangalore, Chennai or Delhi.
8. Find employees whose salary is between 60,000 and 80,000.
9. Display the first three employees.
10. Display the next three employees after skipping the first three.

Challenge Problems

1. Find employees not in the IT department.
2. Find names ending with t.
3. Find names having exactly 5 characters.
4. Find employees with a NULL manager and salary greater than 50,000.
5. Find employees who are either in Finance or have a salary above 80,000.
6. Write one query using AND, OR and NOT together with parentheses.