16 ms·

SQL language proposal: JOIN FOREIGN

187 points by JoelJacobson 5y ago

unbanned 5y ago
>This improved in SQL-92: Did it?
- JoelJacobson 5y ago
  Yes, I think so, at least according to: https://learnsql.com/blog/history-of-sql-standards/ https://learnsql.com/blog/history-of-sql-standards/ What makes you think otherwise?
unbanned 5y ago
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?
- JoelJacobson 5y ago
  Foreign keys both define relationships between tables, and also enforce referential integrity. The discussion is about how we could potentially mine various additional value from foreign keys as an information resource, rather than just mostly being about referential integrity.
- cryptonector 5y ago
  Why do you think it's opaque? They declare relations between tables. That's structure, intent. Making intent clearer is generally a good thing.
JoelJacobson 5y ago
This is the same idea as already posted in this thread https://news.ycombinator.com/item?id=29687134 https://news.ycombinator.com/item?id=29687134, but with a more in-depth explanation that couldn't fit in the comment field, and with some syntax improvements, such as using "FOREIGN", which is a reserved keyword, instead of the previously suggested "->" notation. Thanks for all the valuable comments on last proposal. Excited to hear what you think about this update.
gigatexal 5y ago
I like this a lot. It makes thing cleaner and more like explicit.
- clintonb 5y ago
  How is this more explicit than specifying join columns today?
  - tapas73 5y ago
    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.
    - gigatexal 5y ago
      Exactly this.
    - cryptonector 5y ago
      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.
  - cryptonector 5y ago
    The intent is more explicit. Indirection != implicit.
torgard 5y ago
I dig it! I prefer defining tables like this: CREATE TABLE category ( id int GENERATED ALWAYS AS IDENTITY, name text ); CREATE TABLE post ( id int GENERATED ALWAYS AS IDENTITY, category_id int REFERENCES category (id) ON DELETE CASCADE ); That is, category.id rather than category.category_id. But the USING clause doesn't work with that style, as far as I understand. This would make my queries nicer.
- thom 5y ago
  I've been quite happy having fully prefixed column names for a long time now. Makes joins easier, big views clearer, and random exports more readable out of the box. Also in my case it's also easier to line up unique column names with things like Clojure specs but I accept that's a niche concern.
- JoelJacobson 5y ago
  Much cleaner, I agree, that's a big win. Will add that to the list of benefits. Will also add a "Drawbacks / Remaining issues" section to the Gist, from all the valuable comments so far in this thread, thank you all, positive as well as negative comments, all very helpful.
hn_throwaway_99 5y ago
This is my "Thanks, I hate it" response. Reason being if you use the example they gave: SELECT f.title, f.did, d.name, f.date_prod, f.kind FROM films f JOIN FOREIGN f.films_did_fkey d You need to implicitly know the table that films_did_fkey points to, because 'd' is just a table alias. I can't think of anywhere else in the SQL standard where you can introduce a table alias without explicitly referencing the table. In my opinion making code essentially unreadable unless you have other background information is an antipattern.
- JoelJacobson 5y ago
  I think your concern is addressed by the idea further down in proposal; giving the foreign keys the same names as the referenced tables. This example was provided: SELECT f.title, f.did, d.name, f.date_prod, f.kind FROM films f JOIN FOREIGN f.distributors d
  - hn_throwaway_99 5y ago
    No, my concern is not that you can also specify the table name that explicitly states the name of both tables, it's that you have proposed a syntax where it is possible to leave off the explicit table name. If you got rid of the syntax that uses the underlying "magic" of needing to know which table the foreign key points to, I'd be more amenable.
    - JoelJacobson 5y ago
      There are two asymmetrical cases; one of them needs two pieces of information, the other three. Having to always specify both tables would syntax wise be redundant, but I agree it’s worth considering it might be a good thing to improve readability, which would help especially in the case where the foreign key isn’t/cannot be given the same name as the referenced table.
    - mrweasel 5y ago
      Yeah, my concern is people in the real world. If you can "magically" join on a foreign key and leave the table name out, or still call the thing anything you want, some smart ass will abuse the live crap out of it and I'll somehow be left to deal with it. I like things to be explicit. Tell me what you're joining and how you want to join it. What is the use case for this? Other than saving some typing, and let's face it, sometimes a little extra typing now, will save you a lot of trouble later. The proposal claims: "The idea is to improve the SQL language", but is it really better?
hnthrowaway0315 5y ago
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).
- dolmen 5y ago
  > 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?
  - hnthrowaway0315 5y ago
    I just say something similar, doesn't have to be the exact form.
- stingraycharles 5y ago
  Simple, effective, and really useful in a lot of situations. I like it!
- nvartolomei 5y ago
  Some have support for this. - BQ https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax https://cloud.google.com/bigquery/docs/reference/standard-sq... - CH https://clickhouse.com/docs/en/sql-reference/statements/select/#except-modifier https://clickhouse.com/docs/en/sql-reference/statements/sele...
  - hnthrowaway0315 5y ago
    Thanks, these really look neat. Unfortunately we only use Vertica :/
- piaste 5y ago
  Similarly, I'd love some form of GROUP BY every column except for <these> 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.
pella 5y ago
related: pgsql-hackers thread and context https://www.postgresql.org/message-id/flat/1aec0dd0-dc27-40e6-89ee-2d487fb990fb%40www.fastmail.com#ae69d491b10eff8f5684e07ff6e3eb0b https://www.postgresql.org/message-id/flat/1aec0dd0-dc27-40e...
Ayesh 5y ago
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.
- JoelJacobson 5y ago
  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.
  - zmmmmm 5y ago
    > 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.
    - JoelJacobson 5y ago
      Thanks for explaining, I have no experience of ORMs, always written my queries manually. This was insightful. Thanks also for the word “inertia”, good one, will add to my vocabulary.
thom 5y ago
Of all the myriad indignities of SQL, this isn't near the top of my list. I also don't like making the names of objects like foreign keys and indexes first class concerns in your queries, that's a whole new layer of cognitive overhead.
- degenerate 5y ago
  Agree. I'd much prefer they worked on the way MySQL determines what indexes to use on a multi-table join, so there could be less emphasis on the need to ANALYZE TABLE and FORCE INDEX when the DB is seemingly being dumb, but there's not enough information in EXPLAIN to tell you where it's actually getting tripped up. Troubleshooting full table scans on large sets of data is a nightmare.
  - Tostino 5y ago
    Slightly different group of people working on MySQL vs this proposal.
  - dspillett 5y ago
    > but there's not enough information in EXPLAIN to tell you where it's actually getting tripped up Unfortunately I don't think providing that information is generally possible. In some specific cases there are useful details it could provide, but there would usually be a myriad of other similar details that are irrelevant and if it included all those you'd not see the wood for the trees. EXPLAIN and query plan outputs in other DBs are “this is what I did” not really “why I did what I did”. To make the query planner bright enough to know what details would be useful to you, would probably pretty much require making it bright enough to do the optimisation job without you¹. [1] picking better indexes without hints, even creating those that are often needed, etc. MS's SQL Server tries to do this a bit with index suggestions. These are sometimes handy, but often at best for guidance². I've seen people blindly follow these suggestions to get a %-or-few gain from a small set of queries that could see orders of magnitude improvement with just a little tweaking elsewhere³, slowly amassing collections of indexes for very specific cases, sometimes multiple on the same key columns but each INCLUDEing a different mix of other data, that balloon their storage requirements⁴. [2] a nudge in the direction of “Mr Dev/DBA, you might want to think about how I'd avoid scanning this large object or performing many many thousands of seeks on this other one” [3] refactoring non-sargable predicates, index changes on other tables being referred to, getting rid of “SELECT *” particularly when referring to hideous views, ... [4] and having the knock-on effects of slowing insert/update activity & important admin functions (particularly backups)
btilly 5y ago
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 existence of a foreign key constraint for the definition of the query. Thereby meaning that if a foreign key constraint becomes a performance problem, we're stuck with it rather than having a solution. Features have consequences. And I don't like the consequences of making business rules that are now explicit in the query, be instead implicit in the table design.
- JoelJacobson 5y ago
  Good point, but addressable: Simply decouple the relationship definition and referential integrity check, allowing a user to drop the referential integrity check if desired, but keeping the relationship definition. I cannot see why you would not want to at least always store the information a certain table/column(s) references some other table/column(s) in the data model. Enforcing referential integrity is probably good in general too, but I agree you might need to disable it for some FKs, in some databases, like PostgreSQL before they got FOR KEY SHARE locks.
  - hn_throwaway_99 5y ago
    At this point you should realize your proposal is a non-starter, and I didn't even realize btilly's objection originally. I can't think of any other feature in SQL where the rules of the query are actually dependent on something not explicit to the query itself. Even USING and NATURAL are just syntactic sugar that depends on the structure of the table, not on any underlying constraints. 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 SQL tools out there as it's a huge new feature, for some minor syntactic sugar. Ain't gonna happen.
    - JoelJacobson 5y ago
jimbobmcgee 5y ago
Is anyone actually likely to implement these based on some random gist on the internet, or are you just screaming into the void? Because, if you have that kind of clout, I've got an INSERT/SET syntax I'd like to put your way...
- JoelJacobson 5y ago
  I'm a small PostgreSQL contributor since 2010 myself. I probably can't write the whole patch myself, but if there is enough interest, and if we can work out the details and address the problems raised in this thread and elsewhere, I'm pretty confident we can do it. If we ever get there, the next step would be a reference implementation, probably in PostgreSQL, or to discuss a proposal in the SQL committee.
  - jimbobmcgee 5y ago
    I'm often interested into what goes into changes to committee-driven standards. To an outsider, proposing a change seems to require one to be part of a shady cabal of Big-5 employees, skilled in the art of hiding subtle, privacy-invading features into inscrutable, plain-text RFCs. That or subjecting yourself to 30K+ what-abouters who deform your suggestion into something unrecognisable. It's refreshing to see a straightforward, well-formatted proposal (even if I do slightly prefer the `FROM table1 x JOIN table2 y ON x.fk` syntax suggested in other comments).
    - JoelJacobson 5y ago
      > To an outsider, proposing a change seems to require one to be part of a shady cabal of Big-5 employees, skilled in the art of hiding subtle, privacy-invading features into inscrutable, plain-text RFCs. I thought so too. Initially I just tried to get in contact with someone at the Swedish Institute for Standards (SIS), to see if it would be possible to send a proposal to someone in the SQL committee, which I thought was nearly impossible to become a member of. But as it turns out, SIS explained I could actually join the Swedish working group, and participate directly there, I just had to send in an application and get the approval from my employer, since there is a cost involved and you have to be a member via a company. Turns out ISO is a very open and democratic organization, just like Hacker News! :) I think this proposal could take years until it land, if it ever does, in some form, if concerns can be addressed, but SQL is here to stay for a while, so that doesn't scare me.
tqi 5y ago
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.
- rcoveson 5y ago
  FWIW we do have `NATURAL JOIN` already, which is a lot worse than this proposal.
  - benjiweber 5y ago
    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 https://twitter.com/benjiweber/status/1476629550608101384 It's just risky if you don't design your schema with relational algebra in mind.
    - cpill 5y ago
      I'm 20 years I have never seen anyone use relational algebra. schemas have to be self documenting albeit only in the real world.
      - salawat 5y ago
        ...I do... All the time. I'm cleaning up a mess of a namespace collision as we speak. I was the only one who saw it coming. I've spent the better part of a month getting people to believe it's an issue.
      - gavinray 5y ago
        SQL is relational algebra, the core of relational algebra is Selection, Projection, Cross-products, Unions, and Set Differences. SQL is a language that implements Relational Algebra/Relational Calculus
    - 5y ago
