Tutorial :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?


Solution:1

select count(*) from dual where 1=2  

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


Solution: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.


Solution: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


Solution: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  


Solution: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.


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