Subquery with IN operator in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the IN operator do in SQL when used with a subquery?

The IN operator checks if a value matches any value in the list returned by the subquery. It helps filter rows based on whether a column's value exists in the subquery result.

beginner

Write a simple example of a subquery with IN operator to find employees who work in departments located in 'New York'.

SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

intermediate

Can the subquery used with IN operator return multiple columns?

No, the subquery used with IN operator must return only one column. This column's values are compared against the outer query's column values.

intermediate

What happens if the subquery with IN operator returns no rows?

If the subquery returns no rows, the IN condition evaluates to false for all rows in the outer query, so no rows are returned.

intermediate

Why might you use a subquery with IN operator instead of a JOIN?

Using IN with a subquery can be simpler and more readable when you only need to check existence of values. JOINs are better for combining columns from multiple tables.

What must a subquery used with the IN operator return?

A No rows

B Multiple columns of values

C A single row with multiple columns

D A single column of values

The subquery must return a single column of values to compare with the outer query's column.

If the subquery returns no rows, what will the IN operator do?

A Return all rows from the outer query

B Cause an error

C Return no rows from the outer query

D Return NULL values

If the subquery returns no rows, the IN condition is false for all rows, so no rows are returned.

Which SQL keyword is used to check if a value exists in a list returned by a subquery?

A IN

B EXISTS

C JOIN

D LIKE

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

Which of these is a valid use of IN with a subquery?

A SELECT * FROM employees WHERE department_id IN (SELECT department_id, location FROM departments);

B SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments);

C SELECT * FROM employees WHERE department_id IN (SELECT location FROM departments);

D SELECT * FROM employees WHERE department_id IN ();

The subquery must return a single column; option A is valid.

Why might you choose IN with a subquery over a JOIN?

A IN is simpler when only checking existence of values

B IN returns more columns

C JOIN cannot combine tables

D IN is faster for all queries

IN with a subquery is simpler and more readable when just checking if values exist.

Explain how the IN operator works with a subquery in SQL.

Think about checking if a value is inside a list of values.

Describe a scenario where using a subquery with IN operator is helpful.

Imagine you want to find employees working in certain departments.