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.
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.
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.
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.
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.
SELECT * FROM order JOIN_ONE customer USING (customer_id)
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.
Join table that maps to an entity in the middle.
You can even have multiple columns that have foreign keys against various tables, like some_table_id, other_table_id, another_table_id with only the needed ones being filled out.
And in practice, this will be way more manageable than the dynamic mess of the OTLT pattern (table_name and table_id): https://www.red-gate.com/simple-talk/blogs/when-the-fever-is...
It's not like you have to particularly care about the fact that most of those columns will be empty in practice, as opposed to making your database hard to query or throwing constraints aside altogether.
I'm not not sure what you mean. Are you saying that instead of FK/PK relations like:
user.residence_country_id = country.id
plus user.citizenship_country_id = country.id
user.residence_link_id = link_user_residence.user_id
link_user_residence.country_id = country.id
user.citizenship_link_id = link_user_citizenship.user_id
link_user_citizenship.country_id = country.id
user.residence1 -> country
user.residence2 -> country
user.join(country)
I'm only really familiar with Django, and it handles this type of thing by making you specify the "residence1" name instead of the table name "country".
[0] To refer back to the top of this comment chain, that user is complaining they can't do something like also have a "country->continent" relationship and do "user.join(continent)" and have the system figure out the two joins needed.
user.residence1 -> country.id
user.residence2 -> country.id
bridge.userid-> user.id
bridge.countryid-> country.idRemember this whole thread is about the system figuring out the JOIN criteria automatically.
I mean, I think the issue is just that there are lots of possible paths so it can't be automated.
If you want to join employees to buildings, is it where their desk is assigned today, or where their desk was assigned two years ago, or where their team is based even though they work remotely, or where they last badged in?
Sure you can build a tool to find all potential joins based on foreign keys, but then how do you know which is correct unless you understand what the tables mean? And then if you understand what the tables mean, writing the join out yourself is trivial.
> Unless your schema is really fucked up, there should only be one or two actually sensible ways to join across multiple tables.
In my experience, having just one or two ways is for simple/toy projects. Lots of joins in no way means a schema is "fucked up". It probably just means it's correctly modeling actual relationships, correctly normalized.
... And god help you if someday Requisitions need to have Revisions too.
In this way you can use polynomially-many constraints or rules to avoid exponentially-many weird or exceptional routes through the schema. I am optimistic that you can build a system where by default, the autojoin solution is the natural one maybe 90% of the time. There will certainly be exceptions where you have to express the join conditions explicitly. But I think you can dramatically reduce the amount of code required.
I would also hazard the suggestion that this might produce productive backpressure on the system. If the autojoiner is struggling to find a good route through the schema, it's possible that the schema is not properly normalized or otherwise messed up.
That's because, inexplicably, devs by and large don't know SQL, and don't want to learn it. It's an absurdly simple language that should take a competent person a day to get a baseline level of knowledge, and perhaps a week to be extremely comfortable with.
As an aside, something you can do is create views (non-materialized) for whatever queries are desired. The counter-arguments to this are that is slows development velocity, but then, so does devs who don't know how to do joins.
I think there's a beautiful space for some SQL-like language that just operates a bit more like a general-purpose language in a more regular fashion. Bonus points for ones where you don't query tables but point at indexes or table scans and the like (resolving the "programmer writes query that is super non-performant because they assume an index is present when it's not").
I think it's still super straightforward to sit down and learn it, but it's really unfortunate that we spend a bunch of time in school learning data structures and then SQL tries really hard to hide all that, making it pretty opaque despite people intuitively understanding B-Trees or indexes.
Maybe it's easier if you think of it like helpful syntactic sugar?
> I think there's a beautiful space for some SQL-like language that just operates a bit more like a general-purpose language in a more regular fashion. Bonus points for ones where you don't query tables but point at indexes or table scans and the like
That sounds like imperative programming, which is fine for most things, but [generally] not RDBMS (or IaC, but that's a completely separate topic). You can't possibly know the state of a given table or index – the cardinality of columns, the relative grouping of tuples to one another, etc. While you can hint at index usage (natively with MySQL, via extension with Postgres), that's as close as the planner will let you get, because it knows these things better than you do.
> resolving the "programmer writes query that is super non-performant because they assume an index is present when it's not"
More frequently, I see "programmer writes query that is super non-performant because they haven't read the docs, and don't know the requirements for the planner to use the index." A few examples:
* Given a table with columns foo, bar, baz, with an index on (foo, bar), a query with a predicate on `bar` alone is [generally] non-sargeable. Postgres can do this, but it's rare, and unlikely to perform as well as you'd want anyway.
* Indices on columns are unlikely to be used for aggregations like GROUP BY, except in very specific circumstances for MySQL [0] (I'm not sure what limitations Postgres has on this).
* Not knowing that a leading wildcard on a predicate (e.g. `WHERE user_name LIKE '%ara'`) will, except under two circumstances [1], skip using an index.
> despite people intuitively understanding B-Trees or indexes.
You say that, but the sheer number of devs I've talked to who are unaware that UUIDv4 is an abysmally bad choice WRT performance for indices – primary or secondary – says otherwise.
[0]: https://dev.mysql.com/doc/refman/8.4/en/group-by-optimizatio...
[1]: Postgres can create trigram indices, which can search with these, at the expense of the index being quite large. Both MySQL and Postgres can make use of the REVERSE() function to create a reverse index on the column, which can then be used in a query with the username also reversed.
My serious belief is that all the SQL variants are generally great, but I just want this to be incremented with some lower-level language that I can be more explicit with, from time to time. If only because sometimes there are operational needs.
The fact that the best we get with this is planner _hints_ is still to this day surprising to me. Hints! I am in control of the machine, why shouldn't it just listen to me! (and to stop the "but random data analyst could break thing", this is why we have invented permission systems)
I don’t think that’s what I said. At least, it wasn’t what I meant.
The planner needs accurate stats on the tables to produce an optimal output. So would you, as a human, if you were writing a query to be executed precisely as written.
It also needs to know information about the system it’s operating in – the amount of RAM it can expect to use being the most important one, but also things like the relative performance of a random disk seek vs. sequential.
The planner also has a huge leg up on most people, in that it is aware of all of the kinds of joins and lookups it can do. Off the top of my head, Postgres can scan in the following ways: sequential scan, parallel sequential scan, index scan, parallel index scan, index-only scan, and bitmap scan. For joins, it can use: nested loop, merge, hash. Do you know when all of those are ideal? What about when you have a query joining multiple tables, keeping in mind that the intermediate contents of a table that has been joined once may look nothing like what it started as?
I don't think that last one is a thing. There is both "bitmap heap scan" and "bitmap index scan" though.
[0]: https://www.cybertec-postgresql.com/en/postgresql-indexing-i...
The one I see many times more often than this is having one index on (foo) and one index on (bar), then querying on both foo and bar, and assuming the database uses both indexes for best performance. It can be done, even by ancient mysql, but it's most likely going to be a lot slower than expected so they're heavily biased against doing it and will most likely just choose one of the two indexes.
To choose the best path to retrieve data, you have to know what are the possible paths (using the underlying data structures, indexes but also different algorithms to filter, join…), but you should also know some data metrics to evaluate if some shortcuts are worth it (a seq scan can be the best choice with a small table..).
And the thing that will trip most humans, is that you need to reevaluate the plan if the underlying assumptions change (data distribution has become something that you never expected).
Note that the planner is also NOT always right, it heavily relies on heuristics and data metrics that can be skewed or not up to date. Some databases allow the use of hints to choose an index or a specific path.
I understand the value of full declarative planning with heuristics, but sometimes the query writers do in fact have a better understanding of the data that will go in.
And beyond that, having consistent plans is actually better in some ideologies! Instead of a query suddenly changing tactics in a data- and time-dependent way, having consistent behavior at the planning phase means that you can apply general engineering maintenance tactics. Keep an eye on query perf, improve things that need to be improved... there are still the possibility of hitting absolutely nasty issues, but the fact that every SQL debugging session starts with "well we gotta run EXPLAIN first after the fact" is actually kind of odd!
That's one of the best things about SQL, it's declarative nature. I describe the end result, data I want to receive — not the instructions on how it should be done. There's no control flow, there's no program state, which means that my mental model of it is so much simpler.
I've read countless articles on how to rearrange the "declaration of what you want" in order to get the database to do it in a fast way.
I think of RDBMS like C: they’re massively capable and performant, but only if you know what you’re doing. They’re also very eager to catch everything on fire if you don’t.
While this is doubtlessly true, in many cases the “rearranging” also involves a subtle change in what you are asking the database to do, in ways which allow the database to actually do less work.
SELECT 1 WHERE EXISTS vs WHERE ID IN (SELECT ID FROM mytable WHERE …) is a great example. The former is a much simpler request despite functionally doing the same thing in the common use-cases.
MySQL, but not Postgres, will also convert the inverse to an antijoin.
As an aside, this is also often a great example to demonstrate why you shouldn’t use SELECT *, since you can’t write a semijoin if you need the predicate column in the result.
https://github.com/permazen/permazen
It maps Java objects to a scalable transactional K/V store of your choice, and handles things like indexing, schema migrations and the rest for you. You express query plans by hand using the Java collections and streams framework.
Incidentally I think it also addresses what a lot of the comments here are talking about: not learning JOINs, indexing, build-in relation cardinality constraints, etc, but that's a separate point!
This sounds like an EAV table, which is generally a bad idea.
Re: types, Postgres allows you to define whatever kind of type you’d like. Also re: inheritance, again, Postgres tables can inherit a parent. Not just FK linking, but literally schema inheritance. It’s rare that you’d want to do this, but you can.
In general, my view is that the supposed impedance mismatch is a good thing, and if anything, it forces the dev to think about less complicated ways to model their data. The relational model has stuck around because it’s incredibly good, not because nothing better has come around.
EDIT: this came across as quite harsh, and I’m sorry for the tone. Making a new product is hard. I’m just very jaded about anything trying to replace SQL, because I love it, it’s near-universal, and it hasn’t (nor is likely to) gone anywhere for quite some time.
One huge problem is that learning SQL will take you nowhere, unless your schema consists of a few tables with a handful of rows at most.
There is no standard SQL, and there is no serious application development without having to delve into the sanctum sanctorum of your database vendor.
There is a standard SQL, and vendors are free to add to it. Generally speaking, though, you can get quite far with the standard.
> there is no serious application development without having to delve into the sanctum sanctorum of your database vendor.
OK? I could say the same thing about needing to intimately know the differences in C++ versions. Yes, you should know your tools well.
So most enterprise schema who have outlived multiple applications. Usually due to time constraint, lack of database administrators and complex business needs. Excel is the king of databases for a reason.
But that's only the trivial case, often you want something more complicated, where the order of clauses or what keywords you pick and how you want the results sorted will affect performance in your specific schema on your specific database engine.
RDBMS management and querying is a rather deep and experience demanding subject once you step outside the trivial cases. You could put more of it in application code, but it will be a lot of chatter and probably worse outcomes in terms of performance than having people on the team that are really good with databases.
That knowledge gets you to a place to understand that most joins are very slow due to each DB page containing only 1% useful information for the query algorithm.
It will help you see reasons why ppl do things like put an array column in, or use wide tables, etc.
We have database performance issues for other reasons (loading of redundant information, too many queries) but not really because of joins.
You are primarily describing natural join here. It won’t read your mind and join your tables automatically, but it automatically uses shared columns as join keys and removes the duplicate keys from the result.
The problem with any “auto” solution is going to be things like modified dates, which may exist in multiple tables but aren’t shared.
Even more magic is natural semi join and natural anti join.
Oracle: https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnaturalj... MySQL: https://dev.mysql.com/doc/refman/8.4/en/join.html
I think EdgeDB [0] is on the right track here. It's a database built on Postgres, but with a different query language. So instead of manually fiddling with JOIN statements, traversing linked tables just looks like dot notation:
select BlogPost.author.email; # get all author emails
Apple.objects.get(box__house__owner = 'pjs_')
People endlessly want hacks, workarounds, and un-normalized data structures, for a single reason - they don't want to have to think about JOIN. It's not actually for performance reasons, it's not actually for any reason other than it's easier to imagine a big table with lots of columns.
I'm actually sympathetic to that reticence but what I am not sympathetic about is this: why, in 2024, can't the computer figure out multi-table joins for me?
Unless your schema is really fucked up, there should only be one or two actually sensible ways to join across multiple tables. Like say I have apples in boxes in houses. I have an apple table, a box table, and a house table. apples have a box_id, boxes have a house_id. Now I want to find all the apples in a house. Do two joins, cool. But literally everyone seems to write this by hand, and it means that crud apps end up with thousands of nearly identical queries which are mostly just boringly spelling out the chain of joins that needs to be applied.
When I started using SQLAlchemy, I naively assumed that a trivial functionality of such a sophisticated ORM would be to implement `apple.join(house)`, automatically figuring out that `box` is the necessary intermediate step. SQLAlchemy even has all the additional relationship information to figure out the details of how those joins need to work. But after weeks of reading the documentation I realized that this is not a supported feature.
In the end I wrote a join tool myself, which can automatically find paths between distant tables in the schema, but it seems ludicrous to have to homebrew something like that.
I'm not a trained software engineer but it seems like this must be a very generic problem -- is there a name for the problem? Are there accepted solutions or no-go-theorems on what is possible? I have searched the internet a lot and mostly just find people saying "oh we just type out all combinatorially-many possible queries"... apologies in advance if I am very ignorant here