SQL Articles

Short, focused explainers on the SQL concepts that come up in real work and interviews — usually two things that look similar but behave differently. Each article has a worked example you can run yourself in the free SQL editor.

ROW_NUMBER vs RANK vs DENSE_RANK

The three ranking window functions, side by side — the entire difference comes down to how each one handles ties.

IN vs EXISTS

How they differ, and the NOT IN / NULL trap that can silently return zero rows from a query that looks correct.

WHERE vs HAVING

Why WHERE can't use aggregate functions, when HAVING is actually needed, and the most common mistake to avoid.

LEFT JOIN vs INNER JOIN

The core difference, plus the classic WHERE-clause trap that silently turns a LEFT JOIN back into an INNER JOIN.

SQL NULL Handling

Why col = NULL never matches, and where NULL quietly changes the behaviour of aggregates, NOT IN, and sorting.

Second Highest Salary in SQL

The classic interview question, solved 4 ways — plus the tie-handling ambiguity most answers miss entirely.

DISTINCT vs GROUP BY

When the two produce identical results, where they diverge, and why COUNT(DISTINCT x) is a different thing entirely.

Clustered vs Nonclustered Index

The physical storage difference in SQL Server/MySQL, why nonclustered lookups cost more, and how Oracle/Postgres compare.

PostgreSQL vs Oracle Differences

Licensing, PL/pgSQL vs PL/SQL, identifier case-folding, and the NULL-concatenation trap that catches migrators off guard.

Why Isn't My Index Being Used?

Seven common causes — functions on the column, implicit type conversion, leading wildcards, stale statistics, and more.

Oracle Bitmap Index vs B-tree Index

Cardinality, star-query optimisation, and the concurrent-DML locking gotcha that rules bitmap out for OLTP tables.

Composite Index Column Order

The leftmost-prefix rule, and the equality-before-range heuristic for ordering a multi-column index.

Oracle Function-Based Index

Indexing UPPER(last_name) for case-insensitive search — and the exact-expression-match rule that decides if it's used.

SQL FULL OUTER JOIN Example

Every row from both tables, matched where possible — and when to reach for it over LEFT/RIGHT JOIN.

SQL Anti-Join

NOT EXISTS vs LEFT JOIN + IS NULL vs NOT IN, compared — and why NOT EXISTS should usually be your default.

Oracle (+) Outer Join Syntax

Oracle's legacy outer-join notation explained, with its direct ANSI JOIN equivalent.

Oracle UNDO vs REDO

What each one stores, why the database needs both, and the gotcha that undo itself generates redo.

Oracle SGA vs PGA

Shared instance memory vs private session memory — what's inside each, and how each is sized.

How to Kill a Session in Oracle

The V$SESSION query to find a stuck or blocking session, and the DISCONNECT SESSION fallback.

Oracle PDB/CDB Architecture

Oracle's multitenant model — one container database hosting many pluggable databases — explained.

We Analyzed Our Own 780 SQL Questions

Original data from SQLMentor's own certification and quiz question bank — what topics dominate each exam, and a real answer-key bias we found and are fixing.

Looking for more structured learning instead? Start with the Oracle SQL tutorial, work through the 30 practice exercises, or bookmark the Oracle SQL cheat sheet.