TBH, I don't think your test is very useful in real world environments. That is, you have 2 independent tables, and you're wanting the solution to depend on the fact that there are columns that are named the same across both tables.

IMO these kinds of "shortcuts based on column naming across tables" usually end in disaster down the road. For example, I've been bitten in the past by "natural joins" when we've wanted to refactor something later.

I definitely agree that I don't want to have to repeat logic within a single table, but the kind of syntactic sugar that is your litmus test is a big foot gun IMO.

Oh, I 100% agree with you.

> IMO these kinds of "shortcuts based on column naming across tables" usually end in disaster down the road.

I can see that point, and that was not what I wanted to express with my litmus test. It's only supposed to be a litmus test after all. In a proper solution there would be additional things I would be looking for, but so far everything I've seen already fails that "trivial" test.

One could easily re-formulate it, so that in the one tabel the column is named ".firstname", and in the other one it is named ".first_part_of_the_name".

The core point is more that no matter the relational logic you layer on top of a table/view, that logic should be paramterizable by table/view/column names, to be properly relocatable. I'd be happy about suggestions for better examples! Some solutions (I think dbt) do have some relocateability across schemas, but usually in a more singleton-like manner rather than being able to instantiate the logic multiple times.

I can just tell you that I interact with queries that would benefit from such kind of reuse on a daily basis. One common thing would also be mechanisms that you want to reuse across many different tables in your schema. E.g. soft-deletes or historic/snapshot tables. Nowadays those kinds of solutions usually end up being expressed in the ORM/query builder of a programming language (and thus highly fragmented across programming language ecosystems), instead of living on an SQL-like level and being able to mature better.

> IMO these kinds of "shortcuts based on column naming across tables" usually end in disaster down the road.

But sometimes this decision has been made years ago and it's not realistic to change it now. I've wanted to do this many times, and I've never been the person who created said tables.

Also, certain use cases perform much better if you create temp tables with small subsets of data from the main tables. It sure would be nice to be able to reuse fragments of SQL written against the main tables... if only SQL were better.

As far as I can tell, the parent is describing something like a trait or interface: both tables have `.firstname: text` and `.lastname: text`. This may not really be relational -- I guess there should be a third table and they should both reference it -- but it does show up in real world schemas.