Table Reference Nodes¶

Table Reference nodes represent how tables and data sources are specified and combined in SQL statements. They handle everything from simple table names to complex join operations and derived tables.

Overview¶

Table reference nodes define the data sources for SQL operations. They appear primarily in FROM clauses and can represent physical tables, views, subqueries, or combinations thereof through joins.

Table Reference Types¶

Basic Table References¶

Table Nodes - TTable
Physical tables and views
Table aliases and schema qualification
Database-specific table hints
Table Lists - TTableList
Collections of table references
Comma-separated table lists
Cartesian product implications

Join Operations¶

JOIN Nodes - TJoinExpr
All join types (INNER, LEFT, RIGHT, FULL)
Join conditions and expressions
Multi-table join chains
Join Conditions
ON clause specifications
USING clause syntax
Natural join handling

Advanced Table Sources¶

Derived Tables - Subqueries in FROM
Subquery table expressions
Common Table Expressions (CTEs)
Inline views and virtual tables
Table Functions
Table-valued functions
UNNEST operations
Lateral joins and table expressions

Table Reference Structure¶

Basic Table Structure¶

TTable
├── tableName (TObjectName)
├── tableAlias (string)
├── schemaName (optional)
└── tableHints (database-specific)

Join Structure¶

TJoinExpr
├── joinType (INNER, LEFT, RIGHT, FULL)
├── leftTable (TTable)
├── rightTable (TTable)
└── onCondition (TExpression)

Common Usage Patterns¶

Extracting All Table Names¶

public Set<String> getAllTableNames(TFromClause fromClause) {
    Set<String> tableNames = new HashSet<>();

    fromClause.acceptChildren(new IElementVisitor() {
        public void visitElement(TParseTreeNode node) {
            if (node instanceof TTable) {
                TTable table = (TTable) node;
                String fullName = table.getFullName();
                tableNames.add(fullName);
            }
        }
    });

    return tableNames;
}

Analyzing Join Relationships¶

public List<JoinInfo> analyzeJoins(TFromClause fromClause) {
    List<JoinInfo> joins = new ArrayList<>();

    fromClause.acceptChildren(new IElementVisitor() {
        public void visitElement(TParseTreeNode node) {
            if (node instanceof TJoinExpr) {
                TJoinExpr join = (TJoinExpr) node;
                JoinInfo info = new JoinInfo(
                    join.getJointype(),
                    getTableName(join.getLeftTable()),
                    join.getOnCondition()
                );
                joins.add(info);
            }
        }
    });

    return joins;
}

Building Table Lineage¶

public Map<String, Set<String>> buildTableLineage(TSqlStatement stmt) {
    Map<String, Set<String>> lineage = new HashMap<>();

    if (stmt instanceof TSelectSqlStatement) {
        TSelectSqlStatement select = (TSelectSqlStatement) stmt;

        // Get source tables
        Set<String> sourceTables = getAllTableNames(select.getFromClause());

        // Map to result (view name or temporary result)
        String resultName = getResultTableName(select);
        lineage.put(resultName, sourceTables);
    }

    return lineage;
}

Join Types and Their Representations¶

SQL Syntax	AST Representation	Join Type
`INNER JOIN`	`TJoinExpr` with `jointype = inner`	Inner join
`LEFT JOIN`	`TJoinExpr` with `jointype = left`	Left outer join
`RIGHT JOIN`	`TJoinExpr` with `jointype = right`	Right outer join
`FULL JOIN`	`TJoinExpr` with `jointype = full`	Full outer join
`CROSS JOIN`	`TJoinExpr` with `jointype = cross`	Cross join
`,` (comma)	`TTableList`	Implicit cross join

Database-Specific Features¶

SQL Server¶

Table hints (WITH (NOLOCK), WITH (INDEX=...))
APPLY operators (CROSS APPLY, OUTER APPLY)
Temporal table syntax (FOR SYSTEM_TIME)

PostgreSQL¶

LATERAL joins for correlated subqueries
Table functions in FROM clause
Advanced CTE features (RECURSIVE)

Oracle¶

Partition-wise joins
Flashback query syntax (AS OF)
Hierarchical query syntax in FROM

MySQL¶

Index hints (USE INDEX, FORCE INDEX)
Partition pruning syntax
Engine-specific table options

Performance Considerations¶

Join Order Analysis¶

public void analyzeJoinOrder(TFromClause fromClause) {
    // Left-deep vs right-deep vs bushy join trees
    // Analyze join selectivity
    // Identify potential performance issues
}

Table Size Estimation¶

public Map<String, Long> estimateTableSizes(Set<String> tables) {
    // Interface with database statistics
    // Estimate cardinality for join optimization
    return tableSizeEstimates;
}