Impara Nested Subqueries with IN, ANY, and ALL | Nested Subqueries in the WHERE section

Scorri per mostrare il menu

When you need to filter results in SQL based on values from another table or query, nested subqueries in the WHERE clause become very powerful. The IN, ANY, and ALL operators let you compare a column's value to a set of results produced by a subquery, allowing for flexible and expressive filtering. Each operator serves a different purpose, and understanding how to use them with nested subqueries will help you write advanced queries that answer complex business questions.


              1234567891011121314151617
            
-- Find employees assigned to projects managed by the 'Engineering' department

SELECT e.employee_id, e.first_name, e.last_name
FROM employees e
WHERE e.employee_id IN (
    SELECT ep.employee_id
    FROM employee_projects ep
    JOIN projects p ON ep.project_id = p.project_id
    WHERE p.project_id IN (
        SELECT ep2.project_id
        FROM employee_projects ep2
        JOIN employees m ON ep2.employee_id = m.employee_id
        WHERE m.department_id = (
            SELECT department_id FROM departments WHERE department_name = 'Engineering'
        )
    )
);

The code above uses the IN operator with a nested subquery to find employees who are assigned to projects managed by someone from the Engineering department. The subquery returns the employee_id values of employees working on those projects, and the outer query filters the employees table to only those whose IDs are in the returned list.

The IN, ANY, and ALL operators each offer different ways to compare values from your main query to the results of a subquery:

IN checks if a value matches any value in the subquery result set;
ANY (or its synonym SOME) compares a value to any value returned by the subquery using an operator you specify (such as >, <, or =);
ALL requires that a comparison is true for every value returned by the subquery.

Use IN when you want to match against a list of possible values. Use ANY when you want to check if a condition is true for at least one value in the subquery. Use ALL when you want to enforce a condition for all values in the subquery.


              1234567891011
            
-- Find employees whose salary is higher than all employees in the 'Sales' department

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary
    FROM employees
    WHERE department_id = (
        SELECT department_id FROM departments WHERE department_name = 'Sales'
    )
);

1. Which of the following best describes the effect of using `IN` with a subquery in the `WHERE` clause?

2. What is the main difference between using `ANY` and `ALL` with a nested subquery?

3. Fill in the blanks to complete the query that selects employees whose salary is higher than all employees in the 'Marketing' department:

Tutto è chiaro?

Grazie per i tuoi commenti!

Sezione 1. Capitolo 5

Chieda ad AI

Chieda pure quello che desidera o provi una delle domande suggerite per iniziare la nostra conversazione

Sezione 1. Capitolo 5

Nested Subqueries with IN, ANY, and ALL

1. Which of the following best describes the effect of using IN with a subquery in the WHERE clause?

2. What is the main difference between using ANY and ALL with a nested subquery?

3. Fill in the blanks to complete the query that selects employees whose salary is higher than all employees in the 'Marketing' department:

1. Which of the following best describes the effect of using `IN` with a subquery in the `WHERE` clause?

2. What is the main difference between using `ANY` and `ALL` with a nested subquery?