How can I reference a single table multiple times in the same query?

Question

Sometimes I need to treat the same table as two separate tables. What is the solution?

Answer 1

Use an alias:

SELECT t1.col1, t2.col3
FROM tbl t1
INNER JOIN tbl t2
    ON t1.col1 = t2.col2

Answer 2

You can reference, just be sure to use a table alias

select a.EmployeeName,b.EmployeeName as Manager
from Employees A
join Employees B on a.Mgr_id=B.Id

Answer 3

Use an alias like a variable name in your SQL:

select
    A.Id,
    A.Name,
    B.Id as SpouseId,
    B.Name as SpouseName
from
    People A
    join People B on A.Spouse = B.id

Answer 4

Alias is the most obvious solution

SELECT * FROM x1 AS x,y1 AS y

However if the table is the result of a query a common table expressions is quite usefull

;WITH ctx AS 
( select * from z)
SELECT y.* FROM ctx AS c1,ctx AS c2

A third solution -- suitable when your query lasts a long time -- is temporary tables:

SELECT * 
INTO #monkey
FROM chimpanzee

SELECT * FROM #monkey m1,#monkey m2

DROP TABLE #MONKEY

Note a common table expression is only available for one query (the query directly after it), and temporary tables last for the whole batch.