Deriving data from nothing?

Ach · #1 (**permalink**) 01-07-05, 12:54

Hi all,
I did something like this and it gave me the result as 123456.please look at this and tell me how does something be derived from a blank table(set)?

Code:

  create table a ( id integer);
  
  select case when 1 = 1 
    then 123456
    else count(*) end as NUMBER
    from a
    where 1 = 0;

I tried this on mysql and one of my friends -who had told me about this- says it works same on sql server.
PM: My friend said that he saw this in one of Joe Celko's columns but I could not find that column.
-Thanks in advance

Pat Phelan · #2 (**permalink**) 01-07-05, 13:12

This is one of those things that doesn't make any sense at first, but once you work through it, it makes perfect sense.

The use of Count(*) within the column list implies grouping. That forces a result set row to be returned at the aggregate level. The WHERE clause is evaluated after the (degenerate case) join, and filters out any rows that might be in the table. After the aggregation, the CASE is evaluated and the result is posted.

Funky, but it does make sense!

-PatP

Internet Services

Web Development

Digital Marketing

Consumer Tech