TWhereClause¶

Overview¶

The TWhereClause node represents WHERE clauses in SQL statements. It contains the boolean expression that filters rows during query execution.

SQL to AST Mapping¶

Simple WHERE Example¶

SELECT * FROM users WHERE age > 18;

AST Structure:

TWhereClause
└── condition: TExpression (age > 18)
    ├── leftOperand: TExpression (age)
    ├── operator: comparison_gt_t
    └── rightOperand: TExpression (18)

Complex WHERE Example¶

SELECT * FROM orders 
WHERE (status = 'shipped' OR status = 'delivered') 
  AND order_date >= '2023-01-01'
  AND customer_id IN (1, 2, 3);

AST Structure:

TWhereClause
└── condition: TExpression (logical_and_t)
    ├── leftOperand: TExpression (logical_and_t)
    │   ├── leftOperand: TExpression (logical_or_t)
    │   │   ├── leftOperand: TExpression (status = 'shipped')
    │   │   └── rightOperand: TExpression (status = 'delivered')
    │   └── rightOperand: TExpression (order_date >= '2023-01-01')
    └── rightOperand: TExpression (customer_id IN (1, 2, 3))

Key Properties¶

Property	Type	Description	Access Method
`condition`	`TExpression`	The filter expression	`getCondition()`

Common Usage Patterns¶

Extracting WHERE Conditions¶

public void analyzeWhereClause(TSelectSqlStatement select) {
    TWhereClause whereClause = select.getWhereClause();

    if (whereClause != null) {
        TExpression condition = whereClause.getCondition();
        analyzeCondition(condition);
    } else {
        System.out.println("No WHERE clause present");
    }
}

Finding Column References in WHERE¶

public Set<String> getWhereColumns(TWhereClause whereClause) {
    Set<String> columns = new HashSet<>();

    if (whereClause != null) {
        TExpression condition = whereClause.getCondition();

        condition.postOrderTraverse(new IExpressionVisitor() {
            public boolean exprVisit(TParseTreeNode node, boolean isLeafNode) {
                if (node instanceof TExpression) {
                    TExpression expr = (TExpression) node;
                    if (expr.getExpressionType() == EExpressionType.simple_object_name_t) {
                        columns.add(expr.getObjectOperand().toString());
                    }
                }
                return true;
            }
        });
    }

    return columns;
}

Condition Type Analysis¶

public void analyzeConditionTypes(TExpression condition) {
    switch (condition.getExpressionType()) {
        case logical_and_t:
            System.out.println("AND condition");
            analyzeConditionTypes(condition.getLeftOperand());
            analyzeConditionTypes(condition.getRightOperand());
            break;

        case logical_or_t:
            System.out.println("OR condition");
            analyzeConditionTypes(condition.getLeftOperand());
            analyzeConditionTypes(condition.getRightOperand());
            break;

        case comparison_eq_t:
            System.out.println("Equality comparison");
            break;

        case comparison_gt_t:
            System.out.println("Greater than comparison");
            break;

        case in_t:
            System.out.println("IN condition");
            break;

        case like_t:
            System.out.println("LIKE pattern matching");
            break;

        // ... other condition types
    }
}

WHERE Clause Contexts¶

WHERE clauses appear in multiple statement types:

SELECT Statements¶

SELECT column1, column2 FROM table1 WHERE condition;

UPDATE Statements¶

UPDATE table1 SET column1 = value1 WHERE condition;

DELETE Statements¶

DELETE FROM table1 WHERE condition;

Expression Types in WHERE Clauses¶

Comparison Operations¶

=, <>, != (equality/inequality)
<, <=, >, >= (relational)
IS NULL, IS NOT NULL

Logical Operations¶

AND, OR, NOT
Parenthetical grouping

Pattern Matching¶

LIKE with wildcards
REGEXP/RLIKE (database-specific)

Set Operations¶

IN, NOT IN
EXISTS, NOT EXISTS
ALL, ANY, SOME

Range Operations¶

BETWEEN and NOT BETWEEN

Database-Specific Features¶

SQL Server¶

-- Full-text search
WHERE CONTAINS(description, 'search terms')

-- Computed column predicates
WHERE computed_column > 100

PostgreSQL¶

-- Array operations
WHERE tags @> ARRAY['tag1', 'tag2']

-- JSON operations
WHERE data->>'status' = 'active'

Oracle¶

-- Hierarchical queries
WHERE LEVEL > 2

-- Regular expressions
WHERE REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')

MySQL¶

-- Full-text search
WHERE MATCH(title, description) AGAINST('search terms')

-- JSON operations
WHERE JSON_EXTRACT(data, '$.status') = 'active'

Performance Considerations¶

Index Usage¶

// Identify columns that could benefit from indexes
public Set<String> getIndexCandidates(TWhereClause whereClause) {
    Set<String> candidates = new HashSet<>();

    // Look for equality conditions
    // Look for range conditions
    // Consider composite index opportunities

    return candidates;
}

Condition Optimization¶

Sargable predicates (Search ARGument ABLE)
Function calls on columns prevent index usage
Leading wildcard in LIKE prevents index usage

Common Patterns¶

Filter Pushdown Analysis¶

public boolean canPushDownFilter(TExpression condition, Set<String> availableColumns) {
    // Check if all referenced columns are available at this level
    Set<String> referencedColumns = getReferencedColumns(condition);
    return availableColumns.containsAll(referencedColumns);
}

Condition Selectivity Estimation¶

public double estimateSelectivity(TExpression condition) {
    switch (condition.getExpressionType()) {
        case comparison_eq_t:
            return 0.1; // Highly selective
        case comparison_gt_t:
        case comparison_lt_t:
            return 0.33; // Moderately selective
        case like_t:
            return estimateLikeSelectivity(condition);
        default:
            return 0.5; // Default estimate
    }
}