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?

About a *decade* or so ago, it was reasonable advise to suggest `select 1` instead of `select *` in e.g. an `EXISTS` clause. The optimizer was improved a long time ago. It was *never* a replacement for "get all columns from this result set". — Damien_The_Unbeliever, Aug 12 '11 at 12:19

Vladimir · Accepted Answer · 2011-08-12T12:34:33.530

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 & 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.

score 40 · Answer 2 · edited Jan 08 '16 at 15:50

40

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.

edited Jan 08 '16 at 15:50

brett rogers

6,501
7
33
43

answered Aug 12 '11 at 12:20

Filip De Vos

11,568
1
48
60

Can you provide some details about "This has been proven not true"? – Sachin Joseph Mar 16 '21 at 08:10
1

Some people have done extensive performance tests to determine the difference, but you can do it yourself easy enough. See this gist as example: https://gist.github.com/FilipDeVos/3f9f0ab9396e2761b7420e7573d584c2 – Filip De Vos Mar 16 '21 at 18:29

score 31 · Answer 3 · edited May 23 '17 at 12:24

31

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.

edited May 23 '17 at 12:24

Community

1
1

answered Aug 12 '11 at 12:20

Alex K.

171,639
30
264
288

3

Why would I want that? – CodyBugstein Dec 08 '13 at 12:54
9

@Imray This is useful when you are just interested whether the condition in your `WHERE` clause is true or false. It is often used in sub queries. – Andreas Nov 24 '15 at 09:19

score 9 · Answer 4 · answered Aug 12 '11 at 12:25

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

score 5 · Answer 5 · edited Dec 23 '13 at 08:31

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.

What does "select 1 from" do?

5 Answers5

Linked