nicoburns 5y ago
I wish the SQL standard would add some basic syntax sugar, such as support for trailing commas and allowing use of aliases in more places.
mi_lk 5y ago
tangent question - what're some effective ways to learn how to write SQL?
- jdunstan 5y ago
  w3schools and the online documentation for any database are good places to start (Postgres and SQL Server have pretty good documentation). There are also easy-to-google SQL Puzzles, if you're looking for something more advanced. I would also recommend learning about query plans and how to read them, they're invaluable for query optimization.
- orthoxerox 5y ago
  Basic SQL has lots of tutorials. Graduate from them to Markus Winand's use-the-index-luke.com and modern-sql.com
- cryptonector 5y ago
  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/ 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.
deleted 5y ago
[deleted]
Upitor 5y ago
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-statements (you need a between-statement as well)
- Upitor 5y ago
  Also, consider a ‘sales’ table with multiple references to a ‘calendar’ table: [shipped_date], [order_date], [received_date]
  - JoelJacobson 5y ago
    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".
  - CRConrad 5y ago
    Are those timestamps, or actual dates, i.e. meaning a whole day encompassing (usually) 24 hours? In many (most?) data warehousing projects I've seen, you make a "fake date" integer the primary key column of your Dates dimension. This integer consists of 10000 × YEAR_PART + 100 × MONTH_PART + 1 × DAY_PART of the date in question, so yesterday's New Year's Eve woul get 10000 × 2021 + 100 × 12 + 1 × 31 = 20211231. The date dimension itself has many more columns (often booleans, IS_WEEKEND, IS_HOLIDAY, etc; also the date parts themselves in both numeric and character form (12, 'December'), day of week (5 [or 6, depending on convention], 'Friday'), etc) that are used for BI and reporting. But since this generated ISO-8601-date-as-integer column is the primary key of the Dates table, it is also the value of the Date foreign key in all tables that reference Dates. That makes it incredibly handy in queries -- both during development and for ad-hoc reports -- of those tables without joining to the Dates dimension at all: grouping, sorting, limiting to a more manageable date interval in the WHERE clause... And it tells the reader exactly what the actual date in question is. (Well, at least readers who are used to ISO-8601-format dates.) As jerryp would have said, recommended.
jeff-davis 5y ago
I'm not sure this is a good idea. It means that a constraint on the data (a FK is a constraint) affects the semantics of the query. In SQL, those two concepts are seperate.
Guest42 5y ago
Is this not something that should be written at the app level rather than dml?
- Tostino 5y ago
  Foreign keys in general? I am not sure I understand what you mean...
  - Guest42 5y ago
    Not something I’ve ever thought about but I think that it could be done via macro, I have written tsql functions in c# and imagine that other dialects have analogous functionality. It seems as though the point is to cut down on sql code, it’d also be possible to query the foreign keys and create a data structure that could feed a function for joins. I haven’t thought out the specifics but think this type of approach would be more practical than changing the sql standard.
    - Tostino 5y ago
      At that point you are just building a feature into an "ORM" of some sort. The entire point is to allow queries to be written in a more concise way for this simple equality join use case which is (total ballpark figure) ~half of the joins in my system.
thanatos519 5y ago
I dunno. I preferred the SQL-89 syntax.
- samtheprogram 5y ago
  Same here. I didn’t even realize that was an option. As a relative beginner to SQL, is there a real reason the JOIN syntax was needed at all beyond special inner/outer joins?
  - Tostino 5y ago
    Sanity for your coworkers who may have to touch your SQL.
Smotko 5y ago
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 impossible to remember.
- JoelJacobson 5y ago
  It would indeed be difficult to remember, but the proposal also suggest changing the default naming convention for foreign keys, to give them the same name as the referenced table. If using an ORM, I would guess this proposal isn't useful, since then you wouldn't hand-write queries anyway, right? Except when you want to override the queries generated by the ORM? (I'm not an ORM user myself.)
  - to11mtm 5y ago
    Speaking as someone who has used ORMs in the past and contributes to a LINQ Micro ORM... It might make tooling 'easier', but since backwards compatibility has to be considered the actual value add is questionable IMO. Most ORMs/MicroORMs will have tooling that sniffs out the DB Schema including foreign keys, and if you are using those bits (i.e. 'not hand written') most will do the right thing today. I suppose you could include some extra syntax for whatever DSL you're providing users.... IDK. Speaking as someone who is very comfortable in SQL, This feels more like syntactic sugar than anything else.
    - lolive 5y ago
      Could you please elaborate on this FK sniffing, and which tools are actually implementing that?
      - to11mtm 5y ago
        Ahhh, I know that Entity Framework would do this at one point, IDK if EF Core does or not. Linq2Db does it via T4 Template generation, so you can play with it more if you want [0] [0] - https://github.com/linq2db/linq2db/blob/64a0db9a9ed7787ff7551efeee516e955a055b24/Source/LinqToDB.Templates/LinqToDB.ttinclude#L430 https://github.com/linq2db/linq2db/blob/64a0db9a9ed7787ff755...
abhchand 5y ago
This feels like adding syntactic sugar in some sense. That is, a layer of convenience to accomplish something that can already be accomplished today. It also breaks the following: * It's better to be explicit than infer * Keep specifications simple
matteote 5y ago
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.
- JoelJacobson 5y ago
  > I wonder how it is expected to work with non-table references (views, CTEs, subqueries) It’s not, it’s only for the special but common case of joining two tables based on a foreign key.
zoomablemind 5y ago
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...
- branko_d 5y ago
  > FK (which is just an index) Correction: foreign key is not an index. Many (most?) DBMSes allow a FK to exist without an index.
lolive 5y ago
Coming from the graph database / semantic web area, I would propose foreign key/ primary key relationships in the DB to be detailed and named in a schema description, and then queries reference those relationships by name to define the needed joins.
- criticaltinker 5y ago
  Yup totally agree - OP is an interesting proposal and has provoked a lot of quality commentary regarding the tradeoffs of clarity vs conciseness, implicit vs explicit, FK performance, etc. But IMO you’ve raised the important long term consideration - do graph based schemas and query languages obviate the need to model foreign keys explicitly? If this JOIN FOREIGN proposal is an incremental step forward, what’s the next big leap?
  - lolive 5y ago
    The JPA annotations of the DAO entities should not be the place to look for the data model of a DB. #imho
tester756 5y ago
While we are at it let's write SQL queries starting from FROM. `FROM users SELECT *` It'd allow tooling to provide IntelliSense better.
- miohtama 5y ago
  While this might be a joke, the world has been ripe for a replacement for SQL… for the last 20 years.
  - hdjrudni 5y ago
    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.
  - robocat 5y ago
    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.
    - Too 5y ago
      LINQ?
- vosper 5y ago
  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!
  - hdjrudni 5y ago
    Ya..that one is particularly sad because it was recent. Don't know why they felt the need to botch that.
antender 5y ago
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 types of conditions and are equivalent to WHERE clause)
- nrabulinski 5y ago
  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?
  - antender 5y ago
    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.
  - antender 5y ago
    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.
