Advanced Filtering and Nested Query Conditions in MySQL

Course: MySQL Training

Topic: Advanced Filtering and Nested Query Conditions

Course Link: MySQL Training Course – JustAcademy

Demo Link: Register for a Free Course Demo

1. Introduction to Advanced Filtering in MySQL

Advanced filtering in MySQL is the process of retrieving specific records from a database by combining multiple conditions, logical operators, pattern matching, ranges, subqueries, and nested expressions. It helps users find precise information without retrieving unnecessary rows.

Basic filtering generally uses a simple condition in the WHERE clause. Advanced filtering allows several conditions to work together so that queries can answer more complex business questions.

1.1 What Is Filtering?

Filtering means selecting only those rows that satisfy specified conditions. MySQL evaluates the conditions and returns records that match the query requirements.

For example, an organization may want to retrieve employees who work in the Sales department, earn more than ₹40,000, and joined after a particular date.

SELECT employee_id, employee_name, department, salary
FROM employees
WHERE department = 'Sales'
  AND salary > 40000
  AND joining_date > '2024-01-01';

This query returns employees who satisfy all three conditions.

1.2 Why Advanced Filtering Is Important

• Retrieves only relevant records from large tables.
• Combines several business requirements in a single query.
• Supports reporting, analysis, and data validation.
• Helps identify records that meet specific ranges or patterns.
• Allows queries to use results returned by other queries.
• Reduces the need to export data and filter it manually.

2. Understanding the WHERE Clause

The WHERE clause filters records before the final result set is returned. It is one of the most commonly used clauses in MySQL.

2.1 Basic Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition;

2.2 Example Table: Employees

Consider the following sample data for practicing filtering conditions.

2.3 Filtering with Comparison Operators

Comparison operators compare column values against specific values or expressions.

Example:

SELECT employee_name, salary
FROM employees
WHERE salary >= 50000;

This query returns employees whose salary is at least 50,000.

3. Combining Conditions with AND

The AND operator combines two or more conditions. A row is returned only when every condition connected by AND evaluates to true.

3.1 Syntax

SELECT column_list
FROM table_name
WHERE condition1
  AND condition2
  AND condition3;

3.2 Example: Multiple Requirements

SELECT employee_name, department, salary
FROM employees
WHERE department = 'IT'
  AND salary > 50000;

This query retrieves IT employees whose salary is greater than 50,000. An employee must satisfy both conditions.

3.3 AND with Date Conditions

SELECT employee_name, joining_date
FROM employees
WHERE joining_date >= '2024-01-01'
  AND joining_date < '2025-01-01';

This query retrieves employees who joined during the calendar year 2024, assuming joining_date is a date or datetime column. Using an exclusive upper bound also works well for datetime values because it includes all times on December 31, 2024.

4. Combining Conditions with OR

The OR operator returns a row when at least one connected condition evaluates to true.

4.1 Syntax

SELECT column_list
FROM table_name
WHERE condition1
   OR condition2;

4.2 Example: Multiple Departments

SELECT employee_name, department
FROM employees
WHERE department = 'IT'
   OR department = 'Sales';

This query returns employees who work in either IT or Sales.

4.3 OR with Different Column Conditions

SELECT employee_name, city, salary
FROM employees
WHERE city = 'Mumbai'
   OR salary > 60000;

This query returns employees who are located in Mumbai, earn more than 60,000, or satisfy both conditions.

5. Combining AND and OR in a Single Query

Advanced filtering frequently requires both AND and OR. Parentheses are important because they make the intended logic explicit.

5.1 Understanding Operator Precedence

In MySQL, AND has higher precedence than OR. Therefore, an expression without parentheses may be evaluated differently from what a beginner expects.

Consider this query:

SELECT employee_name, department, salary
FROM employees
WHERE department = 'IT'
   OR department = 'Sales'
  AND salary > 45000;

MySQL interprets the condition as:

department = 'IT'
OR (department = 'Sales' AND salary > 45000)

As a result, all IT employees can match regardless of salary, while Sales employees must earn more than 45,000.

5.2 Using Parentheses to Control Logic

SELECT employee_name, department, salary
FROM employees
WHERE (department = 'IT' OR department = 'Sales')
  AND salary > 45000;

This query first considers employees in IT or Sales and then requires their salary to be greater than 45,000.

5.3 Comparison of the Two Conditions

Best practice: Use parentheses whenever a query combines AND and OR. This makes the intended business logic easier to understand, review, and maintain.

