Subqueries in WHERE with IN in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Find employees working in departments located in 'New York'

Given the tables employees(emp_id, emp_name, dept_id) and departments(dept_id, dept_name, location), what is the output of the following query?

SELECT emp_name FROM employees WHERE dept_id IN (SELECT dept_id FROM departments WHERE location = 'New York');

PostgreSQL

SELECT emp_name FROM employees WHERE dept_id IN (SELECT dept_id FROM departments WHERE location = 'New York');

A List of employee names who work in departments located in New York

B List of all employee names regardless of location

C Empty result set

D Syntax error due to subquery

💡 Hint

Think about how the subquery filters departments by location and how IN matches dept_id.

The subquery selects dept_id values for departments in New York. The main query returns employees whose dept_id matches any of those values, so only employees in New York departments are listed.

📝 Syntax

intermediate

Identify the syntax error in subquery with IN

Which option contains a syntax error in the use of a subquery with IN in PostgreSQL?

Consider the query:

SELECT product_name FROM products WHERE category_id IN (SELECT category_id FROM categories WHERE category_name = 'Electronics');

PostgreSQL

SELECT product_name FROM products WHERE category_id IN (SELECT category_id FROM categories WHERE category_name = 'Electronics');

A SELECT product_name FROM products WHERE category_id IN (SELECT category_id FROM categories WHERE category_name = 'Electronics');

B SELECT product_name FROM products WHERE category_id IN (SELECT category_id FROM categories WHERE category_name = Electronics);

C SELECT product_name FROM products WHERE category_id = IN (SELECT category_id FROM categories WHERE category_name = 'Electronics');

D SELECT product_name FROM products WHERE category_id IN SELECT category_id FROM categories WHERE category_name = 'Electronics';

💡 Hint

Check the placement of parentheses around the subquery.

Option D is missing parentheses around the subquery, which is required syntax for IN with a subquery. Option C has an invalid '= IN' operator combination. Option B is missing quotes around the string literal 'Electronics'. Only option A is correct syntax.

❓ optimization

advanced

Optimize query using IN with subquery for better performance

You have a query:

SELECT order_id FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE region = 'West');

Which option is the best optimization to improve performance in PostgreSQL?

PostgreSQL

SELECT order_id FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE region = 'West');

A Add an index on orders.order_id

B Rewrite using EXISTS:<br>SELECT order_id FROM orders WHERE EXISTS (SELECT 1 FROM customers WHERE customers.customer_id = orders.customer_id AND region = 'West');

C Rewrite using JOIN:<br>SELECT orders.order_id FROM orders JOIN customers ON orders.customer_id = customers.customer_id WHERE customers.region = 'West';

D Rewrite using NOT IN instead of IN

💡 Hint

Consider how JOINs can be more efficient than IN subqueries in some cases.

Option C uses a JOIN which often performs better than IN with a subquery because it allows the database to optimize the join operation. Option C with EXISTS can be efficient but may not always outperform JOIN. Option C adds an index on order_id which is not relevant for filtering by customer_id. Option C changes logic and is incorrect.

🔧 Debug

advanced

Debug why query returns empty result with IN subquery

Given the query:

SELECT student_name FROM students WHERE class_id IN (SELECT class_id FROM classes WHERE teacher = 'Mr. Smith');

The query returns no rows, but you know students exist in classes taught by Mr. Smith. What is the most likely cause?

PostgreSQL

SELECT student_name FROM students WHERE class_id IN (SELECT class_id FROM classes WHERE teacher = 'Mr. Smith');

A The teacher name in classes table is stored with trailing spaces

B The subquery returns NULL values causing IN to fail

C The students table has no class_id column

D The query syntax is invalid

💡 Hint

Think about string matching and how trailing spaces affect equality.

If the teacher names have trailing spaces, the equality condition in the subquery fails to match 'Mr. Smith' exactly, so the subquery returns no rows. This causes the main query to return no students. Option A is unlikely because NULLs in subquery do not cause empty results with IN. Option A would cause a syntax error. Option A is false because the query is valid.

🧠 Conceptual

expert

Understanding NULL behavior in IN subqueries

Consider the query:

SELECT product_name FROM products WHERE category_id IN (SELECT category_id FROM categories WHERE discontinued IS NULL);

What will be the result if the subquery returns some NULL values along with valid category_ids?

PostgreSQL

SELECT product_name FROM products WHERE category_id IN (SELECT category_id FROM categories WHERE discontinued IS NULL);

A The query raises an error due to NULL values in subquery

B The query returns no rows because NULL in subquery causes IN to fail

C The query returns all products regardless of category_id

D The query returns all products with category_id matching non-NULL category_ids only

💡 Hint

Remember how NULLs are treated in IN conditions in SQL.

In SQL, NULL values in the list for IN are ignored when matching. The query returns products whose category_id matches any non-NULL category_id returned by the subquery. It does not return all products or fail due to NULLs. No error is raised.