Why Graph Queries in SQL Matter

display the article:

Why Graph Queries in SQL Matter

Many critical business problems – fraud detection, supply chain traceability, social network analysis, impact assessment – are inherently graph problems. Traditionally, solving these in a relational database meant writing painful recursive CTEs, multi-level self-joins, or shipping data to a separate graph database. Oracle AI Database 26ai changes this fundamentally with first-class support for SQL/PGQ (Property Graph Queries), defined by the SQL:2023 standard.

SQL/PGQ lets you define property graphs over your existing relational tables and query them using intuitive ASCII-art pattern syntax – all within standard SQL. No ETL or separate graph-data copy is required; the SQL property graph is defined over the existing relational tables and your graph queries run against the existing Oracle database infrastructure and security model.

Note: Oracle also provides a separate Graph Server (PGX) for broader graph analytics and visualization. This article focuses specifically on SQL property graphs queried directly through GRAPH_TABLE, with in-database algorithms available through DBMS_OGA.

SQL/PGQ vs. PGQL — An Important Distinction

If you have worked with Oracle graph features before Oracle AI Database 26ai, you may have used PGQL (Property Graph Query Language), Oracle’s earlier graph query language. PGQL remains available for querying PGQL property graphs and can also be used with SQL property graphs in supported Oracle graph environments, while SQL/PGQ is the SQL-standard approach for querying SQL property graphs through GRAPH_TABLE.

The table below summarizes which Oracle graph capability to reach for depending on your requirement:

Requirement	Oracle Capability
Define graph over relational tables	CREATE PROPERTY GRAPH
Pattern matching	SQL/PGQ + GRAPH_TABLE
Variable-length traversal	SQL/PGQ quantified paths
SQL aggregation over paths	GRAPH_TABLE
In-database graph algorithms	DBMS_OGA + GRAPH_TABLE
Shortest-path algorithms	DBMS_OGA or Graph Server / PGX
PageRank	DBMS_OGA or Graph Server / PGX
Community detection	Graph Server / PGX
Graph visualization	Graph Server / PGX and Graph Studio, where available

Defining a Property Graph Over Existing Tables

The CREATE PROPERTY GRAPH statement defines a SQL property graph over your existing relational tables — specifying vertices (nodes) and edges (relationships). This is a property graph definition, not a regular Oracle VIEW.

-- Existing relational tables
CREATE TABLE persons (
    person_id   NUMBER PRIMARY KEY,
    name        VARCHAR2(100),
    role        VARCHAR2(50)
);

CREATE TABLE transactions (
    txn_id      NUMBER PRIMARY KEY,
    sender_id   NUMBER REFERENCES persons(person_id),
    receiver_id NUMBER REFERENCES persons(person_id),
    amount      NUMBER,
    txn_date    DATE
);

-- Define a SQL property graph over them
CREATE PROPERTY GRAPH financial_graph
    VERTEX TABLES (
        persons KEY (person_id)
            PROPERTIES (name, role)
    )
    EDGE TABLES (
        transactions KEY (txn_id)
            SOURCE KEY (sender_id) REFERENCES persons (person_id)
            DESTINATION KEY (receiver_id) REFERENCES persons (person_id)
            PROPERTIES (amount, txn_date)
    );

The graph is a logical definition over your data. The property graph provides a graph-oriented semantic layer over relational data; it does not convert the underlying tables into a different storage model. Your data stays in persons and transactions — fully indexed, secured, and backed up as usual. Only the columns listed in PROPERTIES are visible to graph queries; all other columns remain accessible through normal SQL.

Basic Pattern Matching with GRAPH_TABLE

Use the GRAPH_TABLE operator with a MATCH clause to traverse relationships using intuitive arrow syntax.

-- Find all direct transactions from Alice
SELECT *
FROM GRAPH_TABLE ( financial_graph
    MATCH (sender IS persons) -[t IS transactions]-> (receiver IS persons)
    WHERE sender.name = 'Alice'
    COLUMNS (
        sender.name   AS sender_name,
        receiver.name AS receiver_name,
        t.amount      AS txn_amount,
        t.txn_date    AS txn_date
    )
)
ORDER BY txn_amount DESC;

The pattern (sender)-[t]->(receiver) reads naturally: find a sender node connected via a transaction edge to a receiver node. No self-joins, no subqueries. The result is a standard SQL rowset you can filter, aggregate, and join like any other query.

