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

91–100 of 206 posts

Re: SQL language proposal: JOIN FOREIGN

#91

Post by Upitor »

JoelJacobson wrote: post #85
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 fo…

I see. I should have read the article more carefully :-)

Re: SQL language proposal: JOIN FOREIGN

#93

Post by matteote »

This reminds me of KEY JOIN in Sybase which, unlike this syntax, does not require to specify the foreign key. I find Sybase's syntax to be too implicit, and challenging to port to other dialects; JOIN FOREIGN with explicit PK reference looks much better.

I wonder how it is expected to work with non-table references (views, CTEs, subqueries), especially when the columns involved in the foreign key (on either side) are not returned explicitly by the referenced object.

Re: SQL language proposal: JOIN FOREIGN

#94

Post by orthoxerox »

xyzzy_plugh wrote: post #82
icambron wrote: post #32

Earlier quoted context omitted.

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

Isn't this just SELECT DISTINCT ...?

No. What icambron and I would like is something that can get rid of this group by:

    select division_name, branch_name, dealer_id, dealer_name, quarter, month, sum(total_paid)
    from divsions, branches, dealers, transactions
    where ..... --buncha joins
    group by division_name, branch_name, dealer_id, dealer_name, quarter, month
If I didn't want to group the result by division_name, branch_name, dealer_id, dealer_name, quarter and month, why would I put them in the select clause?

Re: SQL language proposal: JOIN FOREIGN

#95

Post by zoomablemind »

I wonder if primary objective of this proposal is to increase convenience and reduce the amount of typing that users need to do, when using SQL inline?

If indeed that's the goal, then it targets a rather specific subset of users dealing with explorative/ad-hoc analysis on a database. Once such analysis is done, the queries would usually need to be formalized for robustness and to avoid ambiguities.

Obviously, the whole train of queries would derail, should the FK (which is just an index) be dropped for one reason or the other.

The existing JOIN features are explicit at least on the level of specified table structure. I believe, any constraint details in such context will be, well, ...foreign.

Perhaps, a simple solution to verbosity problem may be to use an "intelligent" SQL client, which supports some form of autocomplete and which may as well internally use as many schema/data details as available.

In anycase, thanks for making the proposal. I was not aware of JOIN ... USING syntax. I often wanted some convenient way of specifying homonymous join columns, as some schemes are consistent in such namings. So typing JOIN on col1, col2... would translate into equality joins between the listed tables. However, again, there is ambiguity here...

Re: SQL language proposal: JOIN FOREIGN

#96

Post by zmmmmm »

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…

Data warehouses have all sorts of different design approaches based on their different requirements, wouldn't this just be another one of them? I actually think that having a way to alter the constraint to non-enforcing sounds like it'd be better for your situation anyway because you can then have your load performance but utilise the constraints with background / delayed process that checks the integrity because the information is still there in a standard form.

Which sort of leads to .... I don't agree with your characterisation of foreign key constraints as business rules. They are genuine information about the structure of the data.

Re: SQL language proposal: JOIN FOREIGN

#97

Post by polygotdomain »

blondin wrote: post #75
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).

i would rather it written as SELECT * BUT or SELECT * EXCEPT or even SELECT ALL BUT. SQL has always been that language that is easy to read. even when you don't understand what the queries are doing. adding a cryptic syntax like "-column" would make it less readable.

What about SELECT * WITHOUT {columns_you_do_not_want} FROM...

EXCEPT is already a keyword and has is used for set-based operations, so I don't think it's good to overload it.

Re: SQL language proposal: JOIN FOREIGN

#98

Post by hnthrowaway0315 »

jdunstan wrote: post #57
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).

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.

The problem I found out is that SELECT * LIMIT 10 is guaranteed to be needed so it's actually much more important than whatever is in production (you have to explore a lot before writing the production scripts). My idea is that exploration should be as easy as possible.

Things such as SELECT * EXCEPT col1, col2 are really a PIA to write and can build up frustration level really quickly. Certain IDEs such as Datagrip ease the process by providing "macros" but they are not enough.

Another thing is to generate useful boilerplates such as SELECT col1 FROM table GROUP BY col1 ORDER BY col1 to explore all unique values of col1.

Re: SQL language proposal: JOIN FOREIGN

#99

Post by JoelJacobson »

tqi wrote: post #44
JoelJacobson wrote: post #31

Earlier quoted context omitted.

> Even USING and NATURAL are just syntactic sugar that depends on the structure of the table, not on any underlying constraints. Similar to how JOIN FOREIGN would depend on the structure of the data model, defined by tables, foreign keys, etc. > So, what you have proposed, "allowing a user to drop the referential integrity check if desired, but keeping the relationship definition" would be a massive change to tons of…

The person you are responding to was more direct/harsh than was necessary, but I think it would be good to step back for a moment and reflect on the feedback from this community. You said "If someone can convince me this is a bad idea, that would help me forget about all of this, so I would greatly appreciate your thoughts, no matter how negative or positive." I think there are enough valid objections here to at leas…

When I wrote that comment, the idea had even more flaws than currently, at that time I suggested using "WITH" and a new "->" operator. Thanks to new ideas coming from other users in the PostgreSQL and Hacker News community, those problems have been solved, and we now have less remaining problems with the proposal. I'm really grateful for all the help.

Like I said in another reply, I will put together a "Drawbacks / Remaining issues" section and update the Gist, based on all replies. Perhaps the end result will be Status Quo, but at least then we have documented the reasons why this idea is a dead end. However, thanks to all the improvements just during the last couple of days, I feel really optimistic and motivated, so I think there is a great chance we can solve the remaining issues together if we try.

To comment on the response from the direct/hash person:

The point made by the user, "not currently any SQL statements that depend on structural information as defined in the foreign key relationships", is true, but I don't see why that's an argument by itself against the idea?

I find the other argument, claiming there would be a problem with tooling and processes, much more interesting and I'm eager to fully understand it. I asked a question in hope to do so, "what do you suggest would be the problem from the tools perspective", but has so far not received any reply.

Post reply on HN