Skip to Main Content
  • Questions
  • Difference between select * and select a.* with alias on a table

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Bency.

Asked: May 25, 2020 - 9:08 am UTC

Answered by: Connor McDonald - Last updated: May 27, 2020 - 1:03 am UTC

Category: SQL - Version: 12c

Viewed 100+ times

You Asked

Hi Tom,

What is the difference between using "select * from table1" and "selelct a.* from table1 a".
Observed that while doing the select statement
"select a.* from table1 a" locks the data for update (like if we use the statement from PLSQL developer, commit, rollback button will be active), where as if I use "select * from table1" those buttons are not getting activated. With this, I would like to understand is there any performance impact if I use the select statement with a.*.

Assume I am doing an insert statement by using "select a.* from table1 a" for 1 billion records, does this impact the performance?

Thank you in Advance.

Kind Regards
Bency

and we said...

Nothing in the *database* will lock data with a SELECT unless you are doing SELECT-FOR-UPDATE.

What third-party tools may do if of course their business.

You will see negligible difference in performance (we're talking nanoseconds) between select * and select a.*

However it is generally considered good practice to alias *everything* in a query (the select columns, the where clause columns etc) because it makes your code more robust.

For example, assuming 'owner' comes from my_table1 and 'name, address' comes from my_table2, then the following works just fine:

select owner, name, address
from  my_table1, my_table2
where [join conditions]

But the moment someone adds a column called "name" to my_table1, the query breaks, because we can't resolve which table we should be using.

Conversely

select a.owner, b.name, b.address
from  my_table1 a, my_table2 b
where [join conditions]


will not break.


and you rated our response

  (1 rating)

Reviews

Thank you for the prompt response with detailed explanation

May 26, 2020 - 5:56 am UTC

Reviewer: Bency Mathew from India, Bangalore

Thank you for the prompt response with detailed explanation
Connor McDonald

Followup  

May 27, 2020 - 1:03 am UTC

glad we could help

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.