Lära Introduction to Nested Subqueries in WHERE | Nested Subqueries in the WHERE section

Svep för att visa menyn

Nested subqueries are a powerful feature in SQL that allow you to use the result of one query as a condition for another. When you place a subquery inside the WHERE clause, you can filter data in the outer query based on information calculated or retrieved from another table or set of rows. This approach is especially useful when you need to answer questions that depend on multiple tables or need to consider aggregate information, such as totals or counts, that cannot be easily joined or filtered otherwise.


              123456789
            
-- Find employees who work in departments with more than 10 people
SELECT first_name, last_name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM employees
    GROUP BY department_id
    HAVING COUNT(*) > 10
);

The logic behind this query unfolds in two steps. First, the subquery inside the WHERE clause selects all department_id values from the employees table where the number of employees in each department is greater than 10. This is achieved by grouping employees by their department_id and applying the HAVING COUNT(*) > 10 condition. The outer query then retrieves the first_name and last_name of employees whose department_id matches any of the IDs returned by the subquery. In essence, only employees in large departments—those with more than 10 employees—are included in the result set.


              123456789101112
            
-- List employees who participate in the 'Cloud Migration' project
SELECT first_name, last_name
FROM employees
WHERE employee_id IN (
    SELECT employee_id
    FROM employee_projects
    WHERE project_id = (
        SELECT project_id
        FROM projects
        WHERE project_name = 'Cloud Migration'
    )
);

1. Why would you use a subquery in the WHERE clause of an SQL statement?

2. What does the subquery provide to the outer query in the following statement?

3. Fill in the blanks to select all employees who work in the 'Sales' department using a subquery in the WHERE clause.

Var allt tydligt?

Tack för dina kommentarer!

Avsnitt 1. Kapitel 1

Fråga AI

Fråga vad du vill eller prova någon av de föreslagna frågorna för att starta vårt samtal

Avsnitt 1. Kapitel 1