Skip to content

Join analysis (Semantic IR)

Older SQL — especially Oracle — writes joins in the FROM clause as a comma-separated table list, with the join condition buried in WHERE:

1
2
3
SELECT e.id, d.name
  FROM employees e, departments d
 WHERE e.dept_id = d.id

Read literally, that is a cartesian product with a filter. Semantically it is an inner join. If you are migrating such SQL to ANSI JOIN ... ON syntax, building lineage, or auditing join conditions, you need the second reading — and you need it to survive Oracle's (+) outer-join marker too.

Join analysis gives you that reading. It classifies each join boundary in a query as INNER, LEFT, RIGHT, IMPLICIT_CROSS and so on, and tells you which predicates are join conditions versus plain row filters.

Supported consumption profile

Join Analysis Consumption Profile v1 is a supported, read-only API. Its primary entry point is SqlSemanticAnalyzer; consume the result through AnalysisResult, SemanticProgram, StatementGraph, and the immutable types in gudusoft.gsqlparser.ir.semantic.joinanalysis.

The wider Semantic IR tree contains advanced builder and binding APIs as well as internal validation and parity tools. A Java type being public does not put it in the supported consumption profile. See API tiers before using SemanticIRBuilder directly.

Semantic analysis is separate from the parse tree

TGSqlParser.parse() and the TSelectSqlStatement AST are unchanged by join analysis. A comma join remains a comma join in the AST. If you inspect only the AST, you will not see the semantic promotion described on this page.

Quick start

Pass SQL text and its dialect to SqlSemanticAnalyzer, check the result, then read the immutable join facts. The example below is included from an executable test source and is compiled and run against the library during verification:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
package gudusoft.gsqlparser.examples;

import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.ir.semantic.AnalysisResult;
import gudusoft.gsqlparser.ir.semantic.Diagnostic;
import gudusoft.gsqlparser.ir.semantic.SqlSemanticAnalyzer;
import gudusoft.gsqlparser.ir.semantic.StatementGraph;
import gudusoft.gsqlparser.ir.semantic.joinanalysis.JoinAnalysisFacts;
import gudusoft.gsqlparser.ir.semantic.joinanalysis.JoinEntity;
import gudusoft.gsqlparser.ir.semantic.joinanalysis.Predicate;

import java.util.ArrayList;
import java.util.List;

/** Executable source for the Join Analysis quick start documentation. */
public final class JoinAnalysisQuickStart {

    private JoinAnalysisQuickStart() {
    }

    public static void main(String[] args) {
        for (String line : describeJoins()) {
            System.out.println(line);
        }
    }

    public static List<String> describeJoins() {
        String sql = "SELECT e.id, d.name "
                + "FROM employees e, departments d "
                + "WHERE e.dept_id = d.id";

        AnalysisResult result = SqlSemanticAnalyzer.analyze(
                sql, EDbVendor.dbvoracle);
        if (!result.isSuccessful()) {
            throw new IllegalStateException(formatDiagnostics(result));
        }

        List<String> lines = new ArrayList<>();
        for (StatementGraph graph : result.getProgram().getStatements()) {
            JoinAnalysisFacts facts = graph.getJoinAnalysisFacts();
            for (JoinEntity join : facts.getJoinGraph().getJoins()) {
                lines.add("join #" + join.getOrder()
                        + " type=" + join.getJoinType()
                        + " syntax=" + join.getSourceSyntax());
                for (Predicate condition : join.getConditions()) {
                    lines.add("    condition " + condition);
                }
            }
            for (Predicate filter : facts.getFilterPredicates()) {
                lines.add("filter " + filter);
            }
        }
        return lines;
    }

    private static String formatDiagnostics(AnalysisResult result) {
        StringBuilder message = new StringBuilder("Analysis failed");
        for (Diagnostic diagnostic : result.getDiagnostics()) {
            message.append(System.lineSeparator())
                    .append(diagnostic.getCode())
                    .append(": ")
                    .append(diagnostic.getMessage());
        }
        return message.toString();
    }
}

Output:

1
2
join #0 type=INNER syntax=COMMA
    condition (e.dept_id = d.id) EQUI

The comma join was classified INNER, and the WHERE predicate moved out of the filter list and onto the join as a condition. syntax=COMMA records that this was written as an implicit join, so you can still tell it apart from an ANSI JOIN ... ON that produced the same classification.

The object model

Step Call Gives you
1 SqlSemanticAnalyzer.analyze(sql, vendor) AnalysisResult
2 result.isSuccessful() Whether a publishable program was built without error diagnostics
3 result.getProgram().getStatements() List<StatementGraph> — one per query block
4 graph.getJoinAnalysisFacts() JoinAnalysisFacts, never null
5 facts.getJoinGraph().getJoins() List<JoinEntity> — one per join boundary
6 facts.getFilterPredicates() List<Predicate> — predicates that are not join conditions

Always check result.isSuccessful() before reading the program. You do not need to null-check getJoinAnalysisFacts(); a query with no join facts returns the immutable JoinAnalysisFacts.EMPTY value. Use facts.isEmpty() when you want to skip it.

Diagnostics

Diagnostics are part of the result even when analysis succeeds. Treat DiagnosticCode, DiagnosticCategory, Severity, and an exact VendorError when present as machine-readable values. Diagnostic.getMessage() is display text and may be reworded; do not parse it for control flow.

An ERROR means the strict analyzer does not publish a program or JSON. A result with warnings only may still be successful, but those warnings can identify facts that require catalog metadata or were conservatively omitted.

Catalog metadata is semantic input

Use SqlSemanticAnalyzer.analyze(sql, vendor, Catalog) when table and column metadata is available. The Catalog DTO is the supported input boundary; the analyzer supplies the same metadata to parsing, name resolution, and Semantic IR lowering.

SQL shape Without catalog With a complete catalog
Qualified JOIN ... ON or comma-join predicate Join type, syntax, endpoints, and predicate structure are available Same structure; resolution evidence may become more exact
Qualified Oracle (+) predicate Direction and join structure are available Same structure; resolution evidence may become more exact
NATURAL JOIN Natural-join entity is retained, shared keys are unknown, and NATURAL_CATALOG_REQUIRED is emitted Shared keys are published through StatementGraph.getJoinColumnRefs(); currently they are not synthesized into JoinEntity.getConditions()
Base-table SELECT * Expansion may be unavailable and diagnosed Columns can be expanded in declaration order
Ambiguous unqualified column GSP cannot safely invent ownership Metadata can prove exact, ambiguous, or missing resolution

A partial catalog is not treated as permission to guess. Read diagnostics and do not interpret an empty condition or column-reference list as proof that the SQL had no semantic intent.

API tiers and migration

The support boundary follows the way an API is consumed, not Java package visibility:

Tier Intended use Main APIs
Supported Production read-only join analysis SqlSemanticAnalyzer; AnalysisResult; read-only traversal through SemanticProgram, StatementGraph, diagnostics, Catalog, and joinanalysis types
Advanced / preview Integrations that already own a parsed AST and accept responsibility for resolver and recovery wiring SemanticIRBuilder.buildResult(...), SemanticBuildResult, build options, NameBindingProvider, Resolver2NameBindingProvider, and the TSQLEnv analyzer overload
Internal GSP validation and parity implementation validation, validation.oracle, and diff packages

The supported profile preserves documented method signatures, nullability, collection immutability, diagnostic identifiers, and source-provenance meaning within the same major version. Additive methods, diagnostic codes, JSON fields, and enum values may appear, so switches over enums should retain an unknown or default branch. A bug fix may correct a previously wrong semantic classification and will be called out in release notes.

Migrating direct builder code

New code should use SqlSemanticAnalyzer. If an advanced integration must own the AST pipeline, use the atomic SemanticIRBuilder.buildResult(...) API and inspect its diagnostics and recovery metadata together with the recovered program. The no-argument Resolver2NameBindingProvider() is the metadata-less advanced path; when a TSQLEnv is present, the parser and provider must receive the same instance.