deleted 5y ago
[deleted]
tapas73 5y ago
I will write a letter to santa asking to get this into sql. During 10 years in data warehouse I constantly wondered why SQL does not have something like that. Reducing verboseness is nice, but the main perk is the correctness. Oh.. if I got a cent every time I found a bug in colleagues sql, because of join accidentally multiplying/doubling rows... :-)
bawolff 5y ago
Seems like minor syntactic sugar. I'm not opposed but really only seems mildly useful at best.
halayli 5y ago
It's a great proposition. However, I would suggest having it as a prefix rather than postfix of the JOIN expression to reduce implementation side effects and avoid ambiguity between keyword 'FOREIGN' and a table called 'FOREIGN'. example: FOREIGN LEFT JOIN
slt2021 5y ago
not a good idea, constaints are one thing and joins are another thing. plus I can join on conditions other than =. the problem that author is trying to solve can be easily solved by a view: 1. Declare a view with all necessary JOINs once 2. select from view only what you need, aggregate what you want 3. Optimizer will throw out unnecessary stuff and optimize query while all JOIN logic will be declared only once and will be hidden inside the view plus each DBMS has its own flavor of SQL and will have its own query optimizer nuances when dealing with joins, especially nested via CTEs/views/lateral queries,etc.
- Sesse__ 5y ago
  Your #3 is very optimistic. You can't just remove a join (be it inner, left, or really any other type) just because you didn't refer to any fields from one of the sides, and it's even harder when aggregation is in the mix. There are cases where you can without influencing the result, but they are special-case optimizations and not universally supported across databases.
slotrans 5y ago
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 is heap/clustered, and literally every other implementation detail of the RDBMS. 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. More importantly, it's _not a good idea_, because that semantic separation between data on one side, and the machinery of acceleration and validation on the other side, is critical to the value prop of the relational model and a big reason why it's been so hyper-successful. So no, let's not do this.
- JoelJacobson 5y ago
  > 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_target ] conflict_action where conflict_target can be one of: ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ] ON CONSTRAINT constraint_name [1] https://www.postgresql.org/docs/current/sql-insert.html https://www.postgresql.org/docs/current/sql-insert.html
  - bradleyjg 5y ago
    PostgresSQL isn’t SQL though. Vendor extensions have done all kinds of inelegant or even outright ill advised things from the beginning.
    - JoelJacobson 5y ago
      The INSERT ... ON CONFLICT UPDATE patch has been documented here: https://wiki.postgresql.org/wiki/UPSERT#UPSERT_as_implemented_in_practice https://wiki.postgresql.org/wiki/UPSERT#UPSERT_as_implemente... There are some clear benefits possible only thanks to specifying constraint names. Quoted from the link: * The SQL-standard MERGE doesn't provide for choosing an index, so use of a unique index would need to be conditioned on equality quals against indexed columns in order to provide the behavior being discussed for UPSERT. * The ON expression will need to be evaluated to see whether it properly compares to a unique index on the target table. Initially this will need to be done to determine whether the MERGE is allowed at all; later it will determine which sort of plan is allowed. It would be easier to match an index name, provided the index name is known and stable.
srcreigh 5y ago
We should reject this simply because it does not provide any clear benefits, but meanwhile asks for a lot. People to learn the FK names in their DB, people to learn new SQL syntax (and tooling to support it). It's actually difficult to list all the problems with this proposal. It's just unnecessary and the effort required to predict all the problems it will cause isn't worth doing.
dstola 5y ago
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
- cryptonector 5y ago
  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.
JoelJacobson 5y ago
Dear @Ayesh @CRConrad @DevKoala @Sesse__ @Too @Tostino @Upitor @antender @benjiweber @bradleyjg @branko_d @btilly @clintonb @cogman10 @cpill @criticaltinker @cryptonector @degenerate @dragonwriter @dspillett @dstola @gavinray @gigatexal @goto11 @jeff-davis @jimbobmcgee @jmull @jpz @lolive @magicalhippo @matteote @mjevans @mpolichette @mritchie712 @mrweasel @mtone @nicoburns @nrabulinski @nvartolomei @pella @rcoveson @ridaj @salawat @seadan83 @silon42 @slotrans @slt2021 @srcreigh @tapas73 @thom @torgard @tqi @unbanned @wruza @yunohn @zeroimpl @zmmmmm @zoomablemind, I've updated the Gist with some alternative syntax proposals [1] that affects/addresses your comments, and therefore wanted to notify you about such changes. I would greatly appreciate if you could provide additional feedback, either by replying here, or leave comments on the Gist via Github. Thank you all. [1]: https://gist.github.com/joelonsql/15b50b65ec343dce94db6249cfea8aaa#alternative-syntax https://gist.github.com/joelonsql/15b50b65ec343dce94db6249cf...

