Getting Started With Oracle Week: Joins

Last Updated 8 years ago

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.

/*Inner!*/
SELECT T1.ID, T2.ID
FROM HR.T1, HR.T2 
WHERE T2.ID = T1.ID;

SELECT T1.ID, T2.ID
FROM HR.T1 T1 
JOIN HR.T2 T2 
ON (T1.ID = T2.ID);

/*Left!*/
SELECT T1.ID, T2.ID
FROM HR.T1 T1 
LEFT JOIN HR.T2 T2 
ON (T1.ID = T2.ID);

/*Right!*/
SELECT T1.ID, T2.ID
FROM HR.T1 T1 
RIGHT JOIN HR.T2 T2 
ON (T1.ID = T2.ID);

/*Full!*/
SELECT T1.ID, T2.ID
FROM HR.T1 T1 
FULL JOIN HR.T2 T2 
ON (T1.ID = T2.ID);


/*Cross and Outer Apply*/
SELECT t1.ID, x.WHATEVER
FROM HR.T1 t1
CROSS APPLY (
SELECT t2.ID * 10000000 AS "WHATEVER"
FROM HR.T2 t2
WHERE t2.ID = t1.ID
) x;

SELECT t1.ID, x.WHATEVER
FROM HR.T1 t1
OUTER APPLY (
SELECT t2.ID * 10000000 AS "WHATEVER"
FROM HR.T2 t2
WHERE t2.ID = t1.ID
) x;

/*Inner!*/

SELECT T1.ID, T2.ID

FROM HR.T1, HR.T2

WHERE T2.ID = T1.ID;

SELECT T1.ID, T2.ID

FROM HR.T1 T1

JOIN HR.T2 T2

ON (T1.ID = T2.ID);

/*Left!*/

SELECT T1.ID, T2.ID

FROM HR.T1 T1

LEFT JOIN HR.T2 T2

ON (T1.ID = T2.ID);

/*Right!*/

SELECT T1.ID, T2.ID

FROM HR.T1 T1

RIGHT JOIN HR.T2 T2

ON (T1.ID = T2.ID);

/*Full!*/

SELECT T1.ID, T2.ID

FROM HR.T1 T1

FULL JOIN HR.T2 T2

ON (T1.ID = T2.ID);

/*Cross and Outer Apply*/

SELECT t1.ID, x.WHATEVER

FROM HR.T1 t1

CROSS APPLY (

SELECT t2.ID * 10000000 AS "WHATEVER"

FROM HR.T2 t2

WHERE t2.ID = t1.ID

) x;

SELECT t1.ID, x.WHATEVER

FROM HR.T1 t1

OUTER APPLY (

SELECT t2.ID * 10000000 AS "WHATEVER"

FROM HR.T2 t2

WHERE t2.ID = t1.ID

) x;

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.

SELECT *
FROM HR.EMPLOYEES
NATURAL JOIN HR.DEPARTMENTS;

SELECT *

FROM HR.EMPLOYEES

NATURAL JOIN HR.DEPARTMENTS;

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

SELECT *
FROM HR.T1 JOIN HR.T2
USING(ID);

SELECT *

FROM HR.T1 JOIN HR.T2

USING(ID);

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:

CREATE TABLE HR.NorseGods
    (
      GodID INT NOT NULL ,
      GodName NVARCHAR2 (30) NOT NULL ,
      Title NVARCHAR2 (100) NOT NULL ,
      ManagerID INT NULL 
    );

INSERT  ALL     
INTO HR.NorseGods ( GodID, GodName, Title, ManagerID )VALUES       ( 1,  'Odin',  'War and stuff', NULL)
INTO HR.NorseGods ( GodID, GodName, Title, ManagerID )VALUES       ( 2,  'Thor',  'Thunder, etc.', 1 )
INTO HR.NorseGods ( GodID, GodName, Title, ManagerID )VALUES       ( 3,  'Hel',   'Underworld!',   2 )
INTO HR.NorseGods ( GodID, GodName, Title, ManagerID )VALUES       ( 4,  'Loki',  'Tricksy',       3 )
INTO HR.NorseGods ( GodID, GodName, Title, ManagerID )VALUES       ( 5,  'Vali',  'Payback',       3 )
INTO HR.NorseGods ( GodID, GodName, Title, ManagerID )VALUES       ( 6,  'Freyja','Making babies', 2 )
INTO HR.NorseGods ( GodID, GodName, Title, ManagerID )VALUES       ( 7,  'Hoenir','Quiet time',    6 )
INTO HR.NorseGods ( GodID, GodName, Title, ManagerID )VALUES       ( 8,  'Eir',   'Feeling good',  2 )
INTO HR.NorseGods ( GodID, GodName, Title, ManagerID )VALUES       ( 9,  'Magni', 'Weightlifting', 8 )
SELECT * FROM DUAL;

CREATE TABLE HR.NorseGods

(

GodID INT NOT NULL ,

GodName NVARCHAR2 (30) NOT NULL ,

Title NVARCHAR2 (100) NOT NULL ,

ManagerID INT NULL

);

INSERT ALL

INTO HR.NorseGods ( GodID, GodName, Title, ManagerID )VALUES ( 1, 'Odin', 'War and stuff', NULL)

INTO HR.NorseGods ( GodID, GodName, Title, ManagerID )VALUES ( 2, 'Thor', 'Thunder, etc.', 1 )

INTO HR.NorseGods ( GodID, GodName, Title, ManagerID )VALUES ( 3, 'Hel', 'Underworld!', 2 )

INTO HR.NorseGods ( GodID, GodName, Title, ManagerID )VALUES ( 4, 'Loki', 'Tricksy', 3 )

INTO HR.NorseGods ( GodID, GodName, Title, ManagerID )VALUES ( 5, 'Vali', 'Payback', 3 )

INTO HR.NorseGods ( GodID, GodName, Title, ManagerID )VALUES ( 6, 'Freyja','Making babies', 2 )

INTO HR.NorseGods ( GodID, GodName, Title, ManagerID )VALUES ( 7, 'Hoenir','Quiet time', 6 )

INTO HR.NorseGods ( GodID, GodName, Title, ManagerID )VALUES ( 8, 'Eir', 'Feeling good', 2 )

INTO HR.NorseGods ( GodID, GodName, Title, ManagerID )VALUES ( 9, 'Magni', 'Weightlifting', 8 )

SELECT * FROM DUAL;

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.

SELECT 
GODID, 
TITLE, 
LPAD(' ', LEVEL * 2, ' ') || GODNAME AS "GODNAME", 
MANAGERID,
LEVEL,
SYS_CONNECT_BY_PATH(GODID, '/') AS "PATH",
SYS_CONNECT_BY_PATH(NVL(MANAGERID, 0), '/') AS "PARENT_PATH"
FROM HR.NorseGods ng
--START WITH GODID = 1
START WITH MANAGERID IS NULL
CONNECT BY PRIOR GODID = MANAGERID
ORDER SIBLINGS BY GODID;

SELECT

GODID,

TITLE,

LPAD(' ', LEVEL * 2, ' ') || GODNAME AS "GODNAME",

MANAGERID,

LEVEL,

SYS_CONNECT_BY_PATH(GODID, '/') AS "PATH",

SYS_CONNECT_BY_PATH(NVL(MANAGERID, 0), '/') AS "PARENT_PATH"

FROM HR.NorseGods ng

--START WITH GODID = 1

START WITH MANAGERID IS NULL

CONNECT BY PRIOR GODID = MANAGERID

ORDER SIBLINGS BY GODID;

Let’s talk about some of that!

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.
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.
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.
Here comes CONNECT BY PRIOR, which joins GODID to MANAGERID, which is the point of the whole thing.
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.

JOIN ME NEXT TIME

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!

Getting Started With Oracle Week: Generating Test Data

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.

Reply
Thomas
June 7, 2016 3:44 pm

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.

Reply
- Marcin S
  June 8, 2016 9:06 am
  
  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?
  
  Reply
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 . (https://blogs.msdn.microsoft.com/dotnet/2012/07/20/the-net-framework-4-5-includes-new-garbage-collector-enhancements-for-client-and-server-apps/)

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

Reply