6. Filtering with the IN Operator

The IN operator checks whether a value matches any value in a specified list. It is a convenient alternative to writing several equality conditions connected by OR.

6.1 Syntax

SELECT column_list
FROM table_name
WHERE column_name IN (value1, value2, value3);

6.2 Example

SELECT employee_name, department
FROM employees
WHERE department IN ('IT', 'HR', 'Sales');

This query returns employees whose department is one of the listed departments.

6.3 IN Compared with OR

These two queries express the same basic filtering requirement:

SELECT employee_name
FROM employees
WHERE city = 'Mumbai'
   OR city = 'Pune';

SELECT employee_name
FROM employees
WHERE city IN ('Mumbai', 'Pune');

The IN version is generally easier to read when many alternative values are involved.

6.4 Using NOT IN

The NOT IN operator excludes the values in a specified list.

SELECT employee_name, city
FROM employees
WHERE city NOT IN ('Mumbai', 'Pune');

This query returns employees whose city is neither Mumbai nor Pune. Be careful when using NOT IN with a subquery: if the subquery returns a NULL, the condition can evaluate to unknown and unexpectedly return no rows. Consider NOT EXISTS when working with potentially nullable subquery results.

7. Filtering with BETWEEN

The BETWEEN operator checks whether a value falls within a range. Both endpoints are included.

7.1 Syntax

SELECT column_list
FROM table_name
WHERE column_name BETWEEN lower_value AND upper_value;

7.2 Example: Salary Range

SELECT employee_name, salary
FROM employees
WHERE salary BETWEEN 40000 AND 60000;

This query includes employees earning exactly 40,000 or 60,000, as well as those earning between these amounts.

7.3 NOT BETWEEN

SELECT employee_name, salary
FROM employees
WHERE salary NOT BETWEEN 40000 AND 60000;

This query returns employees whose salaries are below 40,000 or above 60,000.

7.4 Date Range Filtering

SELECT order_id, order_date, total_amount
FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31';

For a DATE column, this includes dates from January 1 through January 31, 2026. If order_date is a DATETIME or TIMESTAMP column, the upper date literal may represent midnight at the start of January 31, excluding later times that day. A half-open range is safer for an entire month:

SELECT order_id, order_date, total_amount
FROM orders
WHERE order_date >= '2026-01-01'
  AND order_date < '2026-02-01';

8. Pattern Matching with LIKE

The LIKE operator filters text values according to a pattern. It is useful for searching names, email addresses, product descriptions, and other text fields.

8.1 Wildcard Characters

8.2 Names Beginning with a Letter

SELECT employee_name
FROM employees
WHERE employee_name LIKE 'A%';

This query retrieves names that begin with A.

8.3 Names Ending with a Letter

SELECT employee_name
FROM employees
WHERE employee_name LIKE '%a';

This query retrieves names that end with the letter a, subject to the column's collation rules.

8.4 Searching for Text Anywhere in a Value

SELECT product_name
FROM products
WHERE product_name LIKE '%phone%';

This query retrieves product names containing the text "phone".

8.5 Case Sensitivity

Whether LIKE comparisons are case-sensitive depends on the collation of the compared string. Many MySQL collations are case-insensitive, while binary or case-sensitive collations can distinguish uppercase and lowercase letters. Check the column collation when case behavior matters.

9. Filtering NULL Values

NULL represents a missing or unknown value. It cannot be tested reliably with ordinary equality operators such as = or <>.

9.1 Using IS NULL

SELECT employee_name, manager_id
FROM employees
WHERE manager_id IS NULL;

This query retrieves employees whose manager ID is missing or unknown.

9.2 Using IS NOT NULL

SELECT employee_name, manager_id
FROM employees
WHERE manager_id IS NOT NULL;

This query returns employees whose manager ID has a non-NULL value.

9.3 Incorrect NULL Filtering

-- Incorrect for finding NULL values
SELECT employee_name
FROM employees
WHERE manager_id = NULL;

The expression manager_id = NULL does not test for a NULL value. Use IS NULL or IS NOT NULL instead.

10. What Are Nested Query Conditions?

A nested query, also known as a subquery, is a query placed inside another SQL statement. It allows the outer query to use a value or a set of values produced by the inner query.

Nested queries are especially useful when the condition depends on information stored in another table or on a value calculated from existing data.

10.1 Basic Structure of a Subquery

