The JOIN syntax in SQL that no-one knows about
HTML-код
- Опубликовано: 7 июл 2024
- There is a type of JOIN that has existed in the Oracle Database for over 20 years, yet almost no-one has heard of it, even fewer use it regularly but it makes a whole class of complex SQL problems trivial to solve
Follow me!
blog: connor-mcdonald.com
twitter: / connor_mc_d
The Podcast!
podcasts.apple.com/au/podcast...
open.spotify.com/show/5dqVVft...
Subscribe for new tech videos every week
Other social media channels here: linktr.ee/connor
Music: Night Owl (Broke For Free), Kevin Mcleod (incomptech), Dyalla
#sql #database 
Наука
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!
That is why it was worth a video!
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.
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)
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.
I am the same with C++ :-)
I learn something about SQL every time I watch your videos. And that’s after nearly 40 years working with SQL databases. Thanks.
Glad to help!
I would have probably done that dual cross joined to a query of players, then left joined to the player quarter
Indeed - there are plenty of solutions, but I always like solutions that are simple and easy.
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.
ANSI joins came in for oracle at around 9i timeframe
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.
"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"
@@DatabaseDude
F403 is intentionally omitted in the PostgreSQL implementation.
And Dual is an oracle-ism too.
@@DatabaseDude that's literally the definition of specific 😂 also, word of advice: didn't be sassy in your own comment section
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
It's not up to Oracle what other vendors decide to leave out of their implementation. Partitioned outer join is in the SQL standard
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 :)
I work with SQL and databases daily
I'm still only here because of the greyhound
Real reason I make tech videos? To post pictures of my hound :-)
Bailey (my hound) likes your comment
@@DatabaseDude I fully endorse this course of action, sir!
Wow.. thankfully after this i am not the one who doesnt know this 😂
Thanks Connor.
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.
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)
Yea very neat, did that back in the day when I had a more SQL intensive role.
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...)
we need something to join to in order to use the "partition by"
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.
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
Awesome!!
Thanks!
Does it work on 11g 11.2.0.3 ?
Been there since Oracle 9i
🙏, I learned a new thing today.
That is why this channel exists
Really no one knows it..thanks Conner
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...
true, but I think that is going to be a much more complicated SQL
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)?
You need to join to *something*
This is good. Well explained
Thank you!
@@DatabaseDude Wondering if there's a CROSS APPLY and OUTER APPLY video in the works ? : )
I think you should start to teach APEX also.
For me, it will be my only way to learn APEX ... :)
I like this trick
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
No typo :-) That is right where the PARTITION command goes for a partitioned join
The AI gets confused because this is a very obscure syntax that Oracle implements (and pretty much nothing else does, apparently).
Why not do some type of pivot? You could list one row per player, with columns for each quarter going across.
Sure, that would work, but why immediately jump to changing the requirement?
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.
"neither"- what database ?
@@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 :(
great language.
why not a simple left outer join?
Try it - you don't get the correct answer
this is why I prefer Excel.
fine for 100 rows...not so fine for 100million
@@DatabaseDude who needs more than 100 rows? Only 12 months in the year!
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
you'll need to upgrade to 23ai then
🤨
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...
You could do that, but now you've got another table to maintain, populate, keep in sync with the base data.
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.
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)
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.😊
If joins are evil, then perhaps relational databases are not for you :-)
@@DatabaseDude read again, "right join" otherwise i won't bother watching.