A huge fraction (not 100%, but maybe 80%) of my frustration in trying to get technical people to …

It's not really a problem of there being combinatorially many ways to join table A to table B, but rather that unless the join is fully-specified those ways will mostly produce different results. Your tool would need to sniff out these ambiguous cases and either fail or prompt the user to specify what they mean. In either case the user isn't saved from understanding joins.

Most databases have the concept of foreign keys. You declare how the tables relate to each other. You can then write a script that queries this metadata to write the join for you. I did this sort of thing over twenty years ago.

> 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 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 experien…

> 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. 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.

> Unless your schema is really fucked up 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.

That join you can have your ORM solve for you with some annotations or whatever implying eager loading and so on. 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.

You should learn about some basic database internals. In particular the data structure for indexes, the difference between primary and secondary indexes, join algorithms, page size being 8KiB, etc. 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.

> 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. 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.…