SemanticIRBuilder.build(...) plus drainBuildDiagnostics() is a deprecated compatibility adapter. Reading only the returned program can hide the error that caused a recovered shape, so do not introduce new uses of that pair.

Semantic IR JSON is versioned separately from the Java API. Branch on AnalysisResult.getSchemaVersion() before depending on a result's wire shape; it matches the top-level schemaVersion in that result's JSON. SqlSemanticAnalyzer.schemaVersion() is the historical baseline for payloads that need no optional schema extensions, not a maximum-version query. Prefer AnalysisResult.getJson() to calling the advanced exporter directly.

JoinEntity fields

Accessor Meaning
getOrder() Position of this boundary, 0-based, left to right
getJoinType() SemanticJoinType: INNER, LEFT, RIGHT, FULL, CROSS, NATURAL, IMPLICIT_CROSS, SEMI, ANTI_SEMI, UNSUPPORTED
getSourceSyntax() JoinSourceSyntax: EXPLICIT, COMMA, LATERAL, SEMI — how it was written
getConditions() List<Predicate> — the join conditions
getConditionText() Optional verbatim source expression that directly defines the join condition. See the provenance contract below
getLeftEndpoint() / getRightEndpoint() The two sides of the boundary
getUsingColumns() Column names from a USING (...) clause
isNatural() / isLateral() Markers for NATURAL and LATERAL joins
getSourceSpan() Position in the original SQL

getJoinType() and getSourceSyntax() are independent: a comma join promoted to INNER reports type=INNER, syntax=COMMA.

conditionText is source provenance

getConditionText() is not a serialization of getConditions(). It preserves an anchored expression that was actually written in the source SQL; GSP never synthesizes or reformats this field.

Source form conditionText Provenance
JOIN ... ON expr (EXPLICIT) Verbatim expr, without the ON keyword; null if it cannot be anchored Written ON expression
JOIN ... USING (...), NATURAL JOIN, or CROSS JOIN (EXPLICIT) null No written condition expression; inspect usingColumns or natural
Comma join (COMMA) null, even after WHERE promotion populates conditions No join-local expression was written
Condition-less LATERAL / APPLY (LATERAL) null Correlation lives in the right relation or subquery body
JOIN LATERAL ... ON expr (EXPLICIT, lateral=true) Verbatim expr It is still a written ON expression
Predicate-derived EXISTS / IN (SEMI) Complete verbatim wrapper, including NOT when written; null if it cannot be anchored Written predicate wrapper, not an ON clause

An implicit join therefore has no literal ON expression, even when its semantic conditions are fully populated:

1
2
FROM a, b WHERE a.x = b.y(+)      -> type=LEFT syntax=COMMA    conditionText=null
FROM a LEFT JOIN b ON a.x = b.y   -> type=LEFT syntax=EXPLICIT conditionText=a.x = b.y

Keep the three representations separate:

1
2
3
conditionText = verbatim source representation
conditions    = decomposed semantic representation
rendered SQL  = a generated target-dialect representation

null does not mean that the join has no semantic condition. Inspect getConditions(), getUsingColumns(), isNatural(), and getSourceSyntax(). Conversely, non-null text does not guarantee that it is complete or valid as an ANSI ON expression in another dialect. A SQL rewriter should use the structured predicates as input to a dialect-aware, all-or-nothing renderer; it should not copy conditionText or concatenate predicate display strings as executable SQL.

JSON contract

The Semantic IR JSON exporter always writes sourceSyntax and conditions. It writes conditionText only when getConditionText() is non-null. A Java null is represented by an omitted key, never by "conditionText": null or an empty string. The missing key has the same meaning as the Java null described above and does not imply that the conditions array is empty.

Predicate carries a PredicateKind: EQUI, NON_EQUI, RANGE, NULL_CHECK, CALL, or COMPLEX.

Rule 1 — comma joins are promoted to INNER

When a top-level WHERE conjunct references relations on both sides of a comma boundary, that conjunct becomes a join condition and the boundary is classified INNER. This is not Oracle-specific — it applies to any dialect that allows a comma-separated FROM list, PostgreSQL included.

