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


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?


select count(*) from dual where 1=2  

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


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.


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


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  


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


 (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.

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »