CODEWITHSUNDEEP
mysql
Harsha
Harsha

Reputation: 737

Numbers in SELECT statements

I encountered sql queries like 'SELECT 1 FROM TABLE_NAME' or 'SELECT 2 FROM TABLE_NAME' while debugging a program. I am curious as to what the numbers in the queries do and what result set does it return.

Thanks

Upvotes: 2

Views: 364

Answers (2)

Sid
Sid

Reputation: 349

Select 1 from your_table --> On execution , you get the value 1 for every row in your_table.

'Select *' and 'Select 1 or 2' have the same performance when executing without an EXIST condition. My personal choice is using 'Select 1 or 2' when there are conditions to check for existing rows as it is slightly faster, for eg. when querying with VIEWS or temp tables having millions of rows and lot many columns.

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166396

It will return a single column with the number as value, with n rows, n being the number of rows returned from the select.

This is usefull in cases such as

SELECT *
FROM YourTable yt
WHERE EXISTS(
    SELECT 1
    FROM SomeOtherTable sot
    WHERE yt.ID = sot.ID
    )

Also, good article at SELECT 1 vs SELECT * – An Interesting Observation

Upvotes: 5

Related Questions