SQL language proposal: JOIN FOREIGN

Enforcement of relational integrity on bulk load should be optional -- you trust the data, right?

But also, if you want relational integrity, then you have to check it somewhere.

Lastly, this isn't about DML or bulk load but about query expressivity. You could easily have FOREIGN KEY constraints where the RDBMS is also instructed not to enforce relational integrity, either on bulk load or at all, and documenting the FK in the schema is still massively useful.

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.

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/15b50b65ec343dce94db6249cf...