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

141–150 of 206 posts

Re: SQL language proposal: JOIN FOREIGN

#141

Post by dolmen »

exabrial wrote: post #110
Smotko wrote: post #79

It might be just me, but I feel like remembering the foreign key name is more difficult than remembering the columns that you need in the ON clause. Especially since you can usually find the column names by just seeing the data in the table (select * from x) wheres seeing the foreign key names is much harder (show create table x?). Also, if you use an ORM it will usually generate foreign key names that are almost imp…

I think this is an operator problem. You're using the wrong tool for the job. TablePlus, SequelAce, the official MySQL client all support cntrl-space autocompletion. I wish we used Postgres, but I imagine the landscape is the same. The big box databases like Oracle, DB2 undoubtedly having this tooling as well. That being said, here is our fk naming convention: `fk-asset_types->asset_categories` which pretty states wh…

SQL is not only written in an SQL client. SQL is also written (and read from) embedded/mixed in an other programming language were tooling is not always available.

Having to know the names of foreign keys (in addition to the column names of the 2 tables) is adding more cognitive load. I don't think that is an improvement.

Re: SQL language proposal: JOIN FOREIGN

#142

Post by hdjrudni »

vosper wrote: post #127
tester756 wrote: post #108

While we are at it let's write SQL queries starting from FROM. `FROM users SELECT *` It'd allow tooling to provide IntelliSense better.

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!

Ya..that one is particularly sad because it was recent. Don't know why they felt the need to botch that.

Re: SQL language proposal: JOIN FOREIGN

#143

Post by hdjrudni »

miohtama wrote: post #125
tester756 wrote: post #108

While we are at it let's write SQL queries starting from FROM. `FROM users SELECT *` It'd allow tooling to provide IntelliSense better.

While this might be a joke, the world has been ripe for a replacement for SQL… for the last 20 years.

I started writing a new SQL parser that just transforms the query...this was one of the features. The other being a syntax for group-wise max queries which are just crazy dumb to write efficiently in MySQL.

Re: SQL language proposal: JOIN FOREIGN

#144

Post by dolmen »

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

> SELECT -col1, -col14 FROM table LIMIT 50;

This is already valid SQL. Example: SELECT -col1, -col4 FROM (SELECT 1 AS col1, 2 AS col4) AS tbl;

Do you think seriously that a new meaning could ever be attached to that syntax?

Re: SQL language proposal: JOIN FOREIGN

#145

Post by goscript »

vosper wrote: post #127
tester756 wrote: post #108

While we are at it let's write SQL queries starting from FROM. `FROM users SELECT *` It'd allow tooling to provide IntelliSense better.

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 };

Re: SQL language proposal: JOIN FOREIGN

#146

Post by tapas73 »

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 would argue that conceptually it's not too different from needing to know what tables/columns exist in datamodel.

I get that it is not common now to care about FKs when writing selects. But it could be. Tooling can be improved to help here. (show fks, autocomplete)

Btw. Everybody seems to concentrate on conciseness, but keep in mind that this helps also with query correctness.

Re: SQL language proposal: JOIN FOREIGN

#147

Post by cogman10 »

Tostino wrote: post #121
cogman10 wrote: post #25

Earlier quoted context omitted.

Absolutely agree. Further, whenever someone is adjusting table performance index tweaks is almost always the first thing to tackle. Adding foreign keys into the query is just as bad as adding indexes into the query (which, you can do in T-SQL, but generally shouldn't). Indexes can be dropped, changed, or added and you SHOULD be relying on the SQL optimizer to use the most appropriate index. This feature appears to on…

While I wish there were a way to easily refactor queries when making these changes, this is not different than it works for renaming/dropping a column..."In the worst, an re-name/drop of a column will end up breaking a bunch of queries, which is insane."

I'd argue it's about expectations.

If I change the shape of my data, of course I'd expect a bunch of stuff that potentially needs to be updated. Adding and removing fields is dangerous.

On the flip side, changing an index or a foreign key will almost never change any query, because the data shape is exactly the same. The most it will effect is queries that change data. Even then, when you make that change you have to address, up front, what to do with existing data that might violate a new constraint.

Re: SQL language proposal: JOIN FOREIGN

#148

Post by tapas73 »

clintonb wrote: post #47
gigatexal wrote: post #3

I like this a lot. It makes thing cleaner and more like explicit.

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.

Re: SQL language proposal: JOIN FOREIGN

#149

Post by robocat »

miohtama wrote: post #125
tester756 wrote: post #108

While we are at it let's write SQL queries starting from FROM. `FROM users SELECT *` It'd allow tooling to provide IntelliSense better.

While this might be a joke, the world has been ripe for a replacement for SQL… for the last 20 years.

I could see some standardised SQL language extension, similar to how TypeScript extended JavaScript, that has a transformation phase into the native SQL dialect within the database access library, or via some middleware.

Re: SQL language proposal: JOIN FOREIGN

#150

Post by fendy3002 »

Eremotherium wrote: post #83
bobbyi wrote: post #67

Earlier quoted context omitted.

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.

Iirc in some older db it won't be able to use indexes this way. Don't know if still applied nowadays.
Post reply on HN