What is "Select -1", and how is it different from "Select 1"?

Question

I have the following query that is part of a common table expression. I don't understand the function of the "Select -1" statement. It is obviously different than the "Select 1" that is used in "EXISTS" statements. Any ideas?

  select days_old, 
         count(express_cd),
         count(*),  
         case 
           when round(count(express_cd)*100.0/count(*),2) < 1 then '0'      
           else '' 
         end ||
           cast(decimal(round(count(express_cd)*100.0/count(*),2),5,2) as varchar(7)) || 
           '%'   
  from foo.bar   
  group by days_old   
  union all  
  select -1, -- Selecting the -1 here
         count(express_cd),
         count(*),   
         case 
           when round(count(express_cd)*100.0/count(*),2) < 1 then '0' 
           else ''
         end ||
           cast(decimal(round(count(express_cd)*100.0/count(*),2),5,2) as varchar(7)) || 
           '%'  
  from foo.bar   
  where days_old between 1 and 7

Answer 1

Based on your query, all the records with days_old between 1 and 7 will be output as '-1', that is what select -1 does, nothing special here and there is no difference between select -1 and select 1 in exists, both will output the records as either 1 or -1, they are doing the same thing to check whether if there has any data.

Back to your query, I noticed that you have a union all and compare each four columns you select connected by union all, I am guessing your task is to get a final result with days_old not between 1 and 7 and combine the result with day_old, which is one because you take all between 1 and 7.

Answer 2

It's just selecting the number "minus one" for each row returned, just like "select 1" will select the number "one" for each row returned.

There is nothing special about the "select 1" syntax uses in EXISTS statements by the way; it's just selecting some random value because EXISTS requires a record to be returned and a record needs data; the number 1 is sufficient.

Why you would do this, I have no idea.

Answer 3

When you have a union statement, each part of the union must contain the same columns. From what I read when I look at this, the first statement is giving you one line for each days old value and then some stats for each day old. The second part of the union is giving you a summary of all the records that are only a week or so less. Since days old column is not relevant here, they put in a fake value as a placeholder in order to do the union. OF course this is just a guess based on reading thousands of queries through the years. To be sure, I would need to actually run teh code.

Since you say this is a CTE, to really understand why this is is happening, you may need to look at the data it generates and how that data is used in the next query that uses the CTE. That might answer your question.

What you have asked is basically about a business rule unique to your company. The true answer should lie in any requirements documents for the original creation of the code. You should go look for them and read them. We can make guesses based on our own experience but only people in your company can answer the why question here.

If you can't find the documentation, then you need to talk (Yes directly talk, preferably in person) to the Stakeholders who use the data and find out what their needs were. Only do this after running the code and analyzing the results to better understand the meaning of the data returned.

Answer 4

It is just a grouping logic there.

Your query returns aggregated data (counts and rounds) grouped by days_old column plus one more group for data where days_old between 1 and 7. So, -1 is just another additional group there, it cannot be 1 because days_old=1 is an another valid group.

result will be like this:

row1: days_old=1 count(*)=2 ...

row2: days_old=3 count(*)=5 ...

row3: days_old=9 count(*)=6 ...

row4: days_old=-1 count(*)=7