Querying Graphs with SQL and PGQL: What is the difference?
November 14, 2023 | 5 minute readWith the availability of Oracle Database 23c on OCI Oracle Base Database Service, developers can use SQL to work with Property Graphs. The SQL:2023 standard was published in June 2023 and includes new syntax for property graphs defined in the standard's SQL/PGQ section. Oracle has implemented parts of the standard in Oracle Database 23c.
When using Oracle Database 19c or earlier, developers use PGQL to query graphs in Oracle Database. PGQL is the Property Graph Query Language, an open-source graph query language (see pgql-lang.org) that Oracle supported while working with the SQL standards committee to develop property graph syntax in SQL.
I hope this post helps clarify the differences and similarities between PGQL support and SQL support for graphs in Oracle Database.
As the SQL standard was being developed, we aligned the syntax of PGQL with the emerging syntax in the SQL standard. PGQL 2.0 syntax for GRAPH_TABLE and CREATE PROPERTY GRAPH is identical to the finalized SQL syntax. But note that PGQL has existed longer than the graph support in SQL and, at the moment, has a larger set of graph-related features when compared to SQL in Oracle Database 23c. We plan to continuously add features to SQL to close this gap over time. Also note that SQL has a much larger set of non-graph features that can be combined with graph features, so using SQL might well satisfy the requirements of your application. If you develop your applications with PGQL and want to migrate to SQL in the future, be sure to use PGQL 2.0. It will then be easy to migrate your application to SQL.
The SQL syntax for property graphs is supported in Oracle Database 23c. PGQL is supported in Oracle Database 23c and earlier database releases (innovation release Oracle Database 21c and long-term releases Oracle Database 19c and Oracle Database 12g Release 2).
What are some of the key differences between PGQL and SQL?
|
PGQL |
SQL |
|
Graphs created using PGQL are referred to as PGQL property graphs. They are queried using PGQL, they cannot be queried using SQL. |
Graphs created using SQL are referred to as SQL property graphs. They are queried using SQL. |
|
At the moment, PGQL has a larger set of graph query features (because it has been in existence longer). |
SQL property graph features in Oracle Database 23c is a subset of what is supported in PGQL. We plan to continuously add features to SQL to close this gap. |
|
PGQL queries are parsed on the client. The required client libraries are available with Oracle Graph Server and Client (released four times a year). |
SQL queries are parsed in the Oracle SQL engine, like any other SQL query. Additional client libraries are not required.
|
|
Property graphs created and queried with PGQL are a collection of metadata tables. They are not a first class object of the database. For example, you cannot grant a privilege to a property graph, it has to be granted to the underlying tables.
|
Property graphs created and queried with SQL are first class objects in Oracle Database. They can be managed in the database like any database object such as a table. |
|
PGQL property graphs are supported from Oracle Database 12.2 onward. |
SQL property graphs are supported from Oracle Database 23c onward.
|
|
PGQL property graphs and SQL property graphs can both be loaded into Oracle Graph Server (PGX) for advanced graph analytics. Oracle Graph Server is an innovation from Oracle Labs designed to run complex graph analytics algorithms at extremely high speeds. |
|
|
Additional graph tools, such as the graph visualization tool shipped with Oracle Graph Server and Client (released four times a year), work with both PGQL property graphs and SQL property graphs. |
|
How should I choose whether I use PGQL or SQL?
For all database versions before Oracle Database 23c, you have to use PGQL and create PGQL property graphs.
In Oracle Database 23c, now available on OCI Oracle Base Database Service and Oracle Database 23c Free (and coming soon everywhere), you can use SQL to create and query SQL property graphs. Working with a native property graph object in SQL has several advantages. As discussed above, the SQL for graph features in Oracle Database 23c is currently a subset of the PGQL features, but using non-graph features in SQL in combination with graph-specific SQL features will satisfy the requirements of many applications. If you need graph-specific features not yet supported in SQL in Oracle Database 23c, you can use PGQL and create PGQL property graphs. A migration tool from PGQL property graphs to SQL property graphs is planned.
Note that a PGQL property graph and a SQL property graph can be created from the same set of tables. There is no copy of data, so there is no cost to creating two different types of graphs from one set of tables. During the development phase you can easily work with both graph models to evaluate both options.
For more information
Visit our documentation to find more information. You can refer to SQL for graphs in Part II of the Graph Developer’s Guide for Property Graph, additional information is in the SQL reference documentation. And you can refer to PGQL in Part III of the Graph Developer’s Guide for Property Graph, additional information is on the PGQL specification page.
You can also visit our page on oracle.com, our blogs on Oracle Database Insider and medium.com, or our YouTube channel.
Melliyal Annamalai
Melli Annamalai is a distinguished product manager at Oracle. Starting from her career as a developer, she has extensive experience in working with different types of data in the database, including multimedia data, data in Apache Hadoop platforms, streaming data, property graphs, and RDF knowledge graphs. Her current focus area is graph database and analytics, in particular on ways to make it easy for developers to use graphs in any application.