SQL Result
FROM a, b WHERE a.x = b.y INNER, condition (a.x = b.y) EQUI
FROM a, b WHERE a.x > b.lo AND a.x < b.hi INNER, two NON_EQUI conditions
FROM e, sg WHERE e.salary BETWEEN sg.lowest AND sg.highest INNER, one RANGE condition

A band join through BETWEEN is recognized even though its bounds collapse into a single COMPLEX operand:

1
2
3
SELECT e.salary, sg.grade
  FROM employees e, salary_grades sg
 WHERE e.salary BETWEEN sg.lowest AND sg.highest

1
2
join #0 type=INNER syntax=COMMA
    condition (e.salary BETWEEN COMPLEX) RANGE

Only the linking predicate is promoted

Predicates that touch just one side stay in getFilterPredicates():

1
SELECT a.c FROM a, b WHERE a.x = b.y AND a.z = 5

1
2
3
join #0 type=INNER syntax=COMMA
    condition (a.x = b.y) EQUI
filter (a.z = LITERAL) EQUI

A true cartesian product stays IMPLICIT_CROSS

Nothing is invented. Without a linking predicate the boundary keeps its literal meaning:

1
2
3
SELECT a.c FROM a, b                    -- join #0 type=IMPLICIT_CROSS
SELECT a.c FROM a, b WHERE a.x = 10     -- join #0 type=IMPLICIT_CROSS
                                        -- filter (a.x = LITERAL) EQUI

IMPLICIT_CROSS therefore means "written as a comma join, and genuinely unconstrained" — distinct from CROSS, which is an explicit CROSS JOIN.

Each boundary is classified independently

With three tables there are two boundaries, and a predicate is attached at the boundary where its last relation becomes available. A predicate spanning a and c cannot be attached at the a/b boundary, because c is not in scope yet:

1
SELECT a.c FROM a, b, c WHERE a.x = c.y

1
2
3
join #0 type=IMPLICIT_CROSS syntax=COMMA
join #1 type=INNER          syntax=COMMA
    condition (a.x = c.y) EQUI

Link both boundaries and both are promoted:

1
SELECT a.c FROM a, b, c WHERE a.x = b.y AND b.z = c.w

1
2
3
4
join #0 type=INNER syntax=COMMA
    condition (a.x = b.y) EQUI
join #1 type=INNER syntax=COMMA
    condition (b.z = c.w) EQUI

Rule 2 — Oracle (+) becomes LEFT or RIGHT

Oracle marks the null-producing side of an outer join with (+). Join analysis detects the marker and classifies the boundary accordingly.

The direction is the opposite of where the marker sits

The (+) marks the side that may produce nulls, so the other side is the preserved one — and the join is named after the preserved side.

SQL Marked side Result
WHERE a.x = b.y(+) right (b) LEFT — a is preserved
WHERE a.x(+) = b.y left (a) RIGHT — b is preserved

Both are equivalent to a LEFT JOIN b and a RIGHT JOIN b respectively.

1
SELECT a.c FROM a, b WHERE a.x = b.y(+)

1
2
join #0 type=LEFT syntax=COMMA
    condition (a.x = b.y) EQUI

The marker is found even when the column is parenthesized — WHERE a.x = (b.y(+)) gives the same LEFT result.

Unmarked predicates stay filters on an outer join

This matches Oracle's own semantics: on an outer join, only (+)-marked predicates are join conditions. An unmarked two-sided predicate is a WHERE filter, and it changes the result — it is applied after the outer join:

1
SELECT a.c FROM a, b WHERE a.id = b.id(+) AND a.type = b.type

1
2
3
join #0 type=LEFT syntax=COMMA
    condition (a.id = b.id) EQUI
filter (a.type = b.type) EQUI

Rule 3 — invalid (+) degrades to IMPLICIT_CROSS

Some (+) usages are rejected by Oracle itself. Rather than failing the whole analysis, join analysis degrades the boundary to IMPLICIT_CROSS and returns the predicate as a filter, so nothing is silently dropped:

