Skip to content

SQL Clause Nodes

SQL Clause nodes represent the components that appear within SQL statements. Each clause type encapsulates specific functionality like filtering, joining, grouping, or ordering data.

Overview

Clause nodes are building blocks of SQL statements. They inherit from base node classes and contain the expressions, conditions, and specifications needed for their particular function.

Available Clause Types

Data Filtering and Selection

  • WHERE Clause - TWhereClause
  • Filter rows based on conditions
  • Contains boolean expressions

  • Used in SELECT, UPDATE, DELETE statements

  • HAVING Clause - THavingClause

  • Filter grouped results
  • Applied after GROUP BY
  • Similar structure to WHERE clause

Data Sources and Relationships

  • FROM Clause - TFromClause
  • Specify data sources
  • Contains table references and joins

  • Foundation for data retrieval

  • JOIN Clause - TJoin

  • Combine data from multiple tables
  • Various join types (INNER, LEFT, RIGHT, FULL)
  • Contains join conditions

Data Organization

  • GROUP BY Clause - TGroupByClause
  • Group rows for aggregation
  • Contains grouping expressions

  • Enables aggregate functions

  • ORDER BY Clause - TOrderByClause

  • Sort result sets
  • Contains sort specifications
  • Supports multiple sort criteria

Database-Specific Clauses

  • LIMIT Clause - TLimitClause
  • Limit number of returned rows
  • PostgreSQL, MySQL syntax

  • Pagination support

  • TOP Clause - TTopClause

  • Limit number of returned rows
  • SQL Server syntax
  • Percentage options available

Common Properties

Most clause nodes share these characteristics:

Property Description Access Method
startToken First token in clause getStartToken()
endToken Last token in clause getEndToken()
sourceString Original SQL text toString()

Usage Patterns

Checking for Clause Presence

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
TSelectSqlStatement select = // ... parsed statement

// Check if WHERE clause exists
if (select.getWhereClause() != null) {
    TExpression condition = select.getWhereClause().getCondition();
    // Process WHERE condition
}

// Check if GROUP BY clause exists
if (select.getGroupByClause() != null) {
    for (int i = 0; i < select.getGroupByClause().getItems().size(); i++) {
        TExpression groupExpr = select.getGroupByClause().getItems().getGroupByItem(i).getExpr();
        // Process GROUP BY expression
    }
}

Extracting All Clause Conditions

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
public List<TExpression> getAllConditions(TSqlStatement stmt) {
    List<TExpression> conditions = new ArrayList<>();

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

        // WHERE conditions
        if (select.getWhereClause() != null) {
            conditions.add(select.getWhereClause().getCondition());
        }

        // HAVING conditions
        if (select.getHavingClause() != null) {
            conditions.add(select.getHavingClause().getCondition());
        }

        // JOIN conditions
        if (select.getFromClause() != null) {
            extractJoinConditions(select.getFromClause(), conditions);
        }
    }

    return conditions;
}

Database-Specific Considerations

SQL Server

  • TOP clause for row limiting
  • OUTPUT clause for modified data
  • WITH clause for table hints

PostgreSQL

  • LIMIT and OFFSET for pagination
  • RETURNING clause for modified data
  • Advanced window function clauses

Oracle

  • ROWNUM in WHERE clauses
  • CONNECT BY for hierarchical queries
  • MODEL clause for spreadsheet-like calculations

MySQL

  • LIMIT with optional OFFSET
  • ON DUPLICATE KEY UPDATE clause
  • Storage engine specific hints

See Also