[Solved] Which of the following SQL queries correctly uses a subquery in the WHERE clause to filter results? — Ans: SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments... | SQL

SQL - Subqueries

Which of the following SQL queries correctly uses a subquery in the WHERE clause to filter results?

ASELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'NY');

BSELECT name FROM employees JOIN departments ON employees.department_id = departments.id WHERE location = 'NY';

CSELECT name FROM employees WHERE department_id = departments.id;

DSELECT name FROM employees WHERE EXISTS departments WHERE location = 'NY';

Step-by-Step Solution

Solution:

Step 1: Check subquery syntax
SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'NY'); uses a subquery inside IN() correctly to filter employees by department location.
Step 2: Validate other options
The JOIN query uses JOIN syntax, not subquery; the query with WHERE department_id = departments.id; is invalid syntax; the EXISTS query lacks proper subquery syntax.
Final Answer:
SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'NY'); -> Option A
Quick Check:
Correct subquery syntax = SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'NY'); [OK]

Quick Trick: Use IN (subquery) for filtering with subqueries [OK]

Common Mistakes:

MISTAKES

Master "Subqueries" in SQL

9 interactive learning modes - each teaches the same concept differently

More SQL Quizzes

Which of the following SQL queries correctly uses a subquery in the WHERE clause to filter results?