Select & select *
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Select & select *

Hybrid View

  1. #1
    Join Date
    May 2000
    Location
    Nagpur,MS,INDIA
    Posts
    10
    Can you please tell me what is the difference between the two statements

    1) Select * from emp;

    2) select column_list from emp;

    Thanking you in advance
    We develop Logics

  2. #2
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530
    Hi,
    There is not much difference between the 2 queries.That depends on the programmer whether he wants to select all the columns from the table or certain columns from the table.
    The benefits of the written queries comes in ur sql tuning.

    for e.g If u write the first query,select * from emp Oracle will keep a parsed version of the query in the shared SQL area of SGA.If another user writes the same query,parse and execution time is greatly reduced.
    In order to get the results from the second query,u can use the first query in ur application and dont show the columns u dont want.In this case the first query is used to get the results from both the queries.
    So,please try to write SQL queries that can be reusable in ur application.It depends on the application developer how he tunes ur application.

    In case of any doubts ,write to me at rohitsn@orasearch.net

    Regards.
    Rohit Nirkhe,Oracle DBA,OCP




  3. #3
    Join Date
    Feb 2001
    Posts
    123
    select * will give you all the columns in the table.
    select column_list is used to restrict the number of columns that are returned to *only* the columns named in the column list.

    If you are selecting values into a list of variables, column_list has many advantages - if the table definition changes (e.g. a column is added) this could break any code that uses select *.

    Also, select * takes more work to parse.

    It is good practice to alias your table names in the from clause too - i.e.

    select e.name
    from emp e

    because this saves recursive calls at parse time.

    HTH,

    David.

  4. #4
    Join Date
    May 2000
    Location
    Nagpur,MS,INDIA
    Posts
    10
    Hi All,
    My intention for puttiong this question is to check if is there any difference exists if we compaire both the queries.
    Please check
    1) select * from emp;
    2) select column_list from emp;

    where column_list represent all the columns of emp table. So please let me know if both these queries are equivalent OR is there any advantage of using one query with other ?
    Thanks again
    We develop Logics

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    You may want to be a little clearer in your question, as it seems to have been answered quite nicely. The difference in functionality is that * returns all the columns while column_list returns specific columns. If column_list is all the columns of the table in the order that they exist in the table, then the 2 are obviously functionally equivalen. The difference in performance is that Select * takes more time to parse, but may already be in the cache. Additionally, they said aliasing the table reduces recursive calls. Risk-wise, Slect * is *much* riskier. IMHO, it should *never* be used. It makes your SQL and PL/SQL subject to the order and number of columns in the table. This risk out-weighs any of the benefits, IMHO.

    I am not sure what else you could possibly be asking for.

    - Chris

  6. #6
    Join Date
    May 2000
    Location
    Nagpur,MS,INDIA
    Posts
    10
    Thank You very much Chris
    We develop Logics

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width