Why does adding count(*) to a select statement force a row to exist in a subquery?

Question

On Oracle 9i, why does the following produce the result 'abc'

select 'abc ' || (select txt from 
     (select 'xyz' as txt from dual where 1=2)) 
from dual

while this produces 'abc xyz':

select 'abc ' || (select txt from 
     (select count(*), 'xyz' as txt from dual where 1=2)) 
from dual

Why does adding count(*) to the subquery result in different behavior? Should the predicate where 1=2 preclude any results in the subquery?

Answer 1

select count(*) from dual where 1=2

returns 0. That is, a row with the value zero.

Answer 2

It's returning the count of everything in the subquery, which is correctly 0. Using aggregate functions always (and correctly) behaves this way and is part of the SQL standard.

Answer 3

count will always return a numeric value, 0 or a positive integer, so you will always have one row in your result set.

Note that the other aggregate functions might return NULL

Answer 4

Understanding how aggregate functions operate in SQL is critical to writing correct queries. When adding an aggregate function (like sum, avg, min, max, count) to a query, you are asking the database to perform a group operation on a set of results. Most aggregates, like min and max, will return null when presented with an empty set of rows to operate on. The exception to this is count() - it (correctly) returns 0 when presented with an empty set or rows.

This question arose from the analysis of a much more complex query - one with multiple subquery expressions in the select clause. As it turns out, the addition of count(*) in the select expression caused some havoc to the results - as it started returning a value where none was expected.

What the developer really wanted, was a case where count(*) would produce null. The easiest way to achieve this is through the use of analytics in Oracle. The query can be written to use the analytic count equivalent: count(*) over ()

select 'abc ' || (select txt from 
     (select count(*) over (), 'xyz' as txt from dual where 1=2)) 
from dual

Answer 5

 (select 'xyz' as txt from dual where 1=2))

This sub-query DOES NOT RETURN ANY ROWS.

 (select count(*), 'xyz' as txt from dual where 1=2))

This subquery returns 1 rows ALL THE TIME.

That is the reason of the different behaviour.