Subquery in WHERE clause in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a subquery in the WHERE clause?

A subquery in the WHERE clause is a query inside another query that helps filter results based on values returned by the inner query.

beginner

How does a subquery in the WHERE clause work?

The outer query uses the result of the subquery to decide which rows to include. The subquery runs first and returns values that the outer query compares against.

beginner

Example: SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'New York');<br>What does this query do?

It finds all employees who work in departments located in New York. The subquery finds department IDs in New York, and the outer query selects employees in those departments.

intermediate

Can a subquery in the WHERE clause return multiple values?

Yes, subqueries can return multiple values when used with operators like IN, ANY, or ALL to compare against multiple results.

intermediate

What is the difference between using '=' and 'IN' with a subquery in the WHERE clause?

'=' expects the subquery to return a single value, while 'IN' allows the subquery to return multiple values to match any of them.

What does a subquery in the WHERE clause do?

A Creates a new table

B Filters rows based on values returned by the inner query

C Updates data in the database

D Deletes rows from a table

A subquery in the WHERE clause filters rows by using values returned from the inner query.

Which operator allows a subquery to return multiple values in the WHERE clause?

A =

B BETWEEN

C IN

D LIKE

The IN operator allows the subquery to return multiple values to match any of them.

What happens if a subquery in the WHERE clause returns no rows?

A The outer query returns no rows

B The outer query returns all rows

C The database throws an error

D The subquery runs again

If the subquery returns no rows, the outer query finds no matching rows and returns no results.

Which of these is a valid use of a subquery in the WHERE clause?

A SELECT * FROM products WHERE price IN 'SELECT price FROM products';

B SELECT * FROM products WHERE price = 'SELECT AVG(price) FROM products';

C SELECT * FROM products WHERE price > ALL 'SELECT price FROM products';

D SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);

Option D correctly uses a subquery to compare price to the average price.

Can a subquery in the WHERE clause reference columns from the outer query?

A Yes, this is called a correlated subquery

B No, subqueries cannot reference outer query columns

C Only if the subquery is in the SELECT clause

D Only if the subquery uses JOIN

A correlated subquery references columns from the outer query and runs once per outer row.

Explain how a subquery in the WHERE clause helps filter data with an example.

Think about how the inner query returns values that the outer query uses to decide which rows to keep.

Describe the difference between using '=' and 'IN' with subqueries in the WHERE clause.

Consider what happens if the subquery returns one value or many values.