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.

Availability and stability

Join analysis lives in gudusoft.gsqlparser.ir.semantic, which is separate from the parse tree. TGSqlParser.parse() and the TSelectSqlStatement AST are unchanged by it — a comma join still appears in the AST as a comma join. If you inspect only the AST you will see no difference.

This package does not currently carry the same backward-compatibility guarantee as gudusoft.gsqlparser, .nodes, .stmt and .util. Check the release notes when upgrading.

Quick start

Join analysis runs on a parsed statement, not on SQL text. Parse first, then build the semantic program:

 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
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.stmt.TSelectSqlStatement;
import gudusoft.gsqlparser.ir.semantic.SemanticProgram;
import gudusoft.gsqlparser.ir.semantic.StatementGraph;
import gudusoft.gsqlparser.ir.semantic.builder.SemanticIRBuilder;
import gudusoft.gsqlparser.ir.semantic.binding.Resolver2NameBindingProvider;
import gudusoft.gsqlparser.ir.semantic.joinanalysis.JoinAnalysisFacts;
import gudusoft.gsqlparser.ir.semantic.joinanalysis.JoinEntity;
import gudusoft.gsqlparser.ir.semantic.joinanalysis.Predicate;

TGSqlParser parser = new TGSqlParser(EDbVendor.dbvoracle);
parser.sqltext = "SELECT e.id, d.name FROM employees e, departments d "
               + "WHERE e.dept_id = d.id";
if (parser.parse() != 0) {
    throw new IllegalStateException(parser.getErrormessage());
}

SemanticProgram program = SemanticIRBuilder.build(
        (TSelectSqlStatement) parser.sqlstatements.get(0),
        new Resolver2NameBindingProvider());

for (StatementGraph graph : program.getStatements()) {
    JoinAnalysisFacts facts = graph.getJoinAnalysisFacts();
    if (facts == null || facts.isEmpty()) {
        continue;
    }
    for (JoinEntity join : facts.getJoinGraph().getJoins()) {
        System.out.println("join #" + join.getOrder()
                + " type=" + join.getJoinType()
                + " syntax=" + join.getSourceSyntax());
        for (Predicate condition : join.getConditions()) {
            System.out.println("    condition " + condition);
        }
    }
    for (Predicate filter : facts.getFilterPredicates()) {
        System.out.println("filter " + filter);
    }
}

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 SemanticIRBuilder.build(stmt, provider) SemanticProgram
2 program.getStatements() List<StatementGraph> — one per query block
3 graph.getJoinAnalysisFacts() JoinAnalysisFacts, or null
4 facts.getJoinGraph().getJoins() List<JoinEntity> — one per join boundary
5 facts.getFilterPredicates() List<Predicate> — predicates that are not join conditions

Always null-check step 3 and call facts.isEmpty(): a single-table query has no join boundaries.

Name binding

SemanticIRBuilder.build needs a NameBindingProvider. Use the no-argument Resolver2NameBindingProvider() when you have no catalog metadata — this is the normal case for analyzing SQL text on its own, and it is what every example on this page uses.

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() Source text of the ON clause. See the caveat 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.

getConditionText() is only populated for syntax=EXPLICIT

An implicit join has no literal ON text in the source, so getConditionText() returns null for syntax=COMMA even when getConditions() is 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

Use getConditions() — it is populated in both cases. If you need to emit an ANSI ON clause, build it from the Predicate list rather than from getConditionText().

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 degraded result is indistinguishable from a real cross join

In the output above, nothing records that a (+) marker was seen and discarded. The same shape is produced by WHERE a.x = b.y, which is valid SQL with a completely different meaning.

If your SQL may contain invalid (+) usage, validate it before analysis rather than trying to detect the degradation afterwards. Emitting a diagnostic on this path is tracked as issue #709.

Known limitations

Limitation Effect Tracking
Comma-separated FROM inside EXISTS/IN subquery bodies and set-operation branches is not supported SemanticIRBuilder.build() throws SemanticIRBuildException #707
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
Invalid (+) usage produces no diagnostic See the warning above #709
getConditionText() is null for syntax=COMMA Use getConditions() instead #711

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

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