1Z0-149: Using Subqueries to Solve Queries

What this domain tests

Subqueries solve queries in stages. The exam tests single-row and multi-row subqueries, correlated subqueries, EXISTS, and inline views.

Core rules

• A single-row subquery returns at most one row and can use =, >, <, and similar operators.
• A multi-row subquery returns several rows and must use IN, ANY, or ALL, or a set-comparison context.
• A correlated subquery references a column from the outer query and is evaluated for each outer row.
• EXISTS tests whether the subquery returns at least one row. NOT EXISTS is often safer than NOT IN when NULLs are present.
• A scalar subquery in the select list must return one value per outer row; if it returns no rows, the result is NULL.
• A subquery in FROM is an inline view and must have an alias. WITH defines a named subquery block.
• Subqueries may appear in WHERE, HAVING, FROM, and the select list.

Worked example

SELECT last_name FROM employees e WHERE EXISTS (SELECT 1 FROM departments d WHERE d.department_id = e.department_id AND d.location_id = 1700);

The subquery is correlated through e.department_id. For each employee, Oracle checks whether a matching department in location 1700 exists. EXISTS stops at the first match, and the select list of the subquery does not affect the result.

Traps

• Using = with a subquery that returns multiple rows.
• Using NOT IN when the subquery can return NULL; use NOT EXISTS or add IS NOT NULL.
• Forgetting the correlation condition and turning a correlated subquery into an uncorrelated one.
• Assuming a scalar subquery returning no rows causes an error; it returns NULL.
• Omitting the alias for an inline view.