SELECT column_list
FROM table_name
WHERE column_name operator (
    SELECT column_name
    FROM another_table
    WHERE condition
);

10.2 Example: Employees Earning More Than the Average Salary

SELECT employee_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

The inner query calculates the average salary. The outer query retrieves employees whose salary is greater than that average.

10.3 How a Nested Query Works

1. MySQL evaluates the inner query as required by the execution plan.
2. The inner query produces a scalar value, a set of values, or a related result.
3. The outer query applies its condition using that result.
4. MySQL returns the rows that satisfy the complete condition.

The optimizer may transform or combine parts of a subquery with the outer query, so the physical execution does not always follow a simple step-by-step sequence.

11. Scalar Subqueries in Filtering

A scalar subquery returns one value: one row with one column. It can be used with comparison operators such as =, >, <, >=, and <=.

11.1 Example: Employees with the Highest Salary

SELECT employee_name, salary
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
);

The inner query calculates the maximum salary. The outer query returns every employee whose salary equals that maximum, including multiple employees if they share the highest salary.

11.2 Example: Products Above the Average Price

SELECT product_name, price
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

This query retrieves products priced above the average price across the table.

11.3 Important Rule

A scalar subquery must return no more than one row and one column. If it returns multiple rows, MySQL can raise an error. Use IN, ANY, ALL, or another appropriate construction when the inner query can return multiple values.

12. Nested Conditions with IN and Subqueries

The IN operator is commonly used when a subquery returns multiple values.

12.1 Example: Employees in Departments Located in Mumbai

Assume a departments table contains department_id, department_name, and location. The employees table contains department_id.

SELECT employee_name, department_id
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location = 'Mumbai'
);

The inner query returns department IDs for departments located in Mumbai. The outer query finds employees assigned to those departments.

12.2 Example: Customers Who Have Placed Orders

SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
);

This query retrieves customers whose IDs appear in the orders table.

12.3 Using NOT IN to Find Customers Without Orders

SELECT customer_id, customer_name
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id
    FROM orders
    WHERE customer_id IS NOT NULL
);

The inner query explicitly excludes NULL customer IDs to prevent NULL-related problems with NOT IN. Another robust option is NOT EXISTS, which is often preferable for this type of requirement.

13. Using EXISTS and NOT EXISTS

EXISTS checks whether a subquery returns at least one row. NOT EXISTS checks whether the subquery returns no rows. These operators are useful for testing whether related records exist.

13.1 Example: Customers Who Have Orders

SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
);

For each customer considered by the outer query, the subquery checks for a matching order. Customers with at least one matching order satisfy the condition.

13.2 Example: Customers Who Have Never Ordered

SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
);

This query returns customers for whom no matching order exists.

13.3 EXISTS Compared with IN

Neither operator is universally faster. The best choice depends on the query, data distribution, indexes, and MySQL optimizer.

14. Correlated Subqueries

A correlated subquery refers to a column from the outer query. The relationship between the inner and outer query allows each outer row to be evaluated against matching records in another table or within the same table.

14.1 Example: Employees Earning More Than Their Department Average

SELECT e.employee_id, e.employee_name, e.department_id, e.salary
FROM employees AS e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees AS e2
    WHERE e2.department_id = e.department_id
);

The inner query calculates the average salary for the department associated with the outer employee. The outer query returns employees who earn more than their department's average.

14.2 Understanding Table Aliases

• e represents the employee row being evaluated by the outer query.
• e2 represents rows considered by the inner query.
• e2.department_id = e.department_id links the inner query to the outer row.
• AVG(e2.salary) calculates the average for the matching department.

14.3 Important Consideration for NULL Department IDs

If department_id is NULL, the equality condition does not match other NULL department IDs. Such employees may not be returned because the correlated average can be NULL. If the business requirement treats all unassigned employees as one group, handle that case explicitly and consistently.

15. Nested Conditions with ANY and ALL

ANY and ALL compare a value with the results of a subquery that may return multiple rows.

15.1 Using ANY

A condition using ANY is true when the comparison succeeds for at least one value returned by the subquery.

SELECT product_name, price
FROM products
WHERE price > ANY (
    SELECT price
    FROM products
    WHERE category_id = 3
);

This query returns products whose price is greater than at least one price in category 3. If the subquery returns non-NULL prices, this means the price exceeds the minimum of those prices.

15.2 Using ALL

A condition using ALL is true when the comparison succeeds for every value returned by the subquery.