SQL Why it degrades
WHERE a.x(+) = b.y(+) (+) on both sides — Oracle raises ORA-01468
WHERE a.x(+) = b.y AND a.z = b.w(+) Conflicting directions on one boundary
WHERE a.x BETWEEN b.lo(+) AND b.hi Marker cannot be classified
WHERE a.x(+) = 5 Single-sided — no second relation to join to 
1
SELECT a.c FROM a, b WHERE a.x(+) = b.y(+)

1
2
join #0 type=IMPLICIT_CROSS syntax=COMMA
filter (a.x = b.y) EQUI

A recovered graph is not a successful validation result

The compatibility builder preserves this historical CROSS/FILTER shape, but it also emits structured diagnostics. Recognized Oracle-invalid forms carry category=DIALECT_SEMANTIC_ERROR and an exact vendor error such as ORA-01468 or ORA-01719; a shape that GSP cannot lower without proving it Oracle-invalid uses category=LOWERING_UNSUPPORTED and has no invented ORA code.

Prefer SemanticIRBuilder.buildResult(...) so the recovered program, diagnostics, and recovery metadata are observed atomically. Legacy build(...) callers must read drainBuildDiagnostics() after every call. SqlSemanticAnalyzer is strict: it does not publish a SemanticProgram when these diagnostics contain an error.

Known limitations

Limitation Effect Tracking
CROSS JOIN, JOIN ... USING and NATURAL JOIN inside scalar / EXISTS/IN / set-operation-branch bodies are not supported SemanticIRBuilder.build() throws SemanticIRBuildException —
A FROM-clause subquery inside those same bodies is not supported SemanticIRBuilder.build() throws SemanticIRBuildException —
An unsupported inner block aborts the whole build() call The outer block's join graph is lost too — there is currently no partial-result mode #708

Rewriting the affected subquery to use explicit JOIN ... ON is the current workaround for the first two.

Comma-separated FROM inside EXISTS/IN subquery bodies, scalar subquery bodies and set-operation branches was also rejected until #707 was fixed. It now analyzes: the body's join graph is identical to the same shape written with JOIN ... ON, except that getSourceSyntax() reports COMMA.

Reference: outcome by input

Every row below is produced by the current release. Vendor is Oracle unless noted.

SQL fragment getJoinType() getSourceSyntax() Conditions Filters
FROM a, b IMPLICIT_CROSS COMMA — —
FROM a, b WHERE a.x = 10 IMPLICIT_CROSS COMMA — (a.x = LITERAL) EQUI
FROM a, b WHERE a.x = b.y INNER COMMA (a.x = b.y) EQUI —
FROM a, b WHERE a.x = b.y AND a.z = 5 INNER COMMA (a.x = b.y) EQUI (a.z = LITERAL) EQUI
FROM a, b WHERE a.x > b.lo AND a.x < b.hi INNER COMMA two NON_EQUI —
FROM e, sg WHERE e.salary BETWEEN sg.lowest AND sg.highest INNER COMMA one RANGE —
FROM a, b WHERE a.x = b.y(+) LEFT COMMA (a.x = b.y) EQUI —
FROM a, b WHERE a.x(+) = b.y RIGHT COMMA (a.x = b.y) EQUI —
FROM a, b WHERE a.x = (b.y(+)) LEFT COMMA (a.x = b.y) EQUI —
FROM a, b WHERE a.id = b.id(+) AND a.type = b.type LEFT COMMA (a.id = b.id) EQUI (a.type = b.type) EQUI
FROM a, b WHERE a.x(+) = b.y(+) IMPLICIT_CROSS COMMA — (a.x = b.y) EQUI
FROM a, b WHERE a.x(+) = b.y AND a.z = b.w(+) IMPLICIT_CROSS COMMA — two EQUI
FROM a, b WHERE a.x BETWEEN b.lo(+) AND b.hi IMPLICIT_CROSS COMMA — one RANGE
FROM a LEFT JOIN b ON a.x = b.y LEFT EXPLICIT (a.x = b.y) EQUI —

See also