ansaurus

Question

Answer 1

+6 A:

Yes. You need to do this for a self join, for example f you have a table storing a hierarchy:

create table Foo (
    FooID int
   ,ParentFooID int
   ,[columns]
)

You can do a join to get the children of parents that satisfy a particular condition with a query like:

Select b.*
  from Foo a
  join Foo b
    on a.FooID = b.ParentFooID
   and [some condition filtering a]

ConcernedOfTunbridgeWells 2010-07-16 10:36:08

+1 for powers of inference!

Ed Harper 2010-07-16 10:38:34

But it will effect the performance or not?

Dev 2010-07-16 10:45:07

The query optimiser will have to do whatever is necessary to resolve the join predicate. For example, if the table is large and has an index on ParentFooID, it will probably have to do index lookups to resolve the FooID - ParentFooID join. Doing the match will always involve some resources.

ConcernedOfTunbridgeWells 2010-07-16 10:49:17

We do this a lot (representing a heirarchy in SQL.) Performance is fine, and it's a good, flexible system - although the queries can get a little involved. It works. (p.s. good inference!)

Ragster 2010-07-16 11:01:39

@Dev, almost certainly not - but there's one way to find out...

Mark Bannister 2010-07-16 11:52:29

Answer 2

A:

No not on the same table, but you can select the same table twice and give each a different alias.

SELECT alias1.*, alias2.*
FROM table alias1, table alias2

This will then allow you to use the same table for a different purpose within a single query.

Chris Diver 2010-07-16 10:39:21

But it will effect the performance or not?

Dev 2010-07-16 10:46:57

Yes, if you select the table twice then it will have to do twice as much work. Performance will depend on how they are used, what they are joined two and the indexes and statistics available to the query optimizer.

Chris Diver 2010-07-16 10:57:41

Since Chris's query has no join condition (implicit or explict) a cartesian join would be expected - ie. every single row in table will be linked with all the rows in the same table, so if you had 20 rows in table your resultset would be 400 rows! This is much worse than twice as much work. However, it's not the aliases that are causing the work - it's the cartesian join.

Mark Bannister 2010-07-16 11:57:32

The cross join was just an example to which I wasn't referring to when answering the comment - the OP did not specify how he was going to use the tables, I'd assume he will either self join or join them to different tables. Like I said performance will depend on how they are used.

Chris Diver 2010-07-16 12:15:06

ansaurus

tags:

views:

answers:

Multiple Alias names for a table

related questions