Why subqueries nest queries in MySQL - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why do subqueries nest queries?

Which of the following best explains why subqueries are used to nest queries in SQL?

A To speed up the database by running multiple queries at the same time.

B To create multiple tables from a single query automatically.

C To allow a query to use the result of another query as input for filtering or calculation.

D To avoid writing any conditions in the main query.

💡 Hint

Think about how one query can depend on the result of another.

Subqueries nest queries so that the outer query can use the results of the inner query to filter or calculate data. This allows complex data retrieval in steps.

❓ query_result

intermediate

Output of nested subquery filtering

Given the tables employees(id, name, department_id) and departments(id, name), what is the output of this query?

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

A Names of employees who work in the Sales department.

B Names of all employees regardless of department.

C Names of departments named Sales.

D An error because subqueries cannot be used in WHERE clauses.

💡 Hint

Look at how the inner query selects department IDs for Sales.

The subquery finds the IDs of departments named 'Sales'. The outer query then selects employees whose department_id matches those IDs, so it returns employees in Sales.

📝 Syntax

advanced

Identify the syntax error in this nested subquery

Which option correctly fixes the syntax error in this query?

SELECT name FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales' AND;

A SELECT name FROM employees WHERE department_id = SELECT id FROM departments WHERE name = 'Sales';

B SELECT name FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales' OR);

C SELECT name FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales' AND);

D SELECT name FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales');

💡 Hint

Check for incomplete conditions and missing parentheses.

The original query ends with an incomplete AND condition, causing syntax error. Option D completes the subquery properly with a closing parenthesis and no incomplete condition.

❓ optimization

advanced

Optimizing nested subqueries for performance

Which option is the best way to optimize this nested subquery for better performance?

SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE name LIKE '%Sales%');

A Use a subquery with DISTINCT to remove duplicates.

B Replace the subquery with a JOIN between employees and departments on department_id and id.

C Add an ORDER BY clause inside the subquery.

D Use a UNION instead of IN.

💡 Hint

Think about how JOINs can be more efficient than IN with subqueries.

JOINs often perform better than IN with subqueries because the database can optimize the join operation better, especially with indexes.

🔧 Debug

expert

Debugging unexpected results from nested subqueries

Why does this query return no rows even though there are employees in the 'Marketing' department?

SELECT name FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = 'Marketing');

A The subquery returns multiple rows, causing the '=' operator to fail.

B The employees table has no matching department_id values.

C The departments table has no 'Marketing' department.

D The query syntax is invalid.

💡 Hint

Check if the subquery returns more than one value when using '='.

If the subquery returns multiple department IDs, using '=' causes an error or no results. Using IN instead of '=' fixes this.