What does “select 1 from” do?

Question

I want to know what does select 1 from do? I saw in internet someone saying "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 be the result, when I execute the statement "select 1 from customer_table"?

Answer 1

The below statement:

select 1 from table

will be returning a column of 1's for all rows in the table. You could use it along with a where statement to verify whether you have an entry for a given key, as in:

if exists(select 1 from table where some_column = 'some_value')

What that person might be saying is rather than doing bulk selects with "select * from table", you should specify the columns that you need specifically, for two reasons:

1) Performance & you might get more data than you actually need.

2) The query's user may be relying on the order of columns. If your table gets refreshed, the client will receive columns in a different order than what expected.

Want to be a SQL expert? Come and join this SQL Certification by Intellipaat.

If you want to know more about Select Statement, refer to the below SQL Select Statement tutorial video that will help you out in a better way:

Answer 2

Use the SQL SELECT 1 FROM customer_table query to obtain a 1 for each record in the customer_table. It does not retrieve any useful information from the table's columns; instead, it only returns a constant value of 1 for every row.

When you run SELECT 1 FROM customer_table to your example table, the subsequent occurs:

Example Table: customer_table

cust_id	cust_name	cust_add
1000000001	Village Toys	Mapl
1000000002	Kids Place	South
1000000003	Fun4All	Sunny
1000000004	Fun4All	Riverside
1000000005	The Toy Store	53rd

Result of SELECT 1 FROM customer_table

1

1

1

1

1

Justification:

Number of Rows: The query will return a row for each entry in the customer_table. Your table has five rows; therefore, the outcome will also contain five rows.

Columns: Each row in the result set will have a single column with the value 1.