Simplifying Join Syntax • Hacker News Board

Re: Simplifying Join Syntax

#21

Post by twic » Fri, May 17, 2024, 5:05 PM UTC

maweki wrote: post #9

This implicit JOIN feature is already present in HQL and also in Jooq. It's very convenient, especially when you use a star-like table structure where the important data table is basically just IDs and references. https://www.jooq.org/doc/latest/manual/coming-from-jpa/from-...

Yes, this is a lot like path expressions in HQL / JPQL.

There is a key difference, i think - in JPA land, relationships between classes are named at both ends. So an OrderDetail has an "Order order" field, but an Order also has a "Set details" field. That means you already have a name to use when going from an order to its details - you can say "sum(order.details.price)". Whereas the language in the article has to make it implicit, with (IMHO weird!) syntax like "OrderDetail.sum(price)".

This starts to hurt more when you have multiple collections of the same type on an entity. Consider:

  City
    city_id
  Person
    birth_city_id
    residence_city_id

What would

  SELECT city_id, Person.COUNT()
  FROM City

do here?

Re: Simplifying Join Syntax

#22

Post by twic » Fri, May 17, 2024, 5:09 PM UTC

> Both tables use id field as their primary keys. Managers are also employees, so the two tables share the ids. Since managers have more attributes, their information is stored in a separate table.

> Now we want to find the total income (including the allowance) of each employee (including every manager).

> A JOIN operation is necessary for SQL to do it:

  > SELECT employee.id, employee.name, employy.salary+manager.allowance
  > FROM employee
  > LEFT JOIN manager ON employee.id=manager.id

> But for two tables having a one-to-one relationship, we can treat them like one table:

  > SELECT id,name,salary+allowance
  > FROM employee

What about employees who aren't managers? I assume they have no entry in the manager table. The SQL would ignore them, because it's a left join, which is not what was asked for. Does the proposed query do the same?

What happens if there is also

  salesperson table
    id
    allowance

? Which table is joined?

This language seems a little half-baked.

Re: Simplifying Join Syntax

#23

Post by atombender » Fri, May 17, 2024, 7:23 PM UTC

david_p wrote: post #8

I love the idea of a simplified SQL syntax for joins. I have been working with graph databases for years now: these databases had to solve this problem from day one, because of the focus on relationships between entities. I must point out that Neo4j was the first to propose a syntax that made traversal feel simple and natural again: the Cypher query language. Neo4 and other industry players have spent years working o…

I think InterSystems Caché did it first. Its SQL join syntax lets you do `WHERE employee->manager->manager->name`. I'm not sure it has arbitrary recursion like you can do with graph databases, however.

Re: Simplifying Join Syntax

#24

Post by ghusbands » Fri, May 17, 2024, 7:29 PM UTC

atemerev wrote: post #13

See “natural join”, which already exists in SQL; I use it all the time.

Natural joins are terrible in that they match on any equal column names. Let's hope you don't have employees with names and departments with names.

In any case, even with natural join syntax, you end up with queries longer than those in the article.

Re: Simplifying Join Syntax

#25

Post by ghusbands » Fri, May 17, 2024, 7:49 PM UTC

david_p wrote: post #8

I love the idea of a simplified SQL syntax for joins. I have been working with graph databases for years now: these databases had to solve this problem from day one, because of the focus on relationships between entities. I must point out that Neo4j was the first to propose a syntax that made traversal feel simple and natural again: the Cypher query language. Neo4 and other industry players have spent years working o…

Graph databases aren't particularly relevant to a novel syntax for relational databases. The part of the related standard that is about relational databases (SQL/PGQ) demands that every table hold either edges or vertices, but not both [1], which is very limiting.

[1] https://peter.eisentraut.org/blog/2023/04/04/sql-2023-is-fin...

Re: Simplifying Join Syntax

#26

Post by ghusbands » Fri, May 17, 2024, 8:14 PM UTC

funcDropShadow wrote: post #12

The presented idea shortens the given examples, but is not composable. What happens if you have 1:N instead of 1:1 relation? Or even a N:M relation. Where do you specify whether you want an innner / outer / left / right join? This proposal works for some simple queries but fails to capture the generality of the relational model. So, from a language development point of view, one has to ask: Is this special case worth…

> All in all, I would not call this a simplification.

It is a simplification, as it gives you less that you need to understand and decode. Working with a query with ten to twenty joins and join conditions, you have to juggle a lot of intermixed concerns and you probably have to read a lot of query text and table/column definitions. And you have to look back and forth to know which tables are pulled in for checks and which for data. For example, this with just four tables:

  SELECT alpha.*, epsilon.zoo FROM beta INNER JOIN alpha ON beta.id=alpha.foo LEFT JOIN epsilon ON alpha.boing=epsilon.id INNER JOIN gamma ON gamma.id = beta.bar WHERE gamma.baz='bar'

Requires you to understand more about everything and spreads things out more than this equivalent:

  SELECT *, boing.zoo FROM alpha WHERE foo.bar.baz='qux'

(This is based on a real query I was given to work with.)

Re: Simplifying Join Syntax

#27

Post by ghusbands » Fri, May 17, 2024, 8:43 PM UTC

zeronone wrote: post #15

jOOQ has similar feature https://www.jooq.org/doc/latest/manual/code-generation/codeg...

Many ORMs have similar features - jOOQ is not unique in this. The article is talking about an addition to SQL syntax, not a DSL embedded in another language.

Re: Simplifying Join Syntax

#28

Post by ghusbands » Fri, May 17, 2024, 9:23 PM UTC

beagle3 wrote: post #6

A 30 year old implementation of the same idea (even slightly better IMHO) can be found in kdb+/ksql/shakti, you can see examples and comparison in https://shakti.com/ (press document, then sql.d - no idea how to link directly). And yes, it is by Arthur Whitney and related to the [in]famous K

The direct link is https://shakti.com/document/sql.d