Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQL language proposal: JOIN FOREIGN

gist.github.com

Post reply on HN
Print view View original

81–90 of 206 posts

Re: SQL language proposal: JOIN FOREIGN

#81

Post by rcoveson »

benjiweber wrote: post #59
rcoveson wrote: post #53

Earlier quoted context omitted.

FWIW we do have `NATURAL JOIN` already, which is a lot worse than this proposal.

NATURAL JOIN is great because it's like a relational AND. How else will you join with Table Dee and Table Dum? https://twitter.com/benjiweber/status/1476629550608101384 It's just risky if you don't design your schema with relational algebra in mind.

That is a really interesting case. Correct me if I'm wrong, but I don't think this is a special case where you have to use `NATURAL JOIN` over `JOIN ON`. The problem is just that the Postgres SQL grammar (maybe most/all SQL grammars) require ON or USING in a non-NATURAL join, so you have to specify the (empty) equijoin predicate list yourself, i.e. the identity value: `JOIN ON TRUE`.

EDIT: I had another thought about this.

I think people not designing with the relational algebra in mind is the heart of the issue, specifically w.r.t. column names. We know that namespaces are a hard problem, and a consequence of that problem is that `NATURAL JOIN` as specified in the relational algebra seems risky, or overly magick-y. It makes what might be an unfortunate coincidence (name collision) into something algebraically impactful.

A foreign key join gets around the problem by keeping names and namespaces out of it. It's really doing exactly what `NATURAL JOIN` is supposed to do, but only in the subset of cases where name collisions are meaningful, not coincidental.

Re: SQL language proposal: JOIN FOREIGN

#82

Post by xyzzy_plugh »

icambron wrote: post #32
piaste wrote: post #28

Earlier quoted context omitted.

Similarly, I'd love some form of GROUP BY every column except for It feels silly when you are SELECTing a ton of columns, then you add a JOIN to a many-to-one relationship which you want to aggregate. Now you need to either make it a subquery (and hope the optimizer doesn't screw up) or duplicate all your SELECT expression (not even the identifiers) into the GROUP BY.

I wish it just had a "group by all the stuff I selected without aggregation" shortcut

Isn't this just SELECT DISTINCT ...?

Re: SQL language proposal: JOIN FOREIGN

#83

Post by Eremotherium »

bobbyi wrote: post #67
hnthrowaway0315 wrote: post #6

What I really need is something like: SELECT -col1, -col14 FROM table LIMIT 50; Where the minus sign means I don't want these two columns. I still don't see a way to do it easily (for Vertica and in Datagrip).

If you have specific columns that you frequently want to ignore (which, I think, is the common case of this), you could define a view that selects all the columns except those and do your queries against that view.

Or create a function that returns a view of a table minus named columns in situ if that's really a common case for you. Just as an idea.

Re: SQL language proposal: JOIN FOREIGN

#84

Post by wiredfool »

ipaddr wrote: post #73
jdunstan wrote: post #57

Earlier quoted context omitted.

That's very close to SELECT *, which has it's own dangers. I agree that it would be nice for exploration and testing, but probably should not be used in production.

What dangers lay in select *...? Too much data?

Minimal if you refer to columns by name. Risky if you rely on column ordering. Can be too much data and a performance issue if you have large columns that you’re not using. (Though, it’s not much different if you’re using an orm that loads the whole object anyway), like Django or sqlalchemy.

I’ve done it for many years (using named columns/ dictionaries as the result set) and its never been an issue.

Re: SQL language proposal: JOIN FOREIGN

#85

Post by JoelJacobson »

Upitor wrote: post #61

In my opinion this proposal seems only to consider simple cases, but there are many not-so-simple relationsship types: Consider a ‘sales’ table which includes columns [time] and [sold_by_employee_id], and a periodized ‘employee’ table which includes columns [employee_id], [valid_from] and [valid_to] columns. There is a perfectly valid relationsship between the two tables, but you cant join them using only equal-state…

Nice example! The join you describe would remain as a JOIN ON.

This is per design. Quote from the proposal:

"The idea is to improve the SQL language, specifically the join syntax, for the special but common case when joining on foreign key columns." ... "If the common simple joins (when joining on foreign key columns) would be written in a different syntax, the remaining joins would visually stand out and we could focus on making sure we understand them when reading a large SQL query."

So, the special non-equal based join condition you describe, would become more visible, and stand out, allowing readers to pay more attention to it.

The hypothesis is most joins are made on foreign key columns, so if we can improve such cases, a lot can be won.

Re: SQL language proposal: JOIN FOREIGN

#86

Post by zmmmmm »

seadan83 wrote: post #34
btilly wrote: post #17

Please, no. A common problem in data warehousing is that a design with lots of foreign keys becomes slow to load. A standard solution is to move the checks for referential integrity elsewhere, then drop the foreign key constraint. This massively improves load performance. This syntax change means that this solution can't be used because you have no idea what random queries out there might rely on the specific existen…