187 points by JoelJacobson 5y ago

unbanned 5y ago

>This improved in SQL-92: Did it?

JoelJacobson 5y ago
Yes, I think so, at least according to: https://learnsql.com/blog/history-of-sql-standards/ https://learnsql.com/blog/history-of-sql-standards/ What makes you think otherwise?

unbanned 5y ago

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?

JoelJacobson 5y ago
Foreign keys both define relationships between tables, and also enforce referential integrity. The discussion is about how we could potentially mine various additional value from foreign keys as an information resource, rather than just mostly being about referential integrity.
cryptonector 5y ago
Why do you think it's opaque? They declare relations between tables. That's structure, intent. Making intent clearer is generally a good thing.

JoelJacobson 5y ago

This is the same idea as already posted in this thread https://news.ycombinator.com/item?id=29687134 https://news.ycombinator.com/item?id=29687134, but with a more in-depth explanation that couldn't fit in the comment field, and with some syntax improvements, such as using "FOREIGN", which is a reserved keyword, instead of the previously suggested "->" notation. Thanks for all the valuable comments on last proposal. Excited to hear what you think about this update.

gigatexal 5y ago

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

clintonb 5y ago
How is this more explicit than specifying join columns today?
- tapas73 5y ago
  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.
  - gigatexal 5y ago
    Exactly this.
  - cryptonector 5y ago
    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.
- cryptonector 5y ago
  The intent is more explicit. Indirection != implicit.

torgard 5y ago

I dig it! I prefer defining tables like this: CREATE TABLE category ( id int GENERATED ALWAYS AS IDENTITY, name text ); CREATE TABLE post ( id int GENERATED ALWAYS AS IDENTITY, category_id int REFERENCES category (id) ON DELETE CASCADE ); That is, category.id rather than category.category_id. But the USING clause doesn't work with that style, as far as I understand. This would make my queries nicer.

thom 5y ago
I've been quite happy having fully prefixed column names for a long time now. Makes joins easier, big views clearer, and random exports more readable out of the box. Also in my case it's also easier to line up unique column names with things like Clojure specs but I accept that's a niche concern.
JoelJacobson 5y ago
Much cleaner, I agree, that's a big win. Will add that to the list of benefits. Will also add a "Drawbacks / Remaining issues" section to the Gist, from all the valuable comments so far in this thread, thank you all, positive as well as negative comments, all very helpful.

hn_throwaway_99 5y ago

This is my "Thanks, I hate it" response. Reason being if you use the example they gave: SELECT f.title, f.did, d.name, f.date_prod, f.kind FROM films f JOIN FOREIGN f.films_did_fkey d You need to implicitly know the table that films_did_fkey points to, because 'd' is just a table alias. I can't think of anywhere else in the SQL standard where you can introduce a table alias without explicitly referencing the table. In my opinion making code essentially unreadable unless you have other background information is an antipattern.

JoelJacobson 5y ago
I think your concern is addressed by the idea further down in proposal; giving the foreign keys the same names as the referenced tables. This example was provided: SELECT f.title, f.did, d.name, f.date_prod, f.kind FROM films f JOIN FOREIGN f.distributors d
- hn_throwaway_99 5y ago
  No, my concern is not that you can also specify the table name that explicitly states the name of both tables, it's that you have proposed a syntax where it is possible to leave off the explicit table name. If you got rid of the syntax that uses the underlying "magic" of needing to know which table the foreign key points to, I'd be more amenable.
  - JoelJacobson 5y ago
    There are two asymmetrical cases; one of them needs two pieces of information, the other three. Having to always specify both tables would syntax wise be redundant, but I agree it’s worth considering it might be a good thing to improve readability, which would help especially in the case where the foreign key isn’t/cannot be given the same name as the referenced table.
  - mrweasel 5y ago
    Yeah, my concern is people in the real world. If you can "magically" join on a foreign key and leave the table name out, or still call the thing anything you want, some smart ass will abuse the live crap out of it and I'll somehow be left to deal with it. I like things to be explicit. Tell me what you're joining and how you want to join it. What is the use case for this? Other than saving some typing, and let's face it, sometimes a little extra typing now, will save you a lot of trouble later. The proposal claims: "The idea is to improve the SQL language", but is it really better?

hnthrowaway0315 5y ago

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

dolmen 5y ago
> 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?
- hnthrowaway0315 5y ago
  I just say something similar, doesn't have to be the exact form.
stingraycharles 5y ago
Simple, effective, and really useful in a lot of situations. I like it!
nvartolomei 5y ago
Some have support for this. - BQ https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax https://cloud.google.com/bigquery/docs/reference/standard-sq... - CH https://clickhouse.com/docs/en/sql-reference/statements/select/#except-modifier https://clickhouse.com/docs/en/sql-reference/statements/sele...
- hnthrowaway0315 5y ago
  Thanks, these really look neat. Unfortunately we only use Vertica :/
piaste 5y ago
Similarly, I'd love some form of GROUP BY every column except for <these> 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.

pella 5y ago

related: pgsql-hackers thread and context https://www.postgresql.org/message-id/flat/1aec0dd0-dc27-40e6-89ee-2d487fb990fb%40www.fastmail.com#ae69d491b10eff8f5684e07ff6e3eb0b https://www.postgresql.org/message-id/flat/1aec0dd0-dc27-40e...

Ayesh 5y ago

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.

JoelJacobson 5y ago
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.
- zmmmmm 5y ago
  > 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.
  - JoelJacobson 5y ago
    Thanks for explaining, I have no experience of ORMs, always written my queries manually. This was insightful. Thanks also for the word “inertia”, good one, will add to my vocabulary.

