SQL Subqueries: Multiple Row Subqueries Explained

Write a Query Using IN

Write an SQL query to find all employees who work in departments located in 'Chicago' using a multiple row subquery with IN.

Expected output: List of employee names working in Chicago departments.

Hint: Use a subquery to select department IDs from departments where location is 'Chicago'.

Use EXISTS to Find Departments

Write a query to find departments that have employees earning more than $80,000 using EXISTS.

Expected output: List of department names with high-earning employees.

Hint: Correlate the subquery with the outer query on department_id.

Compare Salaries Using ANY

Find employees whose salary is less than any employee in the 'Marketing' department using ANY.

Expected output: List of employee names with salary less than some Marketing employees.

Hint: Use a subquery to get salaries from Marketing department.

A multiple row subquery is a subquery that returns more than one row of results, typically used with operators like IN, ANY, ALL, or EXISTS to filter data.

Use EXISTS when you want to check if the subquery returns any rows, often to test for the existence of related data without caring about the actual values.

Using = with a subquery that returns multiple rows causes an error because = expects a single value, not a list.