What does select 1 do?

Question

I have some code and wanted to make it a learning experience. I would like to ask what a select 1 does?

The first 5 lines look like someone is using a calendar function to get the yr, fiscal quarter, fiscal week then extract records if they exist in a table. I am not sure about what you would get other than you would get the yr, quarter, and week information and a column of 1's where the select 1 inner query found something. Could someone please help me with my interpretation of what is being returned? Thank you in advance. Oh, i should note that the calendar function is probably being used to truncate extra data that is returned from using the calendar function. Someone probably just wants something like June 1, 2016 rather then the minutes and seconds displayed.

Here is the code snippet:

select calendar, fiscal_yr, fiscal_qtr, fiscal_week from dim_date_weeks
Where exists (select 1 from dim_date cw
    where cw.calendar_date_Dateadd(dd, datediff(dd, 0, getdate()), 0)
    and weeks.fin_qtr=cq.fin_qtr
        and weeks.fiscalweek= cw.fiscal_week
    )
union
select dlw.calendar_date, dlw.fiscalyr, dlw.fin_qtr,fiscal_week from dim_date dlw join
(select top 1 lw.fiscal_yr, lw.fin_qtr, fiscal_week from dim_date lw
    where lw.calendar=date < (select min(calendar_date)from dim_date_weeks
                        where exists (select 1 from dim_date cw
                                where date_Dateadd(dd, datediff(dd, 0, getdate()), 0)
                                and weeks.fin_qtr=cq.fin_qtr
                                    and weeks.fiscalweek= cw.fiscal_week
                                )
                        )
    order by lw.calendar_date desc

Answer 1

SELECT 1 just selects a 1, of course. What it's used for in this case is testing whether any rows exist that match the criteria: if a row exists that matches the WHERE clause, then it returns 1, otherwise it returns nothing.

That's a common construct in WHERE EXISTS clauses, since you don't really want to return anything except the literal fact that the row exists.

---

Looking at this in more detail, let's show exactly what is happening.

Let's look at the first part only, the second part is sufficiently similar to be irrelevant. So your query is:

select calendar, fiscal_yr, fiscal_qtr, fiscal_week 
  from dim_date_weeks
  where exists 
    (select 1 
      from dim_date cw
      where cw.calendar_date_Dateadd(dd, datediff(dd, 0, getdate()), 0)
            and weeks.fin_qtr=cq.fin_qtr
            and weeks.fiscalweek= cw.fiscal_week
    )

Setting aside the detail that weeks is undefined, we'll assume that is really dim_date_weeks:

First, this is selecting rows from dim_date_Weeks.

Then, for each row in dim_date_weeks, it is querying dim_date, and finding all rows that have a fin_qtr that matches this row's fin_Qtr in weeks, and have a fiscal_week that matches this row's fiscalweek in weeks, and also have a 'true' value returned from that calendar_date_dateadd function (or that is a typo, more likely, and there's some comparison between cw.calendar_Date and a result of the native DateAdd function).

If that query returns any rows, then exists returns true, and that row is added to the result set. If it returns no rows, then exists returns false and that row is not added to the result set.

Now, you'll note that there is no discussion of the 1 here. That's because it is irrelevant. Look at the documentation of EXISTS; all it says is:

Returns TRUE if a subquery contains any rows.

So it doesn't matter at all what you select. This isn't like select count() where it does matter what's inside that parenthesis if the column is nullable and contains nulls; here, it's just rows that matter. So the writer followed a stylistic choice to put 1 there. Others (like the SQL Server documentation) might put select * there. Really you can select anything that's legal to select. It's not actually going to put that data anywhere; none of the information from the inner EXISTS query is put on the result set. Only the fact that it's true or false.

---

Ultimately, this is essentially the same as doing an inner join between the original from table and the exists inner query, and just not selecting anything from it. So the following would more-or-less return the same thing, if cw only returns one row per each weeks row (if they have a one to one relationship):

select weeks.calendar, weeks.fiscal_yr, weeks.fiscal_qtr, weeks.fiscal_week
  from dim_date_weeks weeks
  inner join
  (select 1 
     from dim_date
     where calendar_date_Dateadd(...)
  ) cw
  on cw.fin_qtr=weeks.fin_qtr
  and cw.fiscal_week=weeks.fiscalweek

If they don't have a one to one relationship, or many to one where cw is the one, then this will not return the same thing, and is probably why the exists query was done in the first place. But you get the idea.

Note that I put 1 there also, but could've just as easily put * or fin_Qtr or anything else legal to return. You can always return a constant, in which case a constant will be returned one for every valid row, after all.

---

Let's actually see that in action, just for fun. Assuming this isn't a server that will mind a query being run against it frivolously, run this:

select 1
  from dim_date

What do you get? A lot of 1s, probably. (Might be a good idea to put top 1000 or something in there if dim_Date is big.) In fact, if you did a count of rows on dim_date, and did a count of those 1s, you'd get the same number (if everything ran properly).

Answer 2

SELECT 1 just return 1 , that is just a constant so the query return something and the EXISTS return true.

You can use anything. 1 is just the common one and because is a constanst is very fast.