SELECT product_name, price
FROM products
WHERE price > ALL (
    SELECT price
    FROM products
    WHERE category_id = 3
);

This query returns products whose price is greater than every non-NULL price returned for category 3.

15.3 ANY vs ALL

Note: NULL values can make comparisons evaluate to unknown. Also, comparisons with ALL over an empty result set are true, while comparisons with ANY over an empty result set are false. Account for these rules when interpreting edge cases.

16. Nested Conditions with Multiple Logical Operators

Advanced queries may combine nested subqueries with AND, OR, IN, and other filtering operators.

16.1 Example: High-Paid Employees in Selected Departments

SELECT employee_name, department_id, salary
FROM employees
WHERE salary > 50000
  AND department_id IN (
      SELECT department_id
      FROM departments
      WHERE location IN ('Mumbai', 'Pune')
  );

This query returns employees earning more than 50,000 who work in departments located in Mumbai or Pune.

16.2 Example: Customers Meeting Either of Two Requirements

SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
      AND o.total_amount > 10000
)
OR c.city = 'Mumbai';

This query returns customers who have at least one order exceeding 10,000 or whose city is Mumbai. Customers who satisfy both conditions are still returned only once by this query because the outer table is the customers table.

16.3 Example: Combining AND, OR, and a Nested Condition

SELECT p.product_id, p.product_name, p.price
FROM products AS p
WHERE p.price > 1000
  AND (
      p.category_id IN (
          SELECT category_id
          FROM categories
          WHERE category_name = 'Electronics'
      )
      OR p.stock_quantity < 10
  );

This query retrieves products priced above 1,000 that either belong to Electronics or have fewer than 10 units in stock.

17. Using Nested Conditions with Aggregate Functions

Aggregate functions summarize multiple rows and can be used inside subqueries to create dynamic filtering conditions.

17.1 Common Aggregate Functions

17.2 Products with Prices Above the Average in Their Category

SELECT p.product_id, p.product_name, p.category_id, p.price
FROM products AS p
WHERE p.price > (
    SELECT AVG(p2.price)
    FROM products AS p2
    WHERE p2.category_id = p.category_id
);

This correlated subquery compares each product's price with the average price in its category.

17.3 Customers with More Than Three Orders

SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE (
    SELECT COUNT(*)
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
) > 3;

This query returns customers with more than three matching orders.

17.4 Departments with Above-Average Total Salary

SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
HAVING SUM(salary) > (
    SELECT AVG(department_total)
    FROM (
        SELECT SUM(salary) AS department_total
        FROM employees
        GROUP BY department_id
    ) AS department_salaries
);

The inner derived table calculates the total salary for each department. The outer scalar subquery calculates the average of those department totals. The main query returns departments whose total salary exceeds that average.

Important: The WHERE clause filters rows before grouping, while HAVING filters groups after aggregation. Use the clause that matches the level of filtering required.

18. Nested Queries Compared with JOINs

Subqueries and joins can sometimes express the same business requirement. The best choice depends on readability, the expected result, and query performance.

18.1 Using a Subquery

SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
);

18.2 Using a JOIN

SELECT DISTINCT c.customer_id, c.customer_name
FROM customers AS c
INNER JOIN orders AS o
    ON o.customer_id = c.customer_id;

Both queries return customers who have matching orders. The DISTINCT keyword in the join version prevents customers with multiple orders from appearing multiple times.

18.3 Key Differences

Do not assume that a JOIN is always faster or that a subquery is always slower. Inspect the execution plan and test with representative data when performance matters.

19. Filtering with CASE Expressions

The CASE expression evaluates conditions and returns a value based on the first matching branch. It is useful for conditional calculations and can also be used in a filtering expression.

19.1 Basic CASE Syntax

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE default_result
END

19.2 Example: Filtering by a Calculated Salary Category

SELECT employee_name, salary
FROM employees
WHERE CASE
    WHEN salary >= 60000 THEN 'High'
    WHEN salary >= 40000 THEN 'Medium'
    ELSE 'Low'
END = 'High';

This query returns employees categorized as High salary. For a simple threshold, WHERE salary >= 60000 is clearer and may be easier to optimize. Use CASE-based filtering when the conditional classification itself is required.

20. Filtering with EXISTS and Multiple Conditions

Nested EXISTS conditions can check different related tables or business requirements.

20.1 Example: Customers with High-Value Orders and a Valid Email

