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 regularly do this type of thing via views. It's a bit confusing as writes go to one set of tables while selects often go through the view with all the details readily available.
I think I'd want these shortcuts to be well defined and obvious to someone exploring via psql. I can also see uses where a foreign key might not be available (left join rather than join).
I wonder if GENERATED ... VIRTUAL might be a way of defining this type of added record.
ALTER TABLE order ADD customer record GENERATED JOIN customer USING (customer_id) VIRTUAL;
ALTER TABLE order_detail ADD order record GENERATED JOIN order USING (order_id) VIRTUAL;
SELECT order.customer.company_name FROM order_detail;
Of course, if they don't reference the GENERATED column then the join isn't added to the query.
--