Advanced Querying and Database Objects
What the domain covers
This domain tests multi-table retrieval and schema objects that support it: inner, outer and self joins, subqueries, set operators, views, indexes and sequences. Exam items ask you to choose the join that preserves unmatched rows, predict duplicate behavior, and explain when an index or view helps.
Core rules
- INNER JOIN returns matching pairs. LEFT JOIN keeps all left rows and NULL-extends unmatched right columns; RIGHT JOIN is the mirror; FULL JOIN keeps both sides. CROSS JOIN is a Cartesian product. A self join uses two aliases of one table.
- Join conditions belong in ON. Filters on the preserved side belong in WHERE, but filters on the optional side usually belong in ON to avoid turning an outer join into an inner join.
- Subqueries can be scalar (one value), row, table or correlated. EXISTS and NOT EXISTS test presence and handle NULLs more safely than IN and NOT IN.
- UNION combines and removes duplicates; UNION ALL keeps duplicates and is faster. INTERSECT returns common rows; EXCEPT (MINUS in Oracle) returns rows in the first query not in the second.
- A view is a stored query; it can simplify access and restrict columns, but it is not a copy of data. An index speeds reads but adds write overhead and storage. A sequence generates numeric values, commonly for surrogate keys.
Worked example
SELECT d.department_name, e.last_name FROM department d LEFT JOIN employee e ON e.department_id = d.department_id WHERE e.last_name IS NULL;
Reasoning: the LEFT JOIN keeps every department, even those with no employees. Unmatched employee columns become NULL. The WHERE predicate keeps only departments with no matching employee, which is an anti-join. If the predicate e.last_name = 'Smith' were placed in WHERE, departments without a Smith would be removed, effectively behaving like an inner join. NOT EXISTS is an alternative that avoids NULL pitfalls.
Traps that cost marks
- Using NOT IN against a subquery that can return NULL; the result may be empty. Prefer NOT EXISTS.
- Assuming UNION ALL removes duplicates; it does not.
- Placing a filter on the outer-joined table in WHERE and losing unmatched rows.
- Expecting an index to help every query; low-selectivity columns and functions on indexed columns can prevent its use.
- Treating a view as materialized data; unless it is a materialized view, it re-runs the query.