SELECT c.customer_id, c.customer_name, c.email
FROM customers AS c
WHERE c.email IS NOT NULL
  AND c.email <> ''
  AND EXISTS (
      SELECT 1
      FROM orders AS o
      WHERE o.customer_id = c.customer_id
        AND o.total_amount > 20000
  );

This query returns customers with a non-NULL, non-empty email and at least one order greater than 20,000. It does not validate whether the email address has a correct format.

20.2 Example: Products Ordered by Customers from a Selected City

SELECT p.product_id, p.product_name
FROM products AS p
WHERE EXISTS (
    SELECT 1
    FROM order_items AS oi
    INNER JOIN orders AS o
        ON o.order_id = oi.order_id
    INNER JOIN customers AS c
        ON c.customer_id = o.customer_id
    WHERE oi.product_id = p.product_id
      AND c.city = 'Mumbai'
);

This query retrieves products that have appeared in at least one order placed by a customer from Mumbai.

21. Understanding Logical Evaluation and NULL

MySQL conditions use three-valued logic: TRUE, FALSE, and UNKNOWN. A comparison involving NULL often evaluates to UNKNOWN rather than TRUE or FALSE. Rows are normally returned by a WHERE condition only when the condition evaluates to TRUE.

21.1 Logical Truth Table

21.2 Example of NULL Affecting Filtering

SELECT employee_name
FROM employees
WHERE salary > 40000;

If an employee's salary is NULL, the comparison is UNKNOWN, so that employee is not returned by this condition.

21.3 Explicitly Including NULL Values

SELECT employee_name, salary
FROM employees
WHERE salary > 40000
   OR salary IS NULL;

This query includes employees who earn more than 40,000 and employees whose salary is unknown.

22. Query Execution Order and Filtering

Understanding the logical processing order of a SELECT query helps prevent errors when combining filters, grouping, and nested conditions.

22.1 Simplified Logical Order

1. FROM and JOIN operations identify the source rows.
2. WHERE filters individual rows.
3. GROUP BY groups the remaining rows.
4. Aggregate calculations are evaluated as required.
5. HAVING filters grouped results.
6. SELECT determines the output expressions.
7. DISTINCT removes duplicate output rows when requested.
8. ORDER BY sorts the results.
9. LIMIT restricts the number of returned rows.

This is a simplified logical model, not a guarantee of the physical execution sequence. MySQL's optimizer may transform the query to improve efficiency while preserving the required result.

22.2 WHERE vs HAVING

23. Practical Database Example: E-Commerce Filtering

Consider an e-commerce database containing customers, orders, and products. The business team wants to identify customers who live in Mumbai or Pune and have placed at least one high-value order.

23.1 Sample Table Structures

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    city VARCHAR(50),
    email VARCHAR(150)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2),
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

23.2 Insert Sample Records

INSERT INTO customers
    (customer_id, customer_name, city, email)
VALUES
    (1, 'Aarav Shah', 'Mumbai', '[email protected]'),
    (2, 'Priya Nair', 'Pune', '[email protected]'),
    (3, 'Rahul Mehta', 'Nashik', '[email protected]'),
    (4, 'Neha Patil', 'Mumbai', '[email protected]');

INSERT INTO orders
    (order_id, customer_id, order_date, total_amount)
VALUES
    (101, 1, '2026-01-10', 25000.00),
    (102, 1, '2026-02-12', 5000.00),
    (103, 2, '2026-02-15', 18000.00),
    (104, 3, '2026-03-01', 30000.00);

23.3 Business Requirement

Find customers who live in Mumbai or Pune and have at least one order greater than 20,000.

SELECT c.customer_id, c.customer_name, c.city
FROM customers AS c
WHERE c.city IN ('Mumbai', 'Pune')
  AND EXISTS (
      SELECT 1
      FROM orders AS o
      WHERE o.customer_id = c.customer_id
        AND o.total_amount > 20000
  );

23.4 Understanding the Result

This example demonstrates how a list-based filter and a correlated EXISTS subquery can work together to implement multiple business rules.

24. Advanced Filtering with NOT EXISTS

NOT EXISTS is particularly useful for finding records that have no matching related rows.

24.1 Find Products Never Ordered

SELECT p.product_id, p.product_name
FROM products AS p
WHERE NOT EXISTS (
    SELECT 1
    FROM order_items AS oi
    WHERE oi.product_id = p.product_id
);

This query returns products for which no matching order-item row exists.

24.2 Find Departments Without Employees

