[halfcat]

> ”but rather that unless the join is fully-specified those ways will mostly produce different results.”

As a SQL non-expert, I think this is why we are averse to joins, because it’s easy to end up with more rows in the result than you intended, and it’s not always clear how to verify you haven’t ended up in this scenario.

[sa46]

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

[cess11]

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.

[singron]

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

[cess11]

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.

[big_whack]

Sorry, but someone who is averse to joins is not a non-expert in SQL, they are a total novice. The answer is like any other programming language. You simply must learn the language fundamentals in order to use it.

[halfcat]

Not sorry, I’ll stick with SQL non-expert as I’ve only worked with databases for a few decades and sometimes run into people who know more.

Working with a database you built or can control is kind of a simplistic example.

In my experience this most often arises when it’s someone else’s database or API you’re interacting with and do not control.

An upstream ERP system doesn’t have unique keys, or it changes the name of a column, or an accounting person adds a custom field, or the accounting system does claim to have unique identifiers but gets upgraded and changes the unique row identifiers that were never supposed to change, or a user deletes a record and recreates the same record with the same name, which now has a different ID so the data in your reporting database no longer has the correct foreign keys, and some of the data has to be cross-referenced from the ERP system with a CRM that only has a flaky API and the only way to get the data is by pulling a CSV report file from an email, which doesn’t have the same field names to reliably correlate the data with the ERP, and worse the CRM makes these user-editable so one of your 200 sales people decides to use their own naming scheme or makes a typo and we have 10 different ways of spelling “New York”, “new york”, “NY”, “newyork2”, “now york”, and yeah…

Turns out you can sometimes end up with extra rows despite your best efforts and that SQL isn’t always the best tool for joining data, and no I’m not interested in helping you troubleshoot your 7-page SQL query that’s stacked on top of multiple layers of nested SQL views that’s giving you too many rows. You might even say I’m averse.

[consteval]

Yes when you have duplicated data and data inconsistencies/integrity issues you might get duplicate data and data inconsistencies/integrity issues in your output.

This is a problem of form, not method. JOINs are a fantastic, well-defined method to aggregate data. If the form of your data is messed up, then naturally the result may be too.

> no I’m not interested in helping you troubleshoot your 7-page SQL query that’s stacked on top of multiple layers of nested SQL views that’s giving you too many rows

People say this type of thing but SQL is an incredibly high-level language.

Yes debugging a big SQL query can suck. Debugging the equivalent of it is almost always much worse. I'd rather debug a 30-line query than a 400 line perl script that does the same thing. Because that's actually the alternative.

I have manually aggregated data, messy data, in awk, perl, python... it is much, much worse.

[halfcat]

> ”I have manually aggregated data, messy data, in awk, perl, python... it is much, much worse.”

Yes, but with Python/etc you can at least do the same logic in a plain loop, which is much slower but serves as a more reliable reference of what the data is supposed to be, which can be used to validate the functionality of SQL output.

Is there an equivalent in SQL of this “slow and dumb” approach for validating? Like, I’m not sure if a lateral join is essentially doing the same thing under the hood.

[rawgabbit]

Most databases have the concept of temporary tables that will automatically disappear when your session ends. For troubleshooting, I would breakdown each step and save it in a temp table. Validate it. Then use it as the input for the next step. Etc.

[rawgabbit]

I have run into this scenario a few times where the multi-hour processes produces an explosion of rows that no one cared to troubleshoot further. They only wanted to de-duplicate the final result.

In practice, I ended up creating a skeleton table that not only had the appropriate constraints for de-duplication. But I would also create mostly empty rows (empty except for the required column key ID fields) with the exact rows they were expecting. And then I would perform an UPDATE <skeleton> ... FROM <complicated join>. It is a hack but if there was no desire to rewrite a process that was written years ago by teams of consultants, I can only do what I can do.

[yunolearn]

Nobody is entitled to any of this being easy. If you don't like working with badly-designed databases, why not simply work with people who know how databases work? In the meantime, I have bad news: nobody is going to do the hard work for you.

[yunolearn]

To verify, you have to think through the problem and arrive at a solution, like all of programming.