Why would a table be aliased twice in the same query?

Question

Here is a query from https://stackoverflow.com/a/2213199/3284469, which get the information of the indexes of a table.

Why do we have two alias of pg_class by pg_class t and pg_class i?

Can i.oid = ix.indexrelid be replaced with t.oid = ix.indexrelid?

Thanks.

select
    t.relname as table_name,
    i.relname as index_name,
    a.attname as column_name
from
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
where
    t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r'
    and t.relname like 'test%'
order by
    t.relname,
    i.relname;

score 7 · Accepted Answer · answered Jul 07 '18 at 23:38

It is common in a query to alias a table twice where you need to refer to two different records from the same table.

Common situations are where a table has a self pointer, e.g. Employees where each employee points to a Manager - another employee. Viz:

EmpID   Name         ManagerID
1       John Boss    NULL
2       Mary Manager 1
3       Brian        2
4       Simon        2
5       Susan        2

In the above table, John is the boss, Mary reports to John, and the other three staff report to Mary.

If you want to get a list of each employee and their boss you need to alias the table twice:

SELECT worker.Name, boss.ManagerName
FROM 
    Employees AS worker
    LEFT JOIN Employees as boss ON worker.ManagerID = boss.EmpID

You can also use this for aggregating employee salary costs under each layer of supervision etc.

So in your question, no, you cannot replace one with the other because you are effectively referring to a different set of records from the table.

You need to think of SQL as SETs of data, not as Tables. This is an important step in learning quality SQL. A Set might be represented by a table, a subset of a table, or another query made of tables, yet other queries, or constants, or a combination.

In this case the underlying table is polymorphic - it stores data of a number of different types. So a subset is used for referring to different information within the table.

Why would a table be aliased twice in the same query?

1 Answers1