EXIN SQL Foundation common mistakes
EXIN SQL Foundation · Understand the traps and how to avoid them.
Writing NULL comparisons as `WHERE column = NULL` or `WHERE column != NULL`, then assuming the query proved the value is or is not empty.
Candidates treat NULL as an ordinary literal value like 0 or an empty string. Because SQL uses three-valued logic, any comparison with NULL evaluates to UNKNOWN, never TRUE, so the row is silently filtered out and the result set looks deceptively 'correct'.
What to do instead
Always test for the null state with IS NULL / IS NOT NULL. When NULL should be treated as a real value (e.g. in COALESCE or a default), wrap it explicitly and state the intended semantics in the query. Remember NOT IN with a NULL in the list returns no rows.
Confusing WHERE and HAVING, for example filtering on an aggregate with `WHERE COUNT(*) > 5` or putting a row-level condition in HAVING.
Both clauses filter, so candidates reach for whichever they learned first. The logical order of evaluation (FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY) is not the order clauses are written, and this is the usual gap.
What to do instead
Filter individual rows before grouping in WHERE; filter groups after aggregation in HAVING. Practice by writing the logical evaluation order at the top of the query and checking each predicate against it. Note that SELECT aliases are generally not usable in WHERE but often are in HAVING/ORDER BY depending on the product.
Using INNER JOIN when rows from one side with no match must be preserved, or adding a WHERE condition on the outer side that silently turns a LEFT JOIN back into an INNER JOIN.
Candidates equate 'join' with 'inner join' and do not think about unmatched rows. The second error is subtler: a predicate like WHERE b.status = 'active' in the WHERE clause rejects the NULL-extended rows produced by the outer join.
What to do instead
Choose the join type by asking which rows must survive. Put conditions that restrict the optional table in the ON clause of the outer join, not in WHERE. Verify with a small dataset that unmatched rows appear with NULLs.
Writing aggregate queries that select non-aggregated columns not listed in GROUP BY, then trusting the arbitrary value returned.
Some engines (notably older MySQL configurations) permit this, so the query runs without error and the candidate assumes it is valid. The result is non-deterministic and often wrong.
What to do instead
Every selected column must either be inside an aggregate function or appear in GROUP BY. Check by listing the SELECT columns and GROUP BY columns side by side. Use ONLY_FULL_GROUP_BY-style strictness as a mental rule even where the product is lenient.
Using DISTINCT to remove duplicate rows when the duplication actually comes from a join fan-out, and not noticing that DISTINCT can also hide genuinely different rows that differ only in one selected column.
DISTINCT is taught as a quick de-duplication tool. Candidates apply it as a band-aid without diagnosing why duplicates appeared, and they forget DISTINCT applies to the whole selected row, not to a single column.
What to do instead
Diagnose the cause first: check whether the join key is unique on the joined side. If you need one row per entity, aggregate or use a subquery/EXISTS instead. When you need distinct values of one column, select only that column or use GROUP BY.
Assuming a subquery in the SELECT or WHERE list can be treated like a join, or writing a scalar subquery that returns multiple rows and expecting it to work.
Candidates learn subqueries as 'queries inside queries' without distinguishing scalar, row, table, and correlated forms. A scalar subquery must return at most one row and one column, and that constraint is often overlooked.
What to do instead
Classify the subquery before writing it: scalar (one value), multi-row (use IN/ANY/ALL/EXISTS), or table (use in FROM). For correlated subqueries, confirm the outer reference is intentional. Test with data that produces multiple matches to see the error.
Misusing UNION versus UNION ALL, either expecting UNION to preserve duplicates or using UNION ALL and being surprised by repeated rows.
The names suggest a single 'combine' operation. Candidates forget that UNION performs an implicit DISTINCT and therefore adds sorting/deduplication cost, while UNION ALL simply concatenates.
What to do instead
Default to UNION ALL when duplicates are acceptable or impossible, and use UNION only when de-duplication is required. Also confirm column count and compatible data types match across the combined queries, since the column names come from the first SELECT.
Choosing the wrong data type for a column, such as storing dates or numbers in VARCHAR, or using CHAR for variable-length text, and then being unable to sort, compare, or index correctly.
Data types are often skimmed as syntax rather than design decisions. String storage 'works' for insertion, so the consequences only appear later as lexicographic sorting ('10' before '9') and failed range comparisons.
What to do instead
Match the type to the domain: DATE/TIMESTAMP for dates, DECIMAL/NUMERIC for exact money, integer types for identifiers, VARCHAR for variable text, CHAR only for fixed-length codes. Check that comparisons and ORDER BY behave as expected on sample data.
Writing DDL that omits or misplaces constraints: no PRIMARY KEY, a FOREIGN KEY referencing a non-unique column, or expecting a CHECK/NOT NULL constraint to be enforced when it was never declared.
Candidates focus on getting the CREATE TABLE to execute rather than on the integrity rules. Constraint syntax varies by product, and some constraints are only enforced if declared at the right level (column vs table).
What to do instead
Declare PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY and CHECK explicitly and verify them by attempting violating inserts in a scratch database. Know that a foreign key must reference a primary or unique key, and that constraint naming helps later ALTER TABLE operations.
Treating DELETE and TRUNCATE as interchangeable, or running an UPDATE/DELETE without a WHERE clause and committing it.
Both remove rows, but DELETE is DML and can be filtered, rolled back and fire triggers, while TRUNCATE is DDL-like, removes all rows, and resets or bypasses certain logging and identity behaviour. The missing-WHERE error comes from testing on a small table where the damage is invisible.
What to do instead
Use DELETE with a WHERE clause for selective removal and TRUNCATE only when all rows must go and the product's semantics allow it. Before executing, run the same predicate as a SELECT COUNT(*) to preview affected rows, and use transactions with an explicit COMMIT only after verification.