The JOIN syntax in SQL that no-one knows about

[@hoi-polloi1863]

This is really nice! I suppose you could get a similar result by joining the scores against a full join on the list of unique quarters vs unique players, but this partitioned business seems really clean.

[@DatabaseDude]

Yes, you can achieve it with a full join, but not sure how well that would scale to a real world case (eg 100s of thousands of customers across, say, 12 months)

[@cakeman58]

Hi Conner. I'm retired for over 2 years now and don't play in Oracle like I used to (daily). I felt my SQL skills were pretty strong after using Oracle for almost 30 years, but I have to admit I didn't know about a partitioned outer join. Thanks for posting about this!

[@DatabaseDude]

That is why it was worth a video!

[@geoffstrickler]

Useful, and I think that’s unique to Oracle. The Syntax in SQL Server or other systems may be different. Now, for your specific example, what would be a useful addition is play time by quarter. Clearly that’s additional data entry and likely a separate table that tracks (at a minimum) times of player substitutions, from which you can create play-time by player by quarter, then join that to the score information. And, of course, you can also use that to show or omit players didn’t play in a game or range of games.

[@DatabaseDude]

Agreed - but whilst fine for a basketball game, not sure that would scale nicely to a real world enterprise use case (eg 10,000 customers across 12 months etc)

[@StephenMoreira]

Yea very neat, did that back in the day when I had a more SQL intensive role.

[@krakajak67]

I learn something about SQL every time I watch your videos. And that’s after nearly 40 years working with SQL databases. Thanks.

[@DatabaseDude]

Glad to help!

[@chad_baldwin]

Well that's cool. Definitely not what i was expecting it to be. I thought for sure this video was going to be about chiastic join syntax. (I don't know what it's officially called, but that's what Itzik Ben-Gan I believe calls it).

[@nothinghere1996]

great language.

[@ehsnils]

I started with SQL in the beginning of the 1990's on Oracle (5.1) and at that time there wasn't even a "LEFT" or "RIGHT" join, at most you could use a (+) syntax to make some things happening - and for some tricks it was necessary to create temporary tables to be efficient.

[@DatabaseDude]

ANSI joins came in for oracle at around 9i timeframe

[@vikram4755]

Wow.. thankfully after this i am not the one who doesnt know this 😂 Thanks Connor.

[@monawoka97]

Man as a C++ dev SQL is like a whole other world. It makes sense but I have to stare at the syntax for so long to understand what it's doing.

[@DatabaseDude]

I am the same with C++ :-)

[@orterves]

I would have probably done that dual cross joined to a query of players, then left joined to the player quarter

[@DatabaseDude]

Indeed - there are plenty of solutions, but I always like solutions that are simple and easy.

[@Meower68]

I've used partition by on something like row_number() but never seen it on a join. Good one! I was going to query out a unique list of players, do a cross join on quarters (probably as a TVC), giving me a full set of tuples, then join that against the actual points data. I've done something similar, in the past, on DB/2. This is simpler. Will need to see just how many other databases out there implement that.

[@DatabaseDude]

a cartesian product is definitely an option in this (small data) case, but if you extrapolate that to a more real world example, that could get expensive very fast

[@LuminousWatcher]

I guess that the title should have been "The JOIN syntax in Oracle SQL that no-one knows about" - I do not see this in MySQL, DB2 or MariaDB, which i usually work with - an interesting function nontheless

[@DatabaseDude]

It's not up to Oracle what other vendors decide to leave out of their implementation. Partitioned outer join is in the SQL standard

[@RonPowell-i9g]

I'm not a database guy at all. I've done a bit of SQL work a long time ago. Real reason I'm here? The greyhound thumbnail picture :)

[@orterves]

I work with SQL and databases daily I'm still only here because of the greyhound

[@DatabaseDude]

Real reason I make tech videos? To post pictures of my hound :-)

[@DatabaseDude]

Bailey (my hound) likes your comment

[@RonPowell-i9g]

@@DatabaseDude I fully endorse this course of action, sir!

[@LewisCowles]

Why did you use this very Oracle specific syntax? It's interesting, but I'm not sure folks shouldn't jam in a few CTE's (1-4 is a very short union) and then cross-join distinct players, with the quarters. Even using NVL vs COALESCE seems to be a quite specific oracle-ism. Nice video, just looked like you were doing this on a phone, to which I'd considered that it would be sqlite.

[@DatabaseDude]

"very Oracle specific syntax" ...... Um... partitioned outer is in the SQL Standard (F403). Just because other vendors haven't caught up doesn't mean its "specific"

[@professortrog7742]

@@DatabaseDude F403 is intentionally omitted in the PostgreSQL implementation. And Dual is an oracle-ism too.

[@jcwynn4075]

​@@DatabaseDude that's literally the definition of specific 😂 also, word of advice: didn't be sassy in your own comment section

