Hi,
The database Neo4j has a language called "Cypher" where one of the key selling points is they "don’t need join tables".
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