This, the result of a query should not change depending on a constraint.

By that do you mean, should not vary in the data returned or should not break?

Personally I agree that changing a constraint shouldn't alter the data returned. But I'm happy enough if it breaks in a clear and verifiable manner. There are plenty of other situations where adding a constraint will cause existing SQL (if not queries) to break so its not really that much of a change.

Re: SQL language proposal: JOIN FOREIGN

#87

Post by bendbro »

tqi wrote: post #42

Personally, I think SQLs verbose syntax is a good thing, and increases clarity/interpretability. It reduces cognitive load to have things explicitly listed out. Not having the table and column names in the query itself makes it much harder to read and understand queries without prior knowledge of the data model.

I agree with your point about explicit columns being easier to read, but I still sometimes prefer implicit columns. An example: whenever you have numerous subqueries, all using the same columns, implicit columns are easier to read since there is less text on the screen. It's also less error prone to change just one line rather than numerous lines.

This feeds into my view of metaprogramming-like situations. Whenever the code-time-view of a program differs significantly from some runtime-state-view of the program, I think there should be a code-time way to view and perhaps edit both the code-time-view and some kind of runtime-state-view. A programmer shouldn't have to waste time digging through numerous files to evaluate what implementation slots into some dependency injected class, or find out what structure ends up in a python method parameter, or what a preprocessor directive ultimately produces. I know IDEs can handle some of these things, but I think better tools can be produced.

More concisely, instead of approaching code as the single and unchanging view of the program, perhaps it would help to approach code as something more dynamic. I have no concrete ideas as to how this would work.

Re: SQL language proposal: JOIN FOREIGN

#88

Post by JoelJacobson »

Upitor wrote: post #63
Upitor wrote: post #61

In my opinion this proposal seems only to consider simple cases, but there are many not-so-simple relationsship types: Consider a ‘sales’ table which includes columns [time] and [sold_by_employee_id], and a periodized ‘employee’ table which includes columns [employee_id], [valid_from] and [valid_to] columns. There is a perfectly valid relationsship between the two tables, but you cant join them using only equal-state…

Also, consider a ‘sales’ table with multiple references to a ‘calendar’ table: [shipped_date], [order_date], [received_date]

Good example too, but this one can with benefit be written using the JOIN FOREIGN syntax, you just need to give the foreign keys suitable names such as e.g. "shipped_date", "order_date", "received_date". Or, to remind you of which is the referenced table, perhaps you want to include it in the names, and the names would be "shipped_date_calendar", "order_date_calendar", "received_date_calendar", but personally I would prefer just "shipped_date" or perhaps even just "shipped".

Re: SQL language proposal: JOIN FOREIGN

#89

Post by zmmmmm »

JoelJacobson wrote: post #41
Ayesh wrote: post #11

It looked very useful and intuitive at first, but on further thought, I think the only time that you'd truly benefit from it if you SELECT *. For other SELECT queries with explicit field name list, you'd need to know the table the key constraint links to anyway. SQL Views serve perfectly well for queries encouraged by the schema itself, and i think they are more sophisticated and practical way.

There is similar comment in the thread, suggesting both tables should always be specified. Not sure what I prefer yet. The idea is the foreign key name is usually the same as the referenced table, so should be an infrequent problem.

> The idea is the foreign key name is usually the same as the referenced table

I think this is where you are hitting some of your turbulence here because lots of ORMs / schema management tools actually generate completely cryptic fk names (sometimes based on the hash of the columns or similar). Personally I think weighing in legacy baggage like that too highly is a bad thing as it creates enormous inertia.

Re: SQL language proposal: JOIN FOREIGN

#90

Post by mpolichette »

tqi wrote: post #42

Personally, I think SQLs verbose syntax is a good thing, and increases clarity/interpretability. It reduces cognitive load to have things explicitly listed out. Not having the table and column names in the query itself makes it much harder to read and understand queries without prior knowledge of the data model.

I'm with you on this one. I prefer it to be in your face simple to understand over the terse-ness.

In the gist example, I actually prefer the SQL-92 approach where we are joining given an explicit comparison condition. Every other implementation seems to be trying to hide details, for what gain? Less typing?

In order to use FOREIGN, you will need to know not just what columns a table has, but also their configuration. Which would also require that you have properly configured your tables. While this shouldn't be a hard ask, it does add additional dependency and makes use of this "tool" slightly less "portable" between systems.

I have unfortunately seen cases where people will only have foreign keys un-enforced by their table config. As a dev, if you're introduced to a new DB, you wont know immediately if you can use this, and if things are configured wrong, you need to make a pretty significant change to be able to use it.

I don't see a lot of harm from adding this syntax however as people are free to not use it and it relies on an existing strict convention.

Post reply on HN