Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2006
    Posts
    22

    Unanswered: Select * vs Select each column in subquery

    Hello you all.

    I have this query to produce a View that later i'll use to produce some reports (based on this view and/joined other tables)

    Code:
    Select 
      A.col1,
      A.col2,
      A.col3,
      ...
      A.col30,
      B.b1,
      B.b2,
      ...
    from 
      (select * from TableHaving30Columns) A
    join 
      (select b1,b2,... from OtherTable) B
      on ......
    My concern is about the first subquery (select * ...) ; as i need ALL (or almost all) the columns from the TableHaving30Columns Ώis there any improvement in performance if i specify each column in the select (select col1, col2, col3 ... col29, col30 from ...) instead of the * ??
    If not a performance issue, any other consideration??

    Need also a reference to some literature about this theme of expliciting columns rather than * in selects.

    Thanks a lot for your help.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SELECT * FROM should NEVER exist in Production application code
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    Also, an "improvement" could be found by avoiding joining the two subqueries (which neither one has any conditions) and may prevent the use of indexes.
    Use plain vanilla join:
    Code:
    Select 
      A.col1,
      A.col2,
      A.col3,
      ...
      A.col30,
      B.b1,
      B.b2,
      ...
    from TableHaving30Columns A
     join OtherTable B
      on ......
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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