Multi-Hop Path Traversal with Quantified Patterns

SQL/PGQ supports variable-length path traversal using quantifiers such as {2,5}. Quantifiers can be applied directly to an edge pattern or to a parenthesized path pattern, depending on the complexity of the path being expressed. This is useful for finding indirect relationships — for example, transaction cycles that may indicate suspicious activity.

-- Detect 2-to-5 hop transaction cycles that may indicate suspicious activity
SELECT ring_participant, person_id
FROM GRAPH_TABLE ( financial_graph
    MATCH (start IS persons)
          ( -[t IS transactions WHERE t.amount > 10000]-> ){2,5}
          (start)
    WHERE COUNT(edge_id(t)) = COUNT(DISTINCT edge_id(t))
    COLUMNS (
        start.name      AS ring_participant,
        start.person_id AS person_id
    )
);

The quantifier {2,5} means: follow between 2 and 5 hops along transaction edges. Ending at (start) — the same starting node — identifies cycles. The WHERE COUNT(edge_id(t)) = COUNT(DISTINCT edge_id(t)) condition filters out paths that traverse the same edge more than once, making the cycle detection more meaningful. Note that this detects potential suspicious cycles, not confirmed fraud; further investigation is always required.

Important: Variable-length path matching goals such as ANY SHORTEST, ALL SHORTEST, and ANY CHEAPEST are not supported when querying SQL property graphs via GRAPH_TABLE. If you need shortest-path or other graph algorithms, Oracle AI Database 26ai provides in-database graph algorithm functions through DBMS_OGA, while Graph Server (PGX) provides a broader set of graph analytics algorithms and capabilities.

Counting Hops and Aggregating Across a Variable-Length Path

You can use aggregate functions within GRAPH_TABLE to compute information across the matched path. When aggregating over a quantified path, the aggregate expression operates on values associated with the matched graph elements.

-- Find all paths of 1 to 4 hops from Alice, counting hops and total amounts
SELECT *
FROM GRAPH_TABLE ( financial_graph
    MATCH (src IS persons WHERE src.name = 'Alice')
          ( -[t IS transactions]-> ){1,4}
          (dst IS persons)
    COLUMNS (
        src.name              AS source,
        dst.name              AS destination,
        COUNT(edge_id(t))     AS hop_count,
        SUM(t.amount)         AS total_amount
    )
)
ORDER BY hop_count, total_amount DESC;

Here COUNT(edge_id(t)) counts the number of edges matched across all hops, and SUM(t.amount) totals the transaction amounts along the path. Note that COUNT(edge_id(t)) is the correct form — COUNT(t) is not supported.

Key Takeaways

• SQL property graph definition, not a view: CREATE PROPERTY GRAPH defines a graph-oriented semantic layer over existing relational tables — distinct from a regular Oracle VIEW and without converting the underlying tables into a different storage model.
• SQL:2023 standard: SQL/PGQ in Oracle AI Database 26ai is part of the ISO/IEC SQL:2023 standard. PGQL remains available for PGQL property graphs and supported graph environments, while SQL/PGQ is the standard approach for in-database SQL property graph queries.
• Intuitive pattern syntax: ASCII-art patterns like (a)-[e]->(b) replace complex recursive CTEs and multi-level self-joins for relationship traversal.
• Quantified paths: Variable-length traversal uses quantifiers such as {n,m}, applied directly to an edge pattern or to a parenthesized path pattern. Use COUNT(edge_id(e)) = COUNT(DISTINCT edge_id(e)) to filter out paths that traverse the same edge more than once.
• In-database algorithms: Use DBMS_OGA for supported graph algorithms — including Bellman-Ford, PageRank, and weakly connected components (WCC) — on SQL property graphs, with the algorithm results exposed through SQL. Use Graph Server (PGX) when you need its broader graph analytics capabilities and visualization ecosystem.
• Unsupported goals: ANY SHORTEST, ALL SHORTEST, and similar path matching goals are not supported in GRAPH_TABLE for SQL property graphs.
• Full SQL integration: Because SQL/PGQ is integrated into Oracle SQL, it is accessible through SQL*Plus, SQLcl, SQL Developer, and JDBC, while benefiting from Oracle’s security model and database infrastructure.

Oracle AI Database 26ai makes graph a native SQL capability built on an international standard. SQL/PGQ provides a practical starting point for exploring relationship-rich data directly in Oracle — without requiring a separate graph database or moving your relational data.