Searching for a precise term for a SQL phrase

Question

I'm trying to document some SQL and wanted to get the right terminology. If you write SQL like so;

select child.ID, parent.ID
from hierarchy child 
inner join hierarchy parent 
on child.parentId = parent.ID

Then you have one actual table ('hierarchy') which you are giving two names ('parent' and 'child') My question is about how you refer to the logical entity of a table with a name.

What would you write in the blank here for the name?

"This query uses one table (hierarchy) but two _ (child and parent)"

[edit] left a previous draft in the question. now corrected.

Answer 1

I believe this is called a SELF JOIN. A and B (or "child" and "parent", I think you have a typo in your question) are called ALIASes or TABLE ALIASes.

Answer 2

In the SQL Server docs, the term is table_source :

Specifies a table, view, or derived table source, with or without an alias, to use in the Transact-SQL statement

In the BNF grammar, it's:

<table_source> ::= 
{
        table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ] 
        [ WITH ( < table_hint > [ [ , ]...n ] ) ] 
    | rowset_function [ [ AS ] table_alias ] 
        [ ( bulk_column_alias [ ,...n ] ) ] 
        | user_defined_function [ [ AS ] table_alias ] [ (column_alias [ ,...n ] ) ]
    | OPENXML <openxml_clause> 
    | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ] 
    | <joined_table> 
    | <pivoted_table> 
    | <unpivoted_table>
      | @variable [ [ AS ] table_alias ]
        | @variable.function_call ( expression [ ,...n ] ) [ [ AS ] table_alias ] [ (column_alias [ ,...n ] ) ]

Answer 3

The concept is a self join. However, the a is a syntax error. The table is hierarchy, the alias is child.

I would call each part of a self join an instance.

Answer 4

'child', 'parent'

The term used in the SQL-92 Standard spec is "correlation name", being a type of "identifier".

'hierarchy'

The term used in the SQL-92 Standard spec is "table".

Hence the answer to your (edited) question is:

This query uses one table (hierarchy) but two correlation names (child and parent).