Intellipaat Back

Explore Courses Blog Tutorials Interview Questions
0 votes
4 views
in SQL by (6.1k points)

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"?

2 Answers

0 votes
by (12.7k points)

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:

0 votes
by (37.3k points)

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.


 

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
2 answers
0 votes
1 answer

1.2k questions

2.7k answers

501 comments

693 users

Browse Categories

...