Why Two Oracle Counts Can Disagree: Şevket Ayaksız Oracle Guides on NULL and Ties

Two Oracle SQL queries can both run correctly and still return different totals. The reason is often not an error in arithmetic but a decision hidden inside the query: whether NULL values should count, how unknown values affect exclusions, or what should happen when several rows share the same ranking value.

The Oracle material available through Sevket Ayaksız Oracle covers focused subjects such as Oracle SQL NULL behavior, Top-N queries, transactions, bind variables and execution plans. Şevket Ayaksız also publishes short English guides and technology material about Java, Adobe and Microsoft topics on sevketayaksiz.org.

Why can two Oracle counts disagree?

COUNT(*) and COUNT(column) do not answer exactly the same question. COUNT(*) counts rows. COUNT(column) counts rows where the selected expression is not NULL. If a table has ten records but only seven contain a value in one column, the two expressions can return ten and seven.

Both results may be valid because NULL is not the same thing as zero. Zero can be a stored value with a specific meaning, while NULL represents the absence of a known value. Treating NULL as zero without a clear rule can therefore change the meaning of a report.

The important step is to define what the count represents. If the requirement is the total number of records, row counting is appropriate. If the requirement is the number of records with a known measurement, counting the relevant expression answers a different question.

What happens when NULL enters an exclusion?

NULL can also affect exclusion filters. A NOT IN condition may look like a simple instruction to remove matching values, but comparisons involving NULL can produce an unknown result. If NULL appears in the exclusion data, the final result can differ sharply from what a developer expects from ordinary true and false logic.

The first decision should concern the data itself. If NULL should never appear in the compared values, its presence may indicate a data-quality issue. If NULL is valid, the query needs an explicit rule for how that missing value should influence the result. Changing syntax before deciding what NULL means can hide the actual business question.

How do Top-N functions treat ties?

Top-N queries create another hidden choice. A request such as “return the two newest records” sounds precise until several rows share the same timestamp. Oracle analytic functions can express different interpretations of that request.

Function	Tie behavior	Top-N meaning
ROW_NUMBER	Assigns a unique sequence number to every row	Supports a fixed number of returned rows
RANK	Gives tied rows the same rank and may leave gaps	The next distinct value can receive a later rank
DENSE_RANK	Gives tied rows the same rank without gaps	Can include all rows in the selected ranked values

Consider timestamps of 10:00, 10:00 and 09:59. ROW_NUMBER can assign positions one, two and three, so a filter for the first two positions returns two rows. RANK can assign one, one and three. DENSE_RANK can assign one, one and two, which allows all three rows to belong to the first two distinct timestamp groups.

The function therefore defines what “top” means. When ROW_NUMBER is used and several rows have the same primary ordering value, another meaningful ordering column may be needed to make the row sequence deterministic.

Where should COMMIT be placed?

Transaction handling contains a similar decision. COMMIT determines which database changes become permanent together. If a business operation requires several related changes, committing after an early step can leave part of the operation permanent even if a later required step fails.

The transaction guidance connects COMMIT to the business boundary. The developer needs to decide which changes represent one complete operation and control the transaction around that boundary. Oracle DDL also involves implicit commit behavior, so schema-changing statements need to be considered separately when transaction boundaries matter.

What do bind variables change?

Bind variables separate the structure of an SQL statement from the values supplied when it runs. An application can keep the statement structure stable while passing changing values through the appropriate interface.

The guide connects this approach with statement reuse and parsing behavior. That does not support a fixed performance claim for every query. The useful point is that bind variables separate changing inputs from the SQL structure that consumes them.

What can an execution plan tell you?

Execution plans also require interpretation rather than automatic labels. A full table scan is not always evidence of a bad query, and the use of an index does not prove that a statement is efficient. The meaningful comparison is between what the query asks for, the operations Oracle chooses and the runtime evidence available.

A difference between estimated and observed row counts can justify further investigation, but it does not by itself identify a guaranteed fix. The plan is evidence about how Oracle approaches a statement, not a simple checklist of good and bad operations.

Which decision should come first?

The common thread across these Oracle topics is that syntax often exposes a decision that should be made explicitly. Decide whether the requirement concerns all rows or only known values. Decide how NULL should behave in exclusions. Decide whether Top-N means a fixed number of rows or complete tie groups. Decide which database changes form one transaction.

Once those choices are clear, the SQL becomes easier to interpret. Two Oracle counts can disagree without either query being malformed, just as two ranking functions can return different results without either being used incorrectly. The difference becomes understandable when the rule behind the result is made visible.