thom 5y ago

Of all the myriad indignities of SQL, this isn't near the top of my list. I also don't like making the names of objects like foreign keys and indexes first class concerns in your queries, that's a whole new layer of cognitive overhead.

degenerate 5y ago
Agree. I'd much prefer they worked on the way MySQL determines what indexes to use on a multi-table join, so there could be less emphasis on the need to ANALYZE TABLE and FORCE INDEX when the DB is seemingly being dumb, but there's not enough information in EXPLAIN to tell you where it's actually getting tripped up. Troubleshooting full table scans on large sets of data is a nightmare.
- Tostino 5y ago
  Slightly different group of people working on MySQL vs this proposal.
- dspillett 5y ago
  > but there's not enough information in EXPLAIN to tell you where it's actually getting tripped up Unfortunately I don't think providing that information is generally possible. In some specific cases there are useful details it could provide, but there would usually be a myriad of other similar details that are irrelevant and if it included all those you'd not see the wood for the trees. EXPLAIN and query plan outputs in other DBs are “this is what I did” not really “why I did what I did”. To make the query planner bright enough to know what details would be useful to you, would probably pretty much require making it bright enough to do the optimisation job without you¹. [1] picking better indexes without hints, even creating those that are often needed, etc. MS's SQL Server tries to do this a bit with index suggestions. These are sometimes handy, but often at best for guidance². I've seen people blindly follow these suggestions to get a %-or-few gain from a small set of queries that could see orders of magnitude improvement with just a little tweaking elsewhere³, slowly amassing collections of indexes for very specific cases, sometimes multiple on the same key columns but each INCLUDEing a different mix of other data, that balloon their storage requirements⁴. [2] a nudge in the direction of “Mr Dev/DBA, you might want to think about how I'd avoid scanning this large object or performing many many thousands of seeks on this other one” [3] refactoring non-sargable predicates, index changes on other tables being referred to, getting rid of “SELECT *” particularly when referring to hideous views, ... [4] and having the knock-on effects of slowing insert/update activity & important admin functions (particularly backups)

btilly 5y ago

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 existence of a foreign key constraint for the definition of the query. Thereby meaning that if a foreign key constraint becomes a performance problem, we're stuck with it rather than having a solution. Features have consequences. And I don't like the consequences of making business rules that are now explicit in the query, be instead implicit in the table design.

JoelJacobson 5y ago
Good point, but addressable: Simply decouple the relationship definition and referential integrity check, allowing a user to drop the referential integrity check if desired, but keeping the relationship definition. I cannot see why you would not want to at least always store the information a certain table/column(s) references some other table/column(s) in the data model. Enforcing referential integrity is probably good in general too, but I agree you might need to disable it for some FKs, in some databases, like PostgreSQL before they got FOR KEY SHARE locks.
- hn_throwaway_99 5y ago
  At this point you should realize your proposal is a non-starter, and I didn't even realize btilly's objection originally. I can't think of any other feature in SQL where the rules of the query are actually dependent on something not explicit to the query itself. Even USING and NATURAL are just syntactic sugar that depends on the structure of the table, not on any underlying constraints. 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 SQL tools out there as it's a huge new feature, for some minor syntactic sugar. Ain't gonna happen.
  - JoelJacobson 5y ago

jimbobmcgee 5y ago

Is anyone actually likely to implement these based on some random gist on the internet, or are you just screaming into the void? Because, if you have that kind of clout, I've got an INSERT/SET syntax I'd like to put your way...

JoelJacobson 5y ago
I'm a small PostgreSQL contributor since 2010 myself. I probably can't write the whole patch myself, but if there is enough interest, and if we can work out the details and address the problems raised in this thread and elsewhere, I'm pretty confident we can do it. If we ever get there, the next step would be a reference implementation, probably in PostgreSQL, or to discuss a proposal in the SQL committee.
- jimbobmcgee 5y ago
  I'm often interested into what goes into changes to committee-driven standards. To an outsider, proposing a change seems to require one to be part of a shady cabal of Big-5 employees, skilled in the art of hiding subtle, privacy-invading features into inscrutable, plain-text RFCs. That or subjecting yourself to 30K+ what-abouters who deform your suggestion into something unrecognisable. It's refreshing to see a straightforward, well-formatted proposal (even if I do slightly prefer the `FROM table1 x JOIN table2 y ON x.fk` syntax suggested in other comments).
  - JoelJacobson 5y ago
    > To an outsider, proposing a change seems to require one to be part of a shady cabal of Big-5 employees, skilled in the art of hiding subtle, privacy-invading features into inscrutable, plain-text RFCs. I thought so too. Initially I just tried to get in contact with someone at the Swedish Institute for Standards (SIS), to see if it would be possible to send a proposal to someone in the SQL committee, which I thought was nearly impossible to become a member of. But as it turns out, SIS explained I could actually join the Swedish working group, and participate directly there, I just had to send in an application and get the approval from my employer, since there is a cost involved and you have to be a member via a company. Turns out ISO is a very open and democratic organization, just like Hacker News! :) I think this proposal could take years until it land, if it ever does, in some form, if concerns can be addressed, but SQL is here to stay for a while, so that doesn't scare me.

tqi 5y ago

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.

rcoveson 5y ago
FWIW we do have `NATURAL JOIN` already, which is a lot worse than this proposal.
- benjiweber 5y ago
  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 https://twitter.com/benjiweber/status/1476629550608101384 It's just risky if you don't design your schema with relational algebra in mind.
  - cpill 5y ago
    I'm 20 years I have never seen anyone use relational algebra. schemas have to be self documenting albeit only in the real world.
    - salawat 5y ago
      ...I do... All the time. I'm cleaning up a mess of a namespace collision as we speak. I was the only one who saw it coming. I've spent the better part of a month getting people to believe it's an issue.
    - gavinray 5y ago
      SQL is relational algebra, the core of relational algebra is Selection, Projection, Cross-products, Unions, and Set Differences. SQL is a language that implements Relational Algebra/Relational Calculus
  - 5y ago

