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

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

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.