Oracle Join Syntax – Comma-Separated Join (Cross Join)

join;oraclesyntax

I don't understand how this query works:

1    SELECT       
2        EXTRACTVALUE(definition_xml.COLUMN_VALUE, 'SubtypeFieldInfo/DomainName') AS domain_name
3    FROM        
4        SDE.GDB_ITEMS_VW i 
5        JOIN SDE.GDB_ITEMTYPES it ON
6            i.Type = it.UUID
7            ,TABLE(XMLSEQUENCE(XMLType(Definition).Extract('/DETableInfo/Subtypes/Subtype/FieldInfos/SubtypeFieldInfo'))) definition_xml

What does the comma at the start of line #7 do?

My guess is that whoever wrote the query used some sort of SQL short-hand. Perhaps if someone could help re-format the query in 'beginner SQL' it might help.

Best Answer

The comma is the implicit join operator, ANSI SQL-89 syntax. No different than the comma in:

select ... 
from a , b             -- <-- this comma
where a.aid = b.aid;

I think you can replace it with CROSS JOIN^*, assuming a recent enough Oracle version.

The i, it and definition_xml are the aliases of the 3 joined tables (the 2 tables are base tables or views and the 3rd is something like a derived table, Oracle calls them "table collection expressions") but they are no different regarding how they can be joined or referenced.

The parentheses around the joins and the last table are completely optional. I added them just to show how the query is parsed:

SELECT       
    EXTRACTVALUE(definition_xml.COLUMN_VALUE, ...) AS domain_name 
FROM        
      (   SDE.GDB_ITEMS_VW i 
        JOIN 
          SDE.GDB_ITEMTYPES it 
        ON
          i.Type = it.UUID
      )
    ,                           -- or: CROSS JOIN 
      (  TABLE(...) definition_xml
      ) 
;

^* ^{CROSS JOIN and the comma join operator have different precedence but this doesn't matter for the specific query. As a good practice though, it's best not to mix the comma join with explicit JOIN.}

Related Solutions

Peculiar Oracle outer join syntax case

I tried to perform the mechanical process. I hope I remember it right.

This leads to:

SELECT ...
FROM A
         join B on A.A_ID = B.A_ID
    left join C on B.B_ID = C.A_ID and B.B_KEY = C.B_KEY and 'CONSTANT' = C.X_ID
    left join D on C.C_ID = D.C_ID
    left join E on B.A_ID = E.A_ID and B.B_KEY = E.B_KEY

In short I think Leigh Riffel's answer is correct.

Note

in old days the rule to memorize was: oracle where A.a = B.b (+) becomes A.a *= B.b in SQL-Server old syntax the plus goes to the opposite side and becomes a star, which means A left join B on A.a = B.b

Counting results for cross join

You could do this:

SELECT COUNT(*) FROM table1 t1 CROSS JOIN table1 t2;

or this format which will allow you to copy and paste any query without rewriting it and use it as a subquery:

SELECT COUNT(*) FROM (
  SELECT t1.firstname, t2.lastname
  FROM table1 t1 CROSS JOIN table1 t2
) t3;

Best Answer

Related Solutions

Peculiar Oracle outer join syntax case

Counting results for cross join

Related Question