nicoburns 5y ago

I wish the SQL standard would add some basic syntax sugar, such as support for trailing commas and allowing use of aliases in more places.

mi_lk 5y ago

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

jdunstan 5y ago
w3schools and the online documentation for any database are good places to start (Postgres and SQL Server have pretty good documentation). There are also easy-to-google SQL Puzzles, if you're looking for something more advanced. I would also recommend learning about query plans and how to read them, they're invaluable for query optimization.
orthoxerox 5y ago
Basic SQL has lots of tutorials. Graduate from them to Markus Winand's use-the-index-luke.com and modern-sql.com
cryptonector 5y ago
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/ 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.

deleted 5y ago

[deleted]

Upitor 5y ago

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-statements (you need a between-statement as well)

Upitor 5y ago
Also, consider a ‘sales’ table with multiple references to a ‘calendar’ table: [shipped_date], [order_date], [received_date]
- JoelJacobson 5y ago
  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".
- CRConrad 5y ago
  Are those timestamps, or actual dates, i.e. meaning a whole day encompassing (usually) 24 hours? In many (most?) data warehousing projects I've seen, you make a "fake date" integer the primary key column of your Dates dimension. This integer consists of 10000 × YEAR_PART + 100 × MONTH_PART + 1 × DAY_PART of the date in question, so yesterday's New Year's Eve woul get 10000 × 2021 + 100 × 12 + 1 × 31 = 20211231. The date dimension itself has many more columns (often booleans, IS_WEEKEND, IS_HOLIDAY, etc; also the date parts themselves in both numeric and character form (12, 'December'), day of week (5 [or 6, depending on convention], 'Friday'), etc) that are used for BI and reporting. But since this generated ISO-8601-date-as-integer column is the primary key of the Dates table, it is also the value of the Date foreign key in all tables that reference Dates. That makes it incredibly handy in queries -- both during development and for ad-hoc reports -- of those tables without joining to the Dates dimension at all: grouping, sorting, limiting to a more manageable date interval in the WHERE clause... And it tells the reader exactly what the actual date in question is. (Well, at least readers who are used to ISO-8601-format dates.) As jerryp would have said, recommended.

jeff-davis 5y ago

I'm not sure this is a good idea. It means that a constraint on the data (a FK is a constraint) affects the semantics of the query. In SQL, those two concepts are seperate.

Guest42 5y ago

Is this not something that should be written at the app level rather than dml?

Tostino 5y ago
Foreign keys in general? I am not sure I understand what you mean...
- Guest42 5y ago
  Not something I’ve ever thought about but I think that it could be done via macro, I have written tsql functions in c# and imagine that other dialects have analogous functionality. It seems as though the point is to cut down on sql code, it’d also be possible to query the foreign keys and create a data structure that could feed a function for joins. I haven’t thought out the specifics but think this type of approach would be more practical than changing the sql standard.
  - Tostino 5y ago
    At that point you are just building a feature into an "ORM" of some sort. The entire point is to allow queries to be written in a more concise way for this simple equality join use case which is (total ballpark figure) ~half of the joins in my system.

thanatos519 5y ago

I dunno. I preferred the SQL-89 syntax.

samtheprogram 5y ago
Same here. I didn’t even realize that was an option. As a relative beginner to SQL, is there a real reason the JOIN syntax was needed at all beyond special inner/outer joins?
- Tostino 5y ago
  Sanity for your coworkers who may have to touch your SQL.

Smotko 5y ago

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 impossible to remember.

JoelJacobson 5y ago
It would indeed be difficult to remember, but the proposal also suggest changing the default naming convention for foreign keys, to give them the same name as the referenced table. If using an ORM, I would guess this proposal isn't useful, since then you wouldn't hand-write queries anyway, right? Except when you want to override the queries generated by the ORM? (I'm not an ORM user myself.)
- to11mtm 5y ago
  Speaking as someone who has used ORMs in the past and contributes to a LINQ Micro ORM... It might make tooling 'easier', but since backwards compatibility has to be considered the actual value add is questionable IMO. Most ORMs/MicroORMs will have tooling that sniffs out the DB Schema including foreign keys, and if you are using those bits (i.e. 'not hand written') most will do the right thing today. I suppose you could include some extra syntax for whatever DSL you're providing users.... IDK. Speaking as someone who is very comfortable in SQL, This feels more like syntactic sugar than anything else.
  - lolive 5y ago
    Could you please elaborate on this FK sniffing, and which tools are actually implementing that?
    - to11mtm 5y ago
      Ahhh, I know that Entity Framework would do this at one point, IDK if EF Core does or not. Linq2Db does it via T4 Template generation, so you can play with it more if you want [0] [0] - https://github.com/linq2db/linq2db/blob/64a0db9a9ed7787ff7551efeee516e955a055b24/Source/LinqToDB.Templates/LinqToDB.ttinclude#L430 https://github.com/linq2db/linq2db/blob/64a0db9a9ed7787ff755...

abhchand 5y ago

This feels like adding syntactic sugar in some sense. That is, a layer of convenience to accomplish something that can already be accomplished today. It also breaks the following: * It's better to be explicit than infer * Keep specifications simple

matteote 5y ago

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.

JoelJacobson 5y ago
> I wonder how it is expected to work with non-table references (views, CTEs, subqueries) It’s not, it’s only for the special but common case of joining two tables based on a foreign key.

zoomablemind 5y ago

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

branko_d 5y ago
> FK (which is just an index) Correction: foreign key is not an index. Many (most?) DBMSes allow a FK to exist without an index.

lolive 5y ago

Coming from the graph database / semantic web area, I would propose foreign key/ primary key relationships in the DB to be detailed and named in a schema description, and then queries reference those relationships by name to define the needed joins.

criticaltinker 5y ago
Yup totally agree - OP is an interesting proposal and has provoked a lot of quality commentary regarding the tradeoffs of clarity vs conciseness, implicit vs explicit, FK performance, etc. But IMO you’ve raised the important long term consideration - do graph based schemas and query languages obviate the need to model foreign keys explicitly? If this JOIN FOREIGN proposal is an incremental step forward, what’s the next big leap?
- lolive 5y ago
  The JPA annotations of the DAO entities should not be the place to look for the data model of a DB. #imho

tester756 5y ago

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

miohtama 5y ago
While this might be a joke, the world has been ripe for a replacement for SQL… for the last 20 years.
- hdjrudni 5y ago
  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.
- robocat 5y ago
  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.
  - Too 5y ago
    LINQ?
vosper 5y ago
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!
- hdjrudni 5y ago
  Ya..that one is particularly sad because it was recent. Don't know why they felt the need to botch that.

antender 5y ago

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 types of conditions and are equivalent to WHERE clause)

