Idea: Avoid JOINs by using path expressions to follow FKs

Hi,

The database Neo4j has a language called "Cypher" where one of the key selling points is they "don’t need join tables".

Here is an example from https://neo4j.com/developer/cypher/guide-sql-to-cypher/

SQL:

SELECT DISTINCT c.company_name
FROM customers AS c
JOIN orders AS o ON c.customer_id = o.customer_id
JOIN order_details AS od ON o.order_id = od.order_id
JOIN products AS p ON od.product_id = p.product_id
WHERE p.product_name = 'Chocolade';

Neo4j's Cypher:

MATCH (p:product {product_name:"Chocolade"})<-[:PRODUCT]-(:order)<-[:PURCHASED]-(c:customer)
RETURN distinct c.company_name;

Imagine if we could simply write the SQL query like this:

SELECT DISTINCT od.order_id.customer_id.company_name
FROM order_details AS od
WHERE od.product_id.product_name = 'Chocolade';

I took the inspiration for this syntax from SQL/JSON path expressions.

Since there is only a single foreign key on the order_details.order_id column,
we would know how to resolve it, i.e. to the orders table,
and from there we would follow the customer_id column to the customers table,
where we would finally get the company_name value.

In the where clause, we would follow the order_details's product_id column
to the products table, to filter on product_name.

If there would be multiple foreign keys on a column we try to follow,
the query planner would throw an error forcing the user to use explicit joins instead.

I think this syntactic sugar could save a lot of unnecessary typing,
and as long as the column names are chosen wisely,
the path expression will be just as readable as the manual JOINs would be.

Thoughts?

/Joel