CODEWITHSUNDEEP
sqljoin
Alexander
Alexander

Reputation: 255

Why one need different table aliases If the table appears more than once in the FROM clause?

Currently I'm reading a book about SQL which provide following explanation:

you will need to give each instance of the branch table a different alias so that the server knows which one you are referring to in the various clauses.

But I can't understand why it's not possible to JOIN columns from the exact same table while using different ON clauses? Why server need to create second instance of the table and then distinguish them from each other?

Upvotes: 0

Views: 967

Answers (1)

D Stanley
D Stanley

Reputation: 152556

So the parser knows which table you're referring to when referencing the columns in the JOIN clause:

SELECT * FROM Person      // child
INNER JOIN Person         // parent
ON Person.ID = Person.ParentID          // which table goes with which column?

or when using additional columns in the join:

SELECT * FROM Person      // child
INNER JOIN Person         // parent
ON Person.ID = Person.ParentID          // which table goes with which column?
    AND Person.Name = "John"  // child or parent?

or in a WHERE clause

SELECT * FROM Person      // child
INNER JOIN Person         // parent
ON Person.ID = Person.ParentID
WHERE Name = "John"       // child or parent?

Or when adding a third table

SELECT * FROM Person      // child
INNER JOIN Person         // parent
ON Person.ID = Person.ParentID
INNER JOIN Address
ON Person.AddressID = Address.AddressID  // child or parent?

or when specifying columns in the result:

SELECT Name    // Child or Parent?
FROM Person      // child
INNER JOIN Person         // parent
ON Person.ID = Person.ParentID          // which table goes with which column?

Bottom line - there are too many places where the context cannot be assumed, so aliases are required.

SELECT * 
FROM Person AS Child
INNER JOIN Person AS Parent
ON Parent.ID = Child.ParentID

Note that you do not "create [a] second instance" but just reference the same table twice. This may be what you meant but I wanted to be sure you understood that no data is copied or replicated; your're just referencing a table to itself.

Upvotes: 2

Related Questions