Oracle SQL common mistakes
Oracle SQL · Understand the traps and how to avoid them.
Selecting a non-aggregated column in a query that also contains a GROUP BY clause (e.g., SELECT department_id, last_name, AVG(salary) ... GROUP BY department_id), expecting Oracle to pick an arbitrary row.
Candidates carry over behaviour from MySQL/SQLite where the server silently returns a value from some row; Oracle raises ORA-00979 because the column is neither grouped nor wrapped in an aggregate.
What to do instead
Before running any grouped query, list every non-aggregate expression in the SELECT list and confirm it appears verbatim in GROUP BY (or is functionally dependent on a grouped primary key). If the row-level value is genuinely needed, join the aggregate back as an inline view instead of loosening the GROUP BY.
Filtering an aggregate with WHERE instead of HAVING, e.g. WHERE COUNT(*) > 5 or WHERE AVG(salary) > 5000.
WHERE and HAVING look interchangeable on the page, and candidates write the condition at the same time as they think of the group, forgetting the clause is evaluated before rows are grouped.
What to do instead
Apply the evaluation order rule: FROM -> WHERE (row-level) -> GROUP BY -> HAVING (group-level) -> SELECT -> ORDER BY. Any predicate containing a group function must go in HAVING; a predicate on a plain column that does not depend on the group should stay in WHERE for both correctness and performance.
Writing a left outer join but placing the condition on the outer (optional) table in the WHERE clause, e.g. FROM employees e, departments d WHERE e.department_id = d.department_id(+) AND d.department_name = 'Sales', and then wondering why rows with no department disappeared.
WHERE predicates are applied after the outer join is formed, so a condition on the null-extended side eliminates exactly the rows the outer join was meant to preserve.
What to do instead
Move any filter on the optional table into the join condition itself: use ANSI syntax ON e.department_id = d.department_id AND d.department_name = 'Sales', or attach the (+) to every reference of that table (d.department_name = 'Sales'(+) won't work - instead put the predicate in the outer-join clause). Only filters on the mandatory table belong in WHERE. Test with a row known to have no match.
Using NOT IN with a subquery whose result set may contain NULL, then receiving zero rows instead of the expected rows.
Candidates reason 'not in the list' intuitively, but x NOT IN (1, 2, NULL) evaluates to UNKNOWN for every x, so the whole predicate never becomes TRUE. The NULL usually arrives unnoticed from an outer join or an optional column.
What to do instead
Prefer NOT EXISTS with a correlated subquery, or add IS NOT NULL to the inner query, or use the MINUS set operator. When debugging, always run the subquery alone and check for NULLs before trusting NOT IN.
Answering a set-operator question by adding an ORDER BY to each individual SELECT (SELECT ... ORDER BY 1 UNION SELECT ... ORDER BY 1) or by selecting a different number/type of columns in each branch.
The mental model is 'two separate queries glued together'; in reality UNION/UNION ALL/INTERSECT/MINUS operate on whole result sets, so column counts and compatible data types must match and only one ORDER BY may appear at the very end, referring to column positions or the first SELECT's aliases.
What to do instead
Write each branch with the same column count and compatible types, alias columns only in the first branch, and place a single ORDER BY after the last SELECT. Remember UNION removes duplicates (needs a sort) while UNION ALL does not; MINUS is not commutative, so check the direction asked for.
Assuming a string comparison is case-insensitive or that LIKE is pattern-only, e.g. writing WHERE last_name = 'smith' and getting no rows, or using = '%S%' instead of LIKE '%S%'.
Many candidates come from environments with case-insensitive collations or from languages where equality and pattern matching are blended; Oracle character comparisons are exact and case-sensitive by default.
What to do instead
For case-insensitive matching apply UPPER/LOWER or INITCAP to both sides (or use the NLS settings deliberately). Reserve LIKE for wildcards, escape literal % and _ with ESCAPE, and remember that NULL comparisons need IS NULL - = NULL never matches anything.
Using a column alias in the WHERE clause (WHERE total_salary > 10000 where total_salary is defined only in the SELECT list).
Aliases are assigned during SELECT processing, which happens after WHERE, so the alias does not yet exist; candidates confuse this with ORDER BY, where aliases are allowed.
What to do instead
Repeat the expression in WHERE, or wrap the query in an inline view and filter the alias in the outer query. Keep the rule in mind: aliases are valid in ORDER BY and in the outer query of an inline view, but not in WHERE, GROUP BY or HAVING.
Treating NULL as a value in arithmetic, concatenation and sorting - expecting salary + NULL to equal salary, or expecting NULLs to sort with the low values.
Arithmetic with NULL yields NULL, concatenation with NULL effectively yields the other operand's text, and Oracle's default sort places NULLs last for ascending order, all of which contradict intuitive 'zero-ish' reasoning.
What to do instead
Use NVL, COALESCE or NVL2 to supply a default before doing arithmetic or comparison. For sorting, state the placement explicitly with ORDER BY col ASC NULLS LAST or DESC NULLS FIRST rather than relying on the default.
Applying an implicit conversion and expecting a format that is not the session default, e.g. WHERE hire_date = '12-JUN-2020' failing, or TO_CHAR(date) without a format model returning an unexpected layout; also ROUND vs TRUNC on dates being confused.
Character-to-date conversion uses NLS_DATE_FORMAT, which the candidate cannot see and may differ from the exam environment's assumption; and candidates treat ROUND as if it always rounds up.
What to do instead
Always supply an explicit format model: TO_DATE('12-06-2020','DD-MM-YYYY'), TO_CHAR(hire_date,'YYYY-MM-DD'). For numbers and dates use ROUND (nearest, midnight-based 12-hour cut-off for dates) versus TRUNC (down); verify with SYSDATE arithmetic. Never rely on implicit conversion in a predicate, since it also disables index use.
Issuing a DELETE, UPDATE or TRUNCATE on a table and then discovering the change cannot be undone as expected, or leaving the transaction open, or forgetting that a view with a join/aggregate/group function is not updatable.
TRUNCATE is DDL, implicitly commits, and cannot be rolled back, whereas DELETE is DML and can; candidates blur the two and also assume any view can be the target of an UPDATE. They also forget an uncommitted DML transaction may hold locks and is invisible to other sessions.
What to do instead
Decide explicitly: TRUNCATE when all rows go and no rollback is needed; DELETE ... WHERE when selective and transactional. Savepoint before risky DML and COMMIT or ROLLBACK deliberately. For views, check the data dictionary (or remember the rule): simple views on one table with no group functions, DISTINCT, group by, or expressions are key-preserving and updatable; otherwise update the base table.