nrabulinski 5y ago
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?
- antender 5y ago
  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.
- antender 5y ago
  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.

deleted 5y ago

[deleted]

tapas73 5y ago

I will write a letter to santa asking to get this into sql. During 10 years in data warehouse I constantly wondered why SQL does not have something like that. Reducing verboseness is nice, but the main perk is the correctness. Oh.. if I got a cent every time I found a bug in colleagues sql, because of join accidentally multiplying/doubling rows... :-)

bawolff 5y ago

Seems like minor syntactic sugar. I'm not opposed but really only seems mildly useful at best.

halayli 5y ago

It's a great proposition. However, I would suggest having it as a prefix rather than postfix of the JOIN expression to reduce implementation side effects and avoid ambiguity between keyword 'FOREIGN' and a table called 'FOREIGN'. example: FOREIGN LEFT JOIN

slt2021 5y ago

not a good idea, constaints are one thing and joins are another thing. plus I can join on conditions other than =. the problem that author is trying to solve can be easily solved by a view: 1. Declare a view with all necessary JOINs once 2. select from view only what you need, aggregate what you want 3. Optimizer will throw out unnecessary stuff and optimize query while all JOIN logic will be declared only once and will be hidden inside the view plus each DBMS has its own flavor of SQL and will have its own query optimizer nuances when dealing with joins, especially nested via CTEs/views/lateral queries,etc.

Sesse__ 5y ago
Your #3 is very optimistic. You can't just remove a join (be it inner, left, or really any other type) just because you didn't refer to any fields from one of the sides, and it's even harder when aggregation is in the mix. There are cases where you can without influencing the result, but they are special-case optimizations and not universally supported across databases.

slotrans 5y ago

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 is heap/clustered, and literally every other implementation detail of the RDBMS. 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. More importantly, it's _not a good idea_, because that semantic separation between data on one side, and the machinery of acceleration and validation on the other side, is critical to the value prop of the relational model and a big reason why it's been so hyper-successful. So no, let's not do this.

JoelJacobson 5y ago
> 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_target ] conflict_action where conflict_target can be one of: ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ] ON CONSTRAINT constraint_name [1] https://www.postgresql.org/docs/current/sql-insert.html https://www.postgresql.org/docs/current/sql-insert.html
- bradleyjg 5y ago
  PostgresSQL isn’t SQL though. Vendor extensions have done all kinds of inelegant or even outright ill advised things from the beginning.
  - JoelJacobson 5y ago
    The INSERT ... ON CONFLICT UPDATE patch has been documented here: https://wiki.postgresql.org/wiki/UPSERT#UPSERT_as_implemented_in_practice https://wiki.postgresql.org/wiki/UPSERT#UPSERT_as_implemente... There are some clear benefits possible only thanks to specifying constraint names. Quoted from the link: * The SQL-standard MERGE doesn't provide for choosing an index, so use of a unique index would need to be conditioned on equality quals against indexed columns in order to provide the behavior being discussed for UPSERT. * The ON expression will need to be evaluated to see whether it properly compares to a unique index on the target table. Initially this will need to be done to determine whether the MERGE is allowed at all; later it will determine which sort of plan is allowed. It would be easier to match an index name, provided the index name is known and stable.

srcreigh 5y ago

We should reject this simply because it does not provide any clear benefits, but meanwhile asks for a lot. People to learn the FK names in their DB, people to learn new SQL syntax (and tooling to support it). It's actually difficult to list all the problems with this proposal. It's just unnecessary and the effort required to predict all the problems it will cause isn't worth doing.

dstola 5y ago

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

cryptonector 5y ago
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.

JoelJacobson 5y ago

Dear @Ayesh @CRConrad @DevKoala @Sesse__ @Too @Tostino @Upitor @antender @benjiweber @bradleyjg @branko_d @btilly @clintonb @cogman10 @cpill @criticaltinker @cryptonector @degenerate @dragonwriter @dspillett @dstola @gavinray @gigatexal @goto11 @jeff-davis @jimbobmcgee @jmull @jpz @lolive @magicalhippo @matteote @mjevans @mpolichette @mritchie712 @mrweasel @mtone @nicoburns @nrabulinski @nvartolomei @pella @rcoveson @ridaj @salawat @seadan83 @silon42 @slotrans @slt2021 @srcreigh @tapas73 @thom @torgard @tqi @unbanned @wruza @yunohn @zeroimpl @zmmmmm @zoomablemind, I've updated the Gist with some alternative syntax proposals [1] that affects/addresses your comments, and therefore wanted to notify you about such changes. I would greatly appreciate if you could provide additional feedback, either by replying here, or leave comments on the Gist via Github. Thank you all. [1]: https://gist.github.com/joelonsql/15b50b65ec343dce94db6249cfea8aaa#alternative-syntax https://gist.github.com/joelonsql/15b50b65ec343dce94db6249cf...