Convert nested Oracle-style query (with subqueries) to ANSI SQL-CodePudding

I've got the following Oracle-style query that I have to convert to an ANSI SQL query so that I can understand what kind of joins they are:

SELECT
    ID,
    (SELECT TABLE1.COL1
        FROM VIEW1 TABLE1
        WHERE TABLE1.COL2 = (
            SELECT TABLE2.COL2
            FROM VIEW2 TABLE2
            WHERE TABLE2.ID = TABLE3.ID
        )
         AND TABLE1.COL3 = (
            SELECT TABLE2.COL3
            FROM VIEW2 TABLE2
            WHERE TABLE2.COL3 = TABLE3.COL3
        )
FROM VIEW3 TABLE3

It's the subqueries that I can't figure out. I have tried going through the Oracle documentation on this subject (https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm#i2066611) and tried using Google to search for an example or explanation or what kind of join this is however I can only find information on simpler joins online. I'm really stuck on this one and would appreciate any help.

I have also tried converting this query in Oracle SQL Developer but it doesn't convert the subqueries for me.

CodePudding user response：

If I read it correctly, that would be something like this:

select 
  t3.id,
  -- correlated subquery begins here ...
  (select v1.col1
   from view1 v1 join view2 v2 on v2.col2 = v1.col2 
                              and v2.col3 = v1.col3
   where v2.id   = t3.id        --> columns from subquery's tables (v2.id and v2.col3)
     and v2.col3 = t3.col3      --> are related to main query table's columns (t3.id and t3.col)
  )
  -- ... and ends here
from view3 t3;

As you searched for this kind of a join, then: that's a correlated subquery.

If you want to rewrite it some more, then switch to all joins:

select 
  t3.id,
  v1.col1
from view3 t3 join view2 v2 on v2.id   = t3.id   and v2.col3 = t3.col3
              join view1 v1 on v1.col2 = v2.col2 and v1.col3 = v2.col3;

CodePudding user response：

This is a correlated sub-query; it is not a join and it is ANSI standard:

SELECT ID,
       ( SELECT v1.COL1
         FROM   VIEW1 v1
         WHERE  v1.COL2 = (SELECT COL2
                           FROM   VIEW2 v2
                           WHERE  v2.ID = v3.ID)
         AND    v1.COL3 = (SELECT v2.COL3
                           FROM   VIEW2 v2
                           WHERE  v2.COL3 = v3.COL3)
       )
FROM   VIEW3 v3

If you want to convert it to a JOIN then:

SELECT v3.id,
       v1.col1
FROM   view3 v3
       LEFT OUTER JOIN view1 v1
       ON (    EXISTS(
                 SELECT 1
                 FROM   view2 v2a
                 WHERE  v2a.col3 = v3.col3
                 AND    v2a.col3 = v1.col3
               )
           AND EXISTS(
                 SELECT 1
                 FROM   view2 v2b
                 WHERE  v2b.id = v3.id
                 AND    v2b.col2 = v1.col2
               )
           )