What does select 1 from do

Question

I read some articles but really didn t understand what does select 1 from do   Someone says  you should use select 1 instead of select      Here is an example table   cust id     cust name       cust address  1000000001  Village Toys    Mapl 1000000002  Kids Place      South 1000000003  Fun4All         Sunny 1000000004  Fun4All         Riverside 1000000005  The Toy Store   53rd   What will the result be when I write select 1 from customer table what does this statement do

User · Answer

The construction is usually used in  existence  checks  if exists select 1 from customer table where customer    xxx     or   if exists select   from customer table where customer    xxx     Both constructions are equivalent  In the past people said the select   was better because the query governor would then use the best indexed column  This has been proven not true

User · Answer

SELECT COUNT    in EXISTS NOT EXISTS   EXISTS SELECT CCOUNT    FROM TABLE NAME WHERE CONDITIONS  - the EXISTS condition will always return true irrespective of CONDITIONS are met or not   NOT EXISTS SELECT CCOUNT    FROM TABLE NAME WHERE CONDITIONS  - the NOT EXISTS condition will always return false irrespective of CONDITIONS are met or not   SELECT COUNT 1 in EXISTS NOT EXISTS   EXISTS SELECT CCOUNT 1 FROM TABLE NAME WHERE CONDITIONS  - the EXISTS condition will return true if CONDITIONS are met  Else false   NOT EXISTS SELECT CCOUNT 1 FROM TABLE NAME WHERE CONDITIONS  - the NOT EXISTS condition will return false if CONDITIONS are met  Else true

User · Answer

select 1 from table   will return a column of 1 s for every row in the table  You could use it with a where statement to check whether you have an entry for a given key  as in   if exists select 1 from table where some column    some value     What your friend was probably saying is instead of making bulk selects with select   from table  you should specify the columns that you need precisely  for two reasons   1  performance  amp  you might retrieve more data than you actually need   2  the query s user may rely on the order of columns  If your table gets updated  the client will receive columns in a different order than expected

User · Answer

It does what you ask  SELECT 1 FROM table will SELECT  return  a 1 for every row in that table  if there were 3 rows in the table you would get  1 1 1   Take a look at Count    vs Count 1  which may be the issue you were described

User · Answer

The statement SELECT 1 FROM SomeTable just returns a column containing the value 1 for each row in your table   If you add another column in  e g  SELECT 1  cust name FROM SomeTable then it makes it a little clearer               cust name ----------- --------------- 1           Village Toys 1           Kids Place 1           Fun4All 1           Fun4All 1           The Toy Store

[sql-server-2008] What does "select 1 from" do?

Examples related to sql-server-2008