Hacker News Reader

Comment by sa46 | Hacker News Reader

I wish there were a way to specify the expected join cardinality syntactically for 1:1 joins.

    SELECT * FROM order JOIN_ONE customer USING (customer_id)

Malloy has join_one: https://docs.malloydata.dev/documentation/language/join

View on HN

Don't have a suitable database to test on but I'm pretty sure standard SQL allows you to join on a subselect that limits its result to one.

You probably want to use LATERAL JOIN, which will compute the subquery for each left hand row. Otherwise it's computed just once.

With SQL there are commonly several options available, and tradeoffs might not be obvious until it's tested against a real data set. Sometimes even an EXPLAIN that looks good still has some drawback in production, though in my experience it's very rare.

I like that though, I have a preference for REPL style development and fleshing things out interactively. Enough so that I build Java like that, hacking things out directly in tests.

Reply on news.ycombinator.com