Getting Started With Oracle Week: Joins

Oh, THAT relational data

Thankfully, most major platforms (mostly) follow the ANSI Standard when it comes to joins. However, not all things are created equal. Oracle didn’t have CROSS and OUTER APPLY until 12c, and I’d reckon they’re only implemented to make porting over from MS easier. It also introduced the LATERAL join at the same time, which does round about the same thing.

Here’s some familiar joins to keep you calm.

That was boring, huh? It’ll all work just as you expect it to. But we’re not done! Oracle is not without a couple neat things that it wouldn’t hurt SQL Server to implement.

Using, Naturally

I think these constructs are pretty neat. The first one is a Natural Join. This is kind of like Join Roulette, in that Oracle will choose a join condition based on two tables having a column with the same name.

The other slightly more exotic join syntax I like uses USING to shorten the join condition.

You can extend the USING syntax to join multiple columns, too, which I like because it cuts down on typing.

But what else?

Oracle also has some pretty fancy syntax for dealing with hierarchies. Even with all the options, it’s about 6 universes ahead of the recursive CTEs you have to bust out in SQL Server (if you’re not using a hierarchyid, which you’re probably not).

Here’s the Norse God table I used to show that SQL Server’s recursive CTEs are still serial in 2016:

Ignoring the somewhat awkward looking inserts I was experimenting with, that’ll get you the table structure. In Oracle, CONNECT BY is used to generate hierarchies. There are many built-in components that give you information about the structure of your hierarchy as well.

Let’s talk about some of that!

  1. The first thing you may notice is that we use LPAD to give an indented structure to the names to make the chain of command more obvious.
  2. Both LEVEL and SYS_CONNECT_BY are built in components you can use to see which step in the hierarchy you’re on, and how you’ve stepped through it so far.
  3. I’m also using STARTWITH to dictate which part of the hierarchy I want to begin recursion at. Since this is a small table, I want everything. I can either specify GODID = 1, or MANAGERID IS NULL. In this case, both indicate Odin.
  4. Here comes CONNECT BY PRIOR, which joins GODID to MANAGERID, which is the point of the whole thing.
  5. Lastly, ordering SIBLINGS by either GODID or MANAGERID NULLS FIRST gives us our desired display order.

Here are the results!

Any similarity to Marvel characters that might get us sued is absolutely ridiculous.

Any similarity to Marvel characters that might get us sued is absolutely ridiculous.


Just kidding, I wouldn’t do that to you.

Having standards is important. Especially if you drink a lot. The ANSI Standard gives us a good starting point for writing code that’s portable across multiple systems. Though it will (likely) never be flawless, joins are one area you can worry a bit less about.

Thanks for reading!

Previous Post
Getting Started With Oracle Week: Generating Test Data
Next Post
Getting Started With Oracle Week: Aggregating

4 Comments. Leave new

  • ken ambrose
    June 7, 2016 12:52 pm

    Oracle hierarchy support saved my butt on one important project for a large nuclear research site in palo alto, ca. Of course they work with tons of hazardous and deadly materials, and Fire and HAZMAT laws require _every_ hazardous material to be documented for location. including if indoors the room, container, shelf in container, position on shelf, etc. that the material is located.

    And of course the materials are often moved to different location, or removed.

    Tons of hierarchical data.

    Without Oracle’s hierarchy support, I would have committed hari-kari.

  • Respectively, I think the “Natural Join” syntactic evil waiting to inflict its error upon us. Much better to require the developer to be explicit. I also don’t see what the “Using” syntax gains other than to confuse new database developers.

    • Yeah, Natural Join requires you to know what are you doing. Or on what You are using it, to be more specific.

      Using is a very nice syntax tho, I don’t see it confusing, it’s more natural (for me) than natural join ;).
      And after a while you really start to appreciate it. It’s less keystrokes for same effect, at least when there is some naming convention that allows it.

      Ah, if You want to confuse new database devs, get them working on code with Oracle conventional syntax (the “+” syntax).
      Besides, I thought You want to confuse young ones?

  • Jay Ramachandran
    June 7, 2016 6:22 pm

    Thanks for this post I think it’s very useful. We have used hierarchyid with MS SQL but have seen problems as a garbage collection is triggered inside the SQL Server process immediately after and it slows down the whole box, they have made improvements with newer version of .net framework but in older versions the problems still persist . (

    Unfortunately per Microsoft there’s no way to limit or throttle the GC threads in older versions , so recursive cte is the one of the options in mssql.

    Will be nice to see these enhancements in t-SQL


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.