Subqueries in WHERE clause in MySQL - 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 conditions from the inner query.

beginner

How does a subquery in the WHERE clause work?

The main query uses the result of the subquery to decide which rows to include, like checking if a value matches any value returned by the subquery.

beginner

Example: What does this query do?<br>

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

It finds employees who work in departments located in New York by using a subquery to get department IDs in NY and filtering employees by those IDs.

intermediate

Can subqueries in WHERE clause return multiple rows?

Yes, subqueries can return multiple rows when used with operators like IN, ANY, or EXISTS to check conditions against many values.

intermediate

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

If the subquery returns no rows, the condition usually evaluates to false, so the main query returns no matching rows for that condition.

What does a subquery in the WHERE clause do?

A Filters rows based on the result of another query

B Creates a new table

C Deletes rows from a table

D Updates values in a table

A subquery in the WHERE clause filters rows by using the result of the inner query to decide which rows to keep.

Which operator is commonly used with subqueries returning multiple rows in WHERE clause?

A IN

B LIKE

C BETWEEN

D IS NULL

The IN operator checks if a value matches any value in the list returned by the subquery.

What will happen if the subquery in WHERE clause returns no rows?

A The main query returns all rows

B The main query returns no rows for that condition

C The main query throws an error

D The main query ignores the condition

If the subquery returns no rows, the condition is false, so no rows match that filter.

Which keyword can be used to check if a subquery returns any rows in WHERE clause?

A GROUP BY

B COUNT

C ORDER BY

D EXISTS

EXISTS checks if the subquery returns at least one row and returns true or false.

In this query, what does the subquery return?<br>SELECT * FROM orders WHERE customer_id = (SELECT id FROM customers WHERE name = 'Alice');

A The name of the customer

B All customers

C The id of customer named Alice

D All orders

The subquery returns the id of the customer named Alice to filter orders for that customer.

Explain how a subquery in the WHERE clause helps filter data in a main query.

Think about how the inner query result guides which rows to keep.

Describe what happens when a subquery in the WHERE clause returns multiple rows versus a single row.

Consider how the main query compares values from the subquery.