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 
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.


1 Answer 1


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:

    EXTRACTVALUE(definition_xml.COLUMN_VALUE, ...) AS domain_name 
      (   SDE.GDB_ITEMS_VW i 
          SDE.GDB_ITEMTYPES it 
          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.

  • Just to be clear, in your CROSS JOIN fine print, when you say 'it's best not to mix the comma join with explicit JOIN', do you mean that it would be best to replace the comma with CROSS JOIN in this query?
    – User1974
    Commented Oct 29, 2016 at 13:08
  • 2
    @Wilson456 yes. Although it doesn't matter for the query, you might be surprised when you add (later) another join. Unless you know what you are doing, don't mix comma-separated joins with explicit JOIN. Commented Oct 31, 2016 at 10:30
  • More info: 'An alternative way of achieving the same result is to use column names separated by commas after SELECT and mentioning the table names involved, after a FROM clause.' w3resource.com/sql/joins/cross-join.php
    – User1974
    Commented Oct 31, 2016 at 11:19
  • 2
    The "columns names after SELECT" is orthogonal. FROM a, b is equivalent to FROM a CROSS JOIN b. That's all is needed really. The SELECT list can have * or a.* or b.* or any column names and expressions, independent of you write the join. Commented Oct 31, 2016 at 11:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.