[ORACLE, VERTICA] SQL 비교

< JOIN >

• ORACLE

SELECT *

    FROM TABLE_1 A, TABLE_2 B

 WHERE A.COL1 = B.COL1(+)

       AND A.COL2 = B.COL2(+)

 

• VERTICA

   SELECT *

       FROM TABLE_1 A

LEFT JOIN TABLE_2 B

             ON A.COL1 = B.COL2

           AND A.COL2 = B.COL2

 

 

< SubQuery >

- Vertica doesn't support more than 1 level of correlated sub-queries.

- You should convert them into JOINs.

- Vertica SubQuery 제약에 대한 공식문서 내용 : https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AnalyzingData/Queries/Subqueries/SubqueryRestrictions.htm

Subquery Restrictions

 

• ORACLE

SELECT

                 (SELECT COL1 FROM TABLE1 X

                   WHERE B.COL2 = X.COL2

                         AND (SELECT YMD FROM TABLE2 BB

                                   WHERE BB.COL3 = (SELECT COL3 FROM TABLE3 AA

                                                                       WHERE B.PK1 = AA.PK1

                                                                             AND B.PK2 = AA.PK2)

                                  ) BETWEEN X.YMD1 AND X.YMD2

                  ) AS SUBQUERY

FROM TABEL4 C 

INNER JOIN TABLE5 A ON...

INNER JOIN TABLE6 B ON...

 

• VERTICA

SELECT

                 (SELECT COL1 FROM TABLE1 X

                   WHERE B.COL2 = X.COL2

                         AND BB.YMD BETWEEN X.YMD1 AND X.YMD2

                  ) AS SUBQUERY

FROM TABEL4 C 

INNER JOIN TABLE5 A ON...

INNER JOIN TABLE6 B ON...

INNER JOIN TABLE3 AA ON...

INNER JOIN TABLE2 BB ON...