SELECT d.department_id, d.department_name
FROM departments AS d
WHERE NOT EXISTS (
    SELECT 1
    FROM employees AS e
    WHERE e.department_id = d.department_id
);

This query identifies departments that have no employees assigned to them.

24.3 Why NOT EXISTS Is Useful

• It clearly expresses the requirement that no related record should exist.
• It avoids the specific NULL-related problem that can occur with NOT IN.
• It can be used with multiple conditions inside the subquery.
• It is useful for identifying missing relationships and incomplete business processes.

25. Common Errors in Advanced Filtering

26. Performance Optimization for Advanced Filtering

Advanced filtering can involve many conditions and subqueries. Good query design helps MySQL evaluate these conditions efficiently, especially when tables contain large numbers of records.

26.1 Create Appropriate Indexes

Indexes can help MySQL locate matching rows without scanning an entire table. Index selection should reflect the actual query patterns and data distribution.

CREATE INDEX idx_employees_department_salary
ON employees (department_id, salary);

This composite index may help queries that filter by department and salary. The usefulness of the index depends on the conditions, column order, selectivity, and overall execution plan.

26.2 Index Columns Used in Relationships

CREATE INDEX idx_orders_customer_id
ON orders (customer_id);

This index may help queries that search orders by customer ID. MySQL may already create an appropriate index for a foreign-key column, depending on the table definition, so inspect existing indexes before adding another one.

26.3 Avoid SELECT *

Request only the columns required by the result. This reduces unnecessary data transfer and can sometimes allow MySQL to satisfy a query using an index alone.

-- Less specific
SELECT *
FROM employees
WHERE salary > 50000;

-- More specific
SELECT employee_id, employee_name, salary
FROM employees
WHERE salary > 50000;

26.4 Avoid Unnecessary Functions on Filtered Columns

Applying a function to an indexed column can make ordinary index access less effective in some query patterns.

-- May prevent efficient use of a normal index on order_date
SELECT order_id, order_date
FROM orders
WHERE YEAR(order_date) = 2026;

A range condition is often preferable:

SELECT order_id, order_date
FROM orders
WHERE order_date >= '2026-01-01'
  AND order_date < '2027-01-01';

26.5 Use EXPLAIN to Inspect a Query

EXPLAIN
SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
      AND o.total_amount > 20000
);

EXPLAIN shows information about the query plan, such as table access methods, possible indexes, selected indexes, and estimated rows. In supported MySQL versions, EXPLAIN ANALYZE can execute a statement and report actual execution information. Use it carefully because it runs the query.

26.6 General Optimization Checklist

• Confirm that the query returns the correct records before optimizing it.
• Use indexes appropriate to common filter and join conditions.
• Check existing indexes before creating new ones.
• Use table aliases to clarify complex queries.
• Use parentheses to make logical conditions explicit.
• Avoid retrieving unnecessary columns.
• Use EXPLAIN to examine query plans.
• Test performance with representative data and realistic parameters.
• Do not rewrite a readable query solely because it contains a subquery.

27. Step-by-Step Method for Writing Advanced Filtering Queries

1. Understand the requirement: Identify exactly which records should be returned.
2. Identify the source tables: Determine which tables contain the required columns.
3. Separate the conditions: List individual requirements such as city, salary, date, or order amount.
4. Choose logical operators: Use AND when all conditions must match and OR when any condition can match.
5. Group conditions: Add parentheses around related AND and OR expressions.
6. Determine whether a subquery is needed: Use a subquery when the filter depends on another query's result.
7. Select the appropriate subquery operator: Use scalar comparisons, IN, EXISTS, NOT EXISTS, ANY, or ALL according to the requirement.
8. Handle NULL values: Consider whether missing values should be included or excluded.
9. Test the query: Compare the returned rows with expected results using sample records.
10. Optimize when necessary: Review indexes and inspect the execution plan.

28. Practice Exercises

Use the sample employees, departments, customers, orders, and products tables to practice advanced filtering.

Exercise 1: Multiple Conditions

Retrieve employees in the IT department who earn more than 50,000.

Exercise 2: AND and OR

Retrieve employees who work in HR or Sales and earn at least 40,000.

Exercise 3: IN Operator

Find customers whose city is Mumbai, Pune, or Nashik.

Exercise 4: Range Filtering

Retrieve products priced between 500 and 2,000, including both endpoints.

Exercise 5: Pattern Matching

Find customer names that begin with the letter P.

Exercise 6: Scalar Subquery

