Subqueries... document reference? (sql2k)

4 Nov 2005 4:10 PM

Rebecca York

Hi,

Someone recently posted a way of renaming the column names of a subquery.

SELECT
subQuery.Data1
, subQuery.Data2
, subQuery.Data3
FROM
(
SELECT TOP 0 NULL AS Field1 , NULL AS Field2 , NULL AS Field3
UNION ALL SELECT 1 , 2 , 3
UNION ALL SELECT 4 , 5 , 6
UNION ALL SELECT 7 , 8 , 9
) AS subQuery( Data1 , Data2 , Data3 )

If you run the contents of the subquery the field names are Field1, Field2,
Field3.
If you run the full query they are Renamed to Data1,Data2,Data3.

I've had a look through books online (updated jan2004) in Subquery
Fundamentals (which primarily concentrates on IN (subquery) or
EXISTS(subquery)) but I cannot find anything about renaming columns as
above.

Is this documented?

4 Nov 2005 4:19 PM

Tom Moreau

This is standard ANSI-SQL. You basically override the column names of the
derived table with the AS clause. The same applies to a view:

create view MyView (a, b, c)
as
select
x, y, z
from
MyTable
go

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com

Show quote

"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:436b8016$0$138$7b0f0fd3@mistral.news.newnet.co.uk...
> Hi,
>
> Someone recently posted a way of renaming the column names of a subquery.
>
>
> SELECT
> subQuery.Data1
> , subQuery.Data2
> , subQuery.Data3
> FROM
> (
> SELECT TOP 0 NULL AS Field1 , NULL AS Field2 , NULL AS Field3
> UNION ALL SELECT 1 , 2 , 3
> UNION ALL SELECT 4 , 5 , 6
> UNION ALL SELECT 7 , 8 , 9
> ) AS subQuery( Data1 , Data2 , Data3 )
>
>
> If you run the contents of the subquery the field names are Field1,
> Field2,
> Field3.
> If you run the full query they are Renamed to Data1,Data2,Data3.
>
>
> I've had a look through books online (updated jan2004) in Subquery
> Fundamentals (which primarily concentrates on IN (subquery) or
> EXISTS(subquery)) but I cannot find anything about renaming columns as
> above.
>
> Is this documented?
>
>
>

4 Nov 2005 4:38 PM

Bob Barrows [MVP]

Rebecca York wrote:
Show quote

> Hi,
>
> Someone recently posted a way of renaming the column names of a
> subquery.
>
>
> SELECT
>    subQuery.Data1
> , subQuery.Data2
> , subQuery.Data3
> FROM
> (
>      SELECT TOP 0 NULL AS Field1 , NULL AS Field2 , NULL AS Field3
>   UNION ALL SELECT 1 , 2 , 3
>   UNION ALL SELECT 4 , 5 , 6
>   UNION ALL SELECT 7 , 8 , 9
> ) AS subQuery( Data1 , Data2 , Data3 )
>
>
> If you run the contents of the subquery the field names are Field1,
> Field2, Field3.
> If you run the full query they are Renamed to Data1,Data2,Data3.
>
>
> I've had a look through books online (updated jan2004) in Subquery
> Fundamentals (which primarily concentrates on IN (subquery) or
> EXISTS(subquery)) but I cannot find anything about renaming columns as
> above.
>
> Is this documented?

Yes. Look at the documentation for the FROM clause:

Syntax
[ FROM { < table_source > } [ ,...n ] ]

< table_source > ::=
table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ]

    | view_name [ [ AS ] table_alias ] [ WITH ( < view_hint > [ ,...n ] ) ]
    | rowset_function [ [ AS ] table_alias ]
    | user_defined_function [ [ AS ] table_alias ]
    | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]

The subquery is also known as a derived table

HTH,
Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

4 Nov 2005 4:55 PM

Rebecca York

Thanks :)

Show quote

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:ePOJl4V4FHA.1420@TK2MSFTNGP09.phx.gbl...
> Rebecca York wrote:
> > Hi,
> >
> > Someone recently posted a way of renaming the column names of a
> > subquery.
> >
> >
> > SELECT
> >    subQuery.Data1
> > , subQuery.Data2
> > , subQuery.Data3
> > FROM
> > (
> >      SELECT TOP 0 NULL AS Field1 , NULL AS Field2 , NULL AS Field3
> >   UNION ALL SELECT 1 , 2 , 3
> >   UNION ALL SELECT 4 , 5 , 6
> >   UNION ALL SELECT 7 , 8 , 9
> > ) AS subQuery( Data1 , Data2 , Data3 )
> >
> >
> > If you run the contents of the subquery the field names are Field1,
> > Field2, Field3.
> > If you run the full query they are Renamed to Data1,Data2,Data3.
> >
> >
> > I've had a look through books online (updated jan2004) in Subquery
> > Fundamentals (which primarily concentrates on IN (subquery) or
> > EXISTS(subquery)) but I cannot find anything about renaming columns as
> > above.
> >
> > Is this documented?
>
> Yes. Look at the documentation for the FROM clause:
>
> Syntax
> [ FROM { < table_source > } [ ,...n ] ]
>
> < table_source > ::=
>     table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [
,...n ] ) ]
>     | view_name [ [ AS ] table_alias ] [ WITH ( < view_hint > [
,...n ] ) ]
>     | rowset_function [ [ AS ] table_alias ]
>     | user_defined_function [ [ AS ] table_alias ]
>     | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
>
>
> The subquery is also known as a derived table
>
> HTH,
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>