[@brianfisher6799]

In SQL Server i just cross join into a temp table constrained by whatever interval is needed and then select from that temp and left join to the data. Could also use CTE if you have a date dimension...

[@DatabaseDude]

true, but I think that is going to be a much more complicated SQL

[@johnkelly9970]

This is good. Well explained

[@DatabaseDude]

Thank you!

[@johnkelly9970]

@@DatabaseDude Wondering if there's a CROSS APPLY and OUTER APPLY video in the works ? : )

[@theboss9885]

Really no one knows it..thanks Conner

[@lyrebird712]

Would love if I could use this at work. Neither of our databases support this so I have a whole list of utility tables that I join with to get similar functionality.

[@DatabaseDude]

"neither"- what database ?

[@lyrebird712]

@@DatabaseDude MySQL 5.1 and 8.-something (don't recall the exact version, most of our applications are on the old one). I did look the docs up the other day at work and didn't see it :(

[@optimizer77]

Hi Conner, very interesting, thanks for sharing! Last time I worked with SQL was more than 10 yrs ago. I am curious, maybe it would even work by partitioning also by quarter? (basically getting rid of the synthesized table altogether and also the right outer join...)

[@DatabaseDude]

we need something to join to in order to use the "partition by"

[@thomasbrotherton4556]

Why not do some type of pivot? You could list one row per player, with columns for each quarter going across.

[@DatabaseDude]

Sure, that would work, but why immediately jump to changing the requirement?

[@debpatro]

🙏, I learned a new thing today.

[@DatabaseDude]

That is why this channel exists

[@SandeepSandhu]

Awesome!!

[@DatabaseDude]

Thanks!

[@andymoss4285]

I like this trick

[@m9600174]

Can you use this trick for both the quarter and player at once (so you don't have to generate the fake table with the 4 quarters)?

[@DatabaseDude]

You need to join to *something*

[@fisherh9111]

this is why I prefer Excel.

[@DatabaseDude]

fine for 100 rows...not so fine for 100million

[@fisherh9111]

@@DatabaseDude who needs more than 100 rows? Only 12 months in the year!

[@janbrittenson210]

Why not just have a second table with the players who played in each quarter and join with that. It would seem that if a player only played in one quarter you don't want 0's listed for the three they didn't play in...

[@DatabaseDude]

You could do that, but now you've got another table to maintain, populate, keep in sync with the base data.

[@iudithmentzel6288]

I think you should start to teach APEX also. For me, it will be my only way to learn APEX ... :)

[@mohamedaminesekkai7326]

Does it work on 11g 11.2.0.3 ?

[@DatabaseDude]

Been there since Oracle 9i

[@jasfigueroa]

I had not seen that use of PARTITION BY before so I asked our AI SQL Overlord for an explanation.... It seems that there is no spoon ;) The query you provided is a bit unusual in that it includes a PARTITION BY clause without an OVER clause, which is typically how window functions are used. In SQL, PARTITION BY is usually used within the context of a window function to define the partitions on which the window function operates. However, the provided query seems to mix the syntax and semantics of different SQL dialects or perhaps contains a typo. Clarifying the Intended Query Let's break down and correct the query for better understanding. Original Oracle Query sql Copy code SELECT qtr, player, NVL(pts, 0) FROM ( SELECT quarter, player, SUM(points) pts FROM basketball GROUP BY quarter, player ) b PARTITION BY (b.player) -- This seems incorrect or misplaced RIGHT OUTER JOIN ( SELECT rownum qtr FROM dual CONNECT BY LEVEL

[@DatabaseDude]

No typo :-) That is right where the PARTITION command goes for a partitioned join

[@orterves]

The AI gets confused because this is a very obscure syntax that Oracle implements (and pretty much nothing else does, apparently).

[@peabody3000]

why not a simple left outer join?

[@DatabaseDude]

Try it - you don't get the correct answer

[@ItsMrVD]

🤨

[@user-vo3st8kx7s]

I'm not convinced, first I'm 100% percent sure right join are evil, second the partition thing smells too much Oracle to be angel.😊

[@DatabaseDude]

If joins are evil, then perhaps relational databases are not for you :-)

[@user-vo3st8kx7s]

@@DatabaseDude read again, "right join" otherwise i won't bother watching.

[@RoamingAdhocrat]

ah, you started out so personable, but as soon as you said `select from dual` your eyes went black and a swarm of snakes came out of your mouth

[@DatabaseDude]

you'll need to upgrade to 23ai then

PARTITION BY is regular SQL. However, CONNECT BY is an Oracle-specific feature. It could have been stated early in the video. Nice video and good information but currently just wasted my time.

[@DatabaseDude]

true, but that element is not really the emphasis of the video - its just to synthesize some data. So you could take part and utilise whatever suits (generate_series etc etc)