Retrieve employees whose salaries are higher than the overall average salary.

Exercise 7: IN with Subquery

Find employees who belong to departments located in Mumbai.

Exercise 8: EXISTS

Find customers who have placed at least one order.

Exercise 9: NOT EXISTS

Find products that have never appeared in an order item.

Exercise 10: Correlated Subquery

Find employees whose salary is higher than the average salary of their department.

Exercise 11: Aggregate Subquery

Find customers who have placed more than three orders.

Exercise 12: Nested Business Condition

Find customers from Mumbai or Pune who have placed at least one order above 20,000.

29. Practice Exercise Solutions

Solution 1

SELECT employee_name, salary
FROM employees
WHERE department = 'IT'
  AND salary > 50000;

Solution 2

SELECT employee_name, department, salary
FROM employees
WHERE department IN ('HR', 'Sales')
  AND salary >= 40000;

Solution 3

SELECT customer_id, customer_name, city
FROM customers
WHERE city IN ('Mumbai', 'Pune', 'Nashik');

Solution 4

SELECT product_name, price
FROM products
WHERE price BETWEEN 500 AND 2000;

Solution 5

SELECT customer_name
FROM customers
WHERE customer_name LIKE 'P%';

Solution 6

SELECT employee_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

Solution 7

SELECT e.employee_name, e.department_id
FROM employees AS e
WHERE e.department_id IN (
    SELECT d.department_id
    FROM departments AS d
    WHERE d.location = 'Mumbai'
);

Solution 8

SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
);

Solution 9

SELECT p.product_id, p.product_name
FROM products AS p
WHERE NOT EXISTS (
    SELECT 1
    FROM order_items AS oi
    WHERE oi.product_id = p.product_id
);

Solution 10

SELECT e.employee_name, e.department_id, e.salary
FROM employees AS e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees AS e2
    WHERE e2.department_id = e.department_id
);

Solution 11

SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE (
    SELECT COUNT(*)
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
) > 3;

Solution 12

SELECT c.customer_id, c.customer_name, c.city
FROM customers AS c
WHERE c.city IN ('Mumbai', 'Pune')
  AND EXISTS (
      SELECT 1
      FROM orders AS o
      WHERE o.customer_id = c.customer_id
        AND o.total_amount > 20000
  );

30. Mini Project: Customer and Order Analysis

Project objective: Create a set of SQL queries that help an e-commerce business identify valuable customers, inactive customers, high-value orders, and products with low sales activity.

30.1 Requirements

• Find customers from selected cities.
• Retrieve customers who have placed orders above a specified amount.
• Identify customers who have never placed an order.
• Find orders above the average order value.
• Identify products that have never been ordered.
• Find customers whose total order count exceeds a specified threshold.
• Use logical grouping to combine multiple business rules.

30.2 Example: Orders Above the Average Order Value

SELECT order_id, customer_id, total_amount
FROM orders
WHERE total_amount > (
    SELECT AVG(total_amount)
    FROM orders
);

30.3 Example: Customers with No Orders

SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
);

30.4 Example: Customers with at Least Five Orders

SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE (
    SELECT COUNT(*)
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
) >= 5;

30.5 Expected Learning Outcomes

• Write queries with multiple filtering conditions.
• Combine AND, OR, IN, BETWEEN, LIKE, and NULL checks.
• Use scalar and multi-row subqueries correctly.
• Apply EXISTS and NOT EXISTS to relationship-based requirements.
• Understand how NULL values affect filtering logic.
• Use indexes and execution plans to investigate performance.

31. Interview Questions and Answers

Q1. What is advanced filtering in MySQL?

Answer: Advanced filtering is the process of retrieving records by combining multiple conditions, logical operators, ranges, pattern matching, NULL checks, and nested queries.

Q2. What is the difference between AND and OR?

Answer: AND requires all connected conditions to be true. OR requires at least one connected condition to be true.

Q3. Why are parentheses important in a WHERE clause?

Answer: Parentheses control how conditions are grouped and make the intended logic explicit, especially when AND and OR are combined.

Q4. What is a nested query?

Answer: A nested query or subquery is a SELECT statement embedded inside another SQL statement. Its result can be used by the outer query.

Q5. What is a scalar subquery?

Answer: A scalar subquery returns one column and at most one row, producing a single value for use in an expression.

Q6. What is a correlated subquery?

Answer: A correlated subquery refers to columns from its outer query. This lets the inner query evaluate conditions related to the current outer row.

Q7. What is the difference between IN and EXISTS?

Answer: IN checks whether a value matches one of the values returned by a list or subquery. EXISTS checks whether the subquery returns at least one row.

Q8. Why can NOT IN produce unexpected results?

Answer: If the list or subquery contains NULL, a non-matching comparison may evaluate to UNKNOWN. Filtering NULLs or using NOT EXISTS can avoid this issue.

Q9. What is the purpose of BETWEEN?

Answer: BETWEEN checks whether a value lies within an inclusive range. Both the lower and upper endpoints are included.

Q10. What is the difference between WHERE and HAVING?

Answer: WHERE filters individual rows before grouping, while HAVING filters groups after aggregation.

Q11. What are the wildcard characters used by LIKE?

Answer: The percent sign (%) matches zero or more characters, and the underscore (_) matches exactly one character.

Q12. How do you check for missing values in MySQL?

Answer: Use IS NULL to find NULL values and IS NOT NULL to find non-NULL values.

Q13. Can a subquery return multiple rows?

Answer: Yes. Operators such as IN, EXISTS, ANY, and ALL can work with multi-row subquery results. A scalar comparison generally requires a subquery that returns at most one row.

Q14. What does EXISTS return?

Answer: EXISTS evaluates to true if the subquery returns at least one row and false if it returns no rows.

Q15. What does NOT EXISTS do?

Answer: NOT EXISTS identifies rows for which the subquery finds no matching row. It is commonly used to find customers without orders or products that have never been sold.

Q16. How can you find employees earning more than their department average?

Answer: Use a correlated subquery that calculates the average salary for the employee's department and compares the employee's salary against it.

Q17. How do you optimize a complex filtering query?

Answer: Review the query plan using EXPLAIN, check indexes on filtering and relationship columns, avoid unnecessary expressions, retrieve only needed columns, and test with realistic data.

Q18. Is a subquery always slower than a JOIN?

Answer: No. MySQL can optimize many subqueries. Performance depends on the query structure, indexes, data distribution, and execution plan.

32. Quick Revision Checklist

• Understand the role of the WHERE clause.
• Use comparison operators for numeric, text, and date conditions.
• Combine AND and OR with appropriate parentheses.
• Use IN and NOT IN for list-based filtering.
• Use BETWEEN for inclusive range filtering.
• Use LIKE for pattern matching.
• Use IS NULL and IS NOT NULL to handle missing values.
• Understand scalar, multi-row, and correlated subqueries.
• Use EXISTS and NOT EXISTS for checking related records.
• Understand ANY and ALL comparisons.
• Know the difference between WHERE and HAVING.
• Be aware of NULL-related three-valued logic.
• Use EXPLAIN to investigate query performance.
• Test complex conditions against sample data before using them in production.

33. Best Practices for Advanced Filtering

• Write the business requirement in plain language before creating the query.
• Break complicated conditions into smaller logical parts.
• Use descriptive table aliases in queries that involve multiple tables.
• Group combined AND and OR conditions with parentheses.
• Prefer EXISTS or NOT EXISTS when the requirement concerns the existence or absence of related rows.
• Use NOT IN only when its NULL behavior is understood and handled.
• Use half-open ranges for filtering complete periods on datetime columns.
• Choose an appropriate index based on real query patterns rather than indexing every column.
• Do not assume one SQL formulation is always faster than another.
• Validate results and test edge cases, including duplicate records, empty subqueries, and NULL values.

34. Conclusion

Advanced filtering and nested query conditions are essential MySQL skills for retrieving accurate and meaningful information from relational databases. By combining comparison operators, AND, OR, IN, BETWEEN, LIKE, NULL checks, and subqueries, developers can express complex requirements directly in SQL.

Understanding scalar and correlated subqueries, EXISTS, NOT EXISTS, ANY, and ALL makes it easier to filter records based on related data and aggregate calculations. Careful use of parentheses, awareness of NULL behavior, and appropriate indexing help make queries more reliable, readable, and efficient.

Regular practice with employee, customer, order, and product databases will help build the confidence needed to write advanced SQL queries for real-world applications, reporting, analytics, and technical interviews.

35. Learning Resources

• Explore the MySQL Training Course at JustAcademy
• Register for a Course Demo
• MySQL Documentation: WHERE Clause Optimization
• MySQL Documentation: Subqueries
• MySQL Documentation: EXISTS and NOT EXISTS Subqueries
• MySQL Documentation: EXPLAIN