Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004
    Posts
    15

    Unanswered: Column Headings in Result Sets

    Does the ANSI SQL Standard specify how columns are named in result sets?

    I ask this because I just came across some behavior that surprised me, or went against my expectations.

    Given the table: t1 (c1 int)
    and this query: select t1.c1 from t1

    the results are displayed as follows on all databases I tried it on (SQL Server, Oracle, DB2, mySQL and Sybase):

    c1
    ---
    1
    2
    ..etc

    I would have expected the column heading to be different, as follows:

    t1.c1
    -----
    1
    2
    ...etc

    So, if you say 't1.c1' in the select list, you should see t1.c1 in the column heading in the result set.

    Given another table, t2 (c1 int), the column headings in the result set of the query 'select t1.c1, t2.c1 from t1,t2' were the same on all database platforms:
    c1 | c1
    ------------
    1 | 2
    2 | 3

    This strikes me as ambiguous, because how do you know which result column came from which table ? I know that you can use column aliases if you want unique names in your column headings, but is there a good reason why the column headings 't1.c1' and 't2.c1' wouldn't be used by default if you specify 't1.c1' and 't2.c1' as items in your select list?

    Thanks,
    Colm.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by colmmagoo
    ... is there a good reason why the column headings 't1.c1' and 't2.c1' wouldn't be used by default if you specify 't1.c1' and 't2.c1' as items in your select list?
    yes -- because by the time the result set is constructed, the database has forgotten which table each column came from



    more accurately, a column name is an identifier, whereas "t1.c1" is a string

    you could always do this, if you really need it --

    select t1.c1 as "t1.c1", ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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