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

171–180 of 206 posts

Re: SQL language proposal: JOIN FOREIGN

#171

Post by cryptonector »

dstola wrote: post #168

Not a fan since proposal would do an implicit join on foreign keys rather than explicit join on the columns. If you know the schemas its great, but would add an extra step to check FKs on the tables otherwise In general explicit > implicit, IMO

This is why we don't use `*` in production SQL. It's also why we try to avoid NATURAL JOIN. USING is OK if you only have two table sources, but you don't always get to have same-name columns on both tables.

This syntax seems pretty clear and explicit, but with an indirection. Indirection != implicit. The intent is quite explicit.

Using ON also is problematic in that you might think while reading a query that the JOIN is on FOREIGN KEY columns, but... maybe not -- without looking at the schema, you can't tell. JOIN FOREIGN has a similar problem: the intent is crystal clear, but now you have to go look at the schema if you want to know which columns that refers to. Normally one would write a comment on the ON, but comments can rot.

Now, making SQL more expressive isn't necessarily a good thing. SQL is already very expressive. But making it more expressive in ways that yield clearer queries is definitely worth considering.

One very nice aspect of JOIN FOREIGN is that because RDBMSes generally require corresponding indices on those columns to optimize ON UPDATE / ON DELETE constraint processing, seeing "JOIN FOREIGN" in a query instantly lets you know that there must be an appropriate index, while ON might be causing a full table scan or query materialization and you'd have to examine the query plan carefully.

Re: SQL language proposal: JOIN FOREIGN

#173

Post by cryptonector »

tapas73 wrote: post #148
clintonb wrote: post #47

Earlier quoted context omitted.

How is this more explicit than specifying join columns today?

It enforces foreign keys during select. One can say that it makes you explicitly state, which foreign key do you use. "on a.foo = b.bar" can be a incomplete and/or incorrect join condition.

It doesn't enforce foreign keys. It uses foreign key definitions to simplify the query. It clarifies intent, and because FOREIGN KEYs generally are required to have appropriate indices, it makes it clear that there must be at least one suitable index for the JOIN.

Re: SQL language proposal: JOIN FOREIGN

#174

Post by cryptonector »

unbanned wrote: post #48

Foreign keys exist for data integrity... something which is rather opaque to query onterface... So why do you believe that has place in a query?

Why do you think it's opaque? They declare relations between tables. That's structure, intent. Making intent clearer is generally a good thing.

Re: SQL language proposal: JOIN FOREIGN

#175

Post by cryptonector »

mi_lk wrote: post #56

tangent question - what're some effective ways to learn how to write SQL?

O'Reilly has an excellent pocket book on generic SQL titled "SQL Pocket Guide: A Guide to SQL Usage" (it has several editions). That book worked really well for me. It is surprisingly thorough for a pocket book. I highly recommend it.

Besides that, I highly recommend https://sqlite.org/ as a reference. It's syntax pages for SQL are fantastic. But to understand the language you need a better resource, and for my money that's the book mentioned above.

Re: SQL language proposal: JOIN FOREIGN

#176

Post by jpz »

slotrans wrote: post #153

This is not actually compatible with SQL semantics. An important constraint on SQL is that a query must run, and produce correct results, relying only on the structure and content of tables . Indexes (can) make queries faster but must not inhibit, or be required for, correctness. The same is true of primary key constraints, foreign key constraints, check constraints, defaults, triggers, partitioning, whether a table…

I agree with all the points you make here, we’ll said.

Re: SQL language proposal: JOIN FOREIGN

#177

Post by JoelJacobson »

cryptonector wrote: post #170
JoelJacobson wrote: post #159

Earlier quoted context omitted.

> This proposal would break that constraint, by elevating the _name_ of a foreign key constraint to an identifier usable in a DQL statement (as distinct from DDL statements where such names can already appear). That's not allowed. Actually, constraint names already do appear in some DQL statements, such as the quite recently added INSERT INTO ... ON CONFLICT in PostgreSQL [1] INSERT INTO ... ON CONFLICT [ conflict_ta…

That's DML, not DQL. Still, I disagree with GP's arguments (see separate comment).

> That's DML, not DQL.

