Great article. Love the interactivity and visuals.
Looks neat, but you seem to think that an outer join and a left join are the same thing. That’s not true. (incorrect page: https://antonz.org/sql-cheatsheet/ )
I did a tutorial today where I explained how joins worked to a student. You can view the whiteboard here for my explanation: https://jamboard.google.com/d/17V39ADf01zcRs3OIDtQqwc41d7To8LKjzOzIXHOsnd0/edit?usp=sharing
Of course I don’t think so. Nor does the article say so.
I think they’re saying it because the linked page has a heading “Outer JOIN (LEFT JOIN)” and some text “An outer (left) JOIN, on the other hand, says [definition]”. I don’t know if you intended it, but this parenthetical looks left join is an alternate name for outer join, and having only one definition implies they have the same definition. If you don’t mean to say so, that would be a good place to add a sentence like “While outer join and left join are very similar, the difference is that…”
Perhaps that’s confusing, but it is a completely standard use of parentheses in English.
Do you have a source for it being standard English to use parentheses to note — if I understand correctly — alternative pieces of text that, if all substituted for the pieces of text before them (in some context-dependent way) would give an equally true sentence?
As a native English user, the only ‘English’ writing in which I’ve ever seen that use of parentheses is the Encyclopedia of Mathematics, which was translated from Russian and somewhat often uses grammar that sounds odd in English. Some examples of this use of parentheses in that encyclopedia can be seen in https://encyclopediaofmath.org/wiki/Vector and https://encyclopediaofmath.org/wiki/Neighbourhood. Personally, I find it confusing, particularly when they use parentheses both in this way and in the more usual way of clarifying, rather than replacing, something preceding.
Something similar is used commonly in mathematical writing in English, but only along with the word “respectively” or the abbreviation “resp.”. See https://linguaphiles.livejournal.com/2058743.html.
I don’t have a source, but I’ve definitely seen it, although it’s not super common. E.g.: A right- (resp: left-) handed person is someone who writes with their right (left) hand, and is usually left- (right-) footed.
I wouldn’t consider that the same phenomenon, when it’s marked with “resp.” (after German “bzw.”), whereas here and in the EoM it’s unmarked and looks the same as the standard use of parentheses to clarify what was just said.
When one is writing a piece like the OP, the goal is to be understood by the audience (whoever that may be). Conforming to standard syntax is not usually a goal, in and of itself; it’s desirable mostly inasmuch as using familiar grammar will help you to be understood. Saying “this text may be confusing, but it’s standard” misses the point: the goal was never to be standard—it was to be understandable!
Fair enough, silly of me to think so. pushcx is right that I think “Outer (left) join” is confusing/misleading.
Looking at both Postgres and SQL Server, LEFT JOIN and LEFT OUTER JOIN are equivalent as are JOIN and INNER JOIN. Both INNER and OUTER are optional keywords, included for compatibility.
Am I missing something?
A left join is an outer join, but not all outer joins are left joins.
In the tutorial whiteboard, I called a full outer join simply “outer join”, which is maybe confusing you, and was probably a bad idea on my part.
Looks like the “partitioned join” chapter needs some editing.
First you say “the table here can be a partitioned join table (not related to table partitions in any way!)” but then: “A partitioned join tells the database engine to perform the join independently within each of the partitions we have defined for a table”.
Also, “here’s how to solve the problem without partitioned joins” won’t solve the problem because it uses a list of dates from the sales table, and so if there are no sales on this day then that date won’t go into the final result. Basically partitioned tables is a left join on list of partitions.
What we all need is “SELECT date FROM DATES(‘2021-01-01’, ‘2022-01-01’)” or something like that. Maybe even just maintaining a table of dates just to be used as for this.
FULL OUTER JOIN is equivalent to two LEFT JOINs btw: