Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can you have an INNER JOIN without the ON keyword?

While debugging into some Oracle code, I came across this query:

SELECT TPM_TASK.TASKID FROM TPM_GROUP 
INNER JOIN TPM_USERGROUPS ON TPM_GROUP.GROUPID = TPM_USERGROUPS.GROUPID 
INNER JOIN TPM_TASK
INNER JOIN TPM_GROUPTASKS ON TPM_TASK.TASKID = TPM_GROUPTASKS.TASKID 
INNER JOIN TPM_PROJECTVERSION ON TPM_TASK.PROJECTID = TPM_PROJECTVERSION.PROJECTID AND TPM_TASK.VERSIONID = TPM_PROJECTVERSION.VERSIONID 
INNER JOIN TPM_TASKSTAGE ON TPM_TASK.STAGEID = TPM_TASKSTAGE.STAGEID 
INNER JOIN TPM_PROJECTSTAGE ON TPM_PROJECTVERSION.STAGEID = TPM_PROJECTSTAGE.STAGEID 
ON TPM_GROUP.GROUPID = TPM_GROUPTASKS.GROUPID

I'm confused by the line:

INNER JOIN TPM_TASK

I haven't seen a JOIN without an ON clause before. Also confusing is the line:

ON TPM_GROUP.GROUPID = TPM_GROUPTASKS.GROUPID

This seems like a random ON clause without any matching JOIN. The query runs without any errors, and returns a bunch of data, so obvious the syntax is perfectly valid. Can someone shed some light on exactly what's going on here?

like image 692
Mike Christensen Avatar asked Jun 01 '12 19:06

Mike Christensen


3 Answers

Small universe... I ran across a tool generating this syntax yesterday and was rather flummoxed.

Apparently,

FROM a 
     INNER JOIN b
     INNER JOIN c ON (b.id = c.id)
     ON (a.id = c.id)

is equivalent to a nested subquery

FROM a
     INNER JOIN (SELECT <<list of columns>>
                   FROM b
                        INNER JOIN c ON (b.id=c.id)) c
             ON (a.id = c.id)
like image 182
Justin Cave Avatar answered Nov 09 '22 02:11

Justin Cave


I think that this is only a problem of ordering your query (since there are only INNER JOINs, the order of them is not really that important). I rearrenged your query and now it looks like this:

SELECT TPM_TASK.TASKID 
FROM TPM_GROUP 
INNER JOIN TPM_USERGROUPS 
    ON TPM_GROUP.GROUPID = TPM_USERGROUPS.GROUPID 
INNER JOIN TPM_GROUPTASKS 
    ON TPM_GROUP.GROUPID = TPM_GROUPTASKS.GROUPID
INNER JOIN TPM_TASK
    ON TPM_TASK.TASKID = TPM_GROUPTASKS.TASKID 
INNER JOIN TPM_PROJECTVERSION 
    ON TPM_TASK.PROJECTID = TPM_PROJECTVERSION.PROJECTID 
    AND TPM_TASK.VERSIONID = TPM_PROJECTVERSION.VERSIONID 
INNER JOIN TPM_TASKSTAGE 
    ON TPM_TASK.STAGEID = TPM_TASKSTAGE.STAGEID 
INNER JOIN TPM_PROJECTSTAGE 
    ON TPM_PROJECTVERSION.STAGEID = TPM_PROJECTSTAGE.STAGEID 

Does it make more sense to you now?, it does to me.

like image 39
Lamak Avatar answered Nov 09 '22 02:11

Lamak


It'd look fine if it had parenthesis in there...

SELECT TPM_TASK.TASKID 
FROM 
    TPM_GROUP 
    INNER JOIN TPM_USERGROUPS ON TPM_GROUP.GROUPID = TPM_USERGROUPS.GROUPID 
    INNER JOIN (
        TPM_TASK
        INNER JOIN TPM_GROUPTASKS ON TPM_TASK.TASKID = TPM_GROUPTASKS.TASKID 
        INNER JOIN TPM_PROJECTVERSION ON TPM_TASK.PROJECTID = TPM_PROJECTVERSION.PROJECTID 
            AND TPM_TASK.VERSIONID = TPM_PROJECTVERSION.VERSIONID 
        INNER JOIN TPM_TASKSTAGE ON TPM_TASK.STAGEID = TPM_TASKSTAGE.STAGEID 
        INNER JOIN TPM_PROJECTSTAGE ON TPM_PROJECTVERSION.STAGEID = TPM_PROJECTSTAGE.STAGEID 
    ) ON TPM_GROUP.GROUPID = TPM_GROUPTASKS.GROUPID

but since they are all inner joins I agree with Lamak's answer.

like image 4
dotjoe Avatar answered Nov 09 '22 02:11

dotjoe