Thanks. I incorrectly assumed there were only two sublanguages. But now when you say it, I've heard both DML and DDL before, but never DQL. Curious if there were even more categories, I found this Wikipedia article:

https://en.wikipedia.org/wiki/Data_query_language

Interesting to read, what a SELECT statement is, depends on if having FROM or WHERE "data manipulators". Quote from Wikipedia:

"Although often considered part of DML, the SQL SELECT statement is strictly speaking an example of DQL. When adding FROM or WHERE data manipulators to the SELECT statement the statement is then considered part of the DML."

Re: SQL language proposal: JOIN FOREIGN

#178

Post by antender »

nrabulinski wrote: post #162
antender wrote: post #120

I, personally, feel like this is a rather pointless addition to an already somewhat bloated language. It it isn't saving that much typing (we have NATURAL JOIN's already, nobody is using them), is kind of inconsistent with other language principles (we should be able to join anything to anything if we need to, see SQL-89) and limits JOIN flexibility (you don't have to join with "=" operator, JOIN's supports different…

Nobody is using NATURAL because usually it’s broken and doesn’t do what you want it to and for any more complex joins or joins on other than = you would still use JOIN ON, what’s the issue? JOIN was introduced when we had multiple tables in the FROM clause and conditions in WHERE for clarity and convenience, why are people so opposed to this change then?

The issue, as i see it, is that adding this feature complicates the language parser even more for not that much actual gains.

1) Implementation has to do some nontrivual rewriting into actual JOIN's which uses indexes, previous special cases like NATURAL were purely syntax sugar 2) All of the tooling that depends on parsing SQL need to add sensible support or else noone will even recommend to use this 3) It is useless for anyone using ORMs in the first place, they will continue to generate normal JOIN's (less actual impact) 4) For anyone using prepared statements it automatically goes to "is not recommended to use" list because it makes your JOIN's depend on existence of foreign keys and corresponding indexes, which are an optional feature DB still should work without. I've had tasks in my career when my team added or removed foreign keys, so this type of JOIN's would make migrations even harder to do. 5) So considering all of the above this is feature designed purely for REPL and for this purpose it is also kind of useless. I can imagine remembering and typing column names in normal JOIN's, but foreign keys usually have some long unintelligeble autogenerated name.

Re: SQL language proposal: JOIN FOREIGN

#179

Post by antender »

nrabulinski wrote: post #162
antender wrote: post #120

I, personally, feel like this is a rather pointless addition to an already somewhat bloated language. It it isn't saving that much typing (we have NATURAL JOIN's already, nobody is using them), is kind of inconsistent with other language principles (we should be able to join anything to anything if we need to, see SQL-89) and limits JOIN flexibility (you don't have to join with "=" operator, JOIN's supports different…

Nobody is using NATURAL because usually it’s broken and doesn’t do what you want it to and for any more complex joins or joins on other than = you would still use JOIN ON, what’s the issue? JOIN was introduced when we had multiple tables in the FROM clause and conditions in WHERE for clarity and convenience, why are people so opposed to this change then?

Basically I think this syntax addition is nonorthogonal, isn't making SQL any more powerful, but isn't solving the main usability problem, which is composability of queries in any way. Like writing JOIN's is not complicated if somewhat wordy already.

Re: SQL language proposal: JOIN FOREIGN

#180

Post by Too »

goscript wrote: post #145
vosper wrote: post #127

Earlier quoted context omitted.

I agree, though I'll give SQL a pass for this because it's old. But how the Javascript world ever thought that `import { function } from 'library'` was better than `from 'library' import { function }` I'll never know. Python got this right long before anyone was even thinking about adding imports to JS!

I agree but not with current JavaScript modules. i would rather work with this: import { functionA } from 'library'; import { functionB } from '../utils/core/abc'; import { functionC } from './a'; over: from 'library' import { functionA }; from '../utils/core/abc' import { functionB }; from './a' import { functionC };

Care to rationalize? Your first example only looks pretty because the function names happens to be aligned while the library names are not. Import a few more functions with different length name and the story will be different. In reality library names tend to align better since they usually start with same prefix spanning across several sub-components.
Post reply on HN