Skip to content

feature_07_join_tables

Jump to bottom
Tony Jang edited this page Oct 5, 2020 · 2 revisions

Feature: Join Tables

여러 테이블의 레코드를 조합하여 하나의 열로 표현하는 Join 문과 관련된 기능을 제공합니다.

Supported Language

✔ = 지원하는 기능
❌ = 지원하지 않는 기능
⚠ = 지원 예정

Feature MySql PostgreSql JSql Oracle SqlServer
Join Tables ✔ ✔ ✔ ✔ ✔
Join Tables (Pivot Columns) ✔ ✔ ✔ ✔ ❌

Case 01. Join Tables

Example Query

SELECT * FROM left_table l JOIN right_table r ON l.uid = r.uid

Database

Database.Table = sample_db.left_table

Columns = name, uid

Database.Table = sample_db.right_table

Columns = age, uid

Expected QSI Tree

Analyze Result

  • name (derived)

    • name (join)
      • name (derived: l)
        • name
          • Reference : sample_db.left_table

  • uid (derived)

    • uid (join)
      • uid (derived: l)
        • uid
          • Reference : sample_db.left_table

  • age (derived)

    • age (join)
      • age (derived: r)
        • age
          • Reference : sample_db.right_table

  • uid (derived)

    • uid (join)
      • uid (derived: r)
        • uid
          • Reference : sample_db.right_table

해당 쿼리에서는 left_table과 right_table을 서로 Join 합니다.

left_table과 right_table에 name, uid, age, uid로 총 4개의 Column이 있으므로 총 4개의 Column이 추적되며 left_table과 right_table에서 같은 이름인 uid 컬럼이 있지만, 마지막까지 추적하여 Reference를 확인하면 left_table과 right_table 모두 정상적으로 추적됩니다.

Case 02. Join Tables (Pivot Columns)

Example Query

SELECT * FROM left_table JOIN right_table USING ( uid )

Database

Database.Table = sample_db.left_table

Columns = name, uid

Database.Table = sample_db.right_table

Columns = age, uid

Expected QSI Tree

Analyze Result

  • uid (derived)

    • uid (join)
      • Reference : sample_db.left_table
      • Reference : sample_db.right_table

  • name (derived)

    • name (join)
      • Reference : sample_db.left_table

  • age (derived)

    • age (join)
      • Reference : sample_db.right_table

left_table에는 uid랑 name 컬럼이 있고 right_table에는 uid랑 age 컬럼이 있습니다.

두개의 테이블을 묶을때 uid 컬럼을 기준으로 묶게 되므로 uid 컬럼에는 left_table과 right_table 총 2개의 테이블의 레퍼런스를 가지고 있습니다.

Case 03. Join Tables (comma)

SELECT * from left_table, right_table

Database

Database.Table = sample_db.left_table

Columns = name, uid

Database.Table = sample_db.right_table

Columns = age, uid

Expected QSI Tree

Analyze Result

  • name (derived)

    • name (join)
      • name (derived)
        • name
          • Reference : sample_db.left_table

  • uid (derived)

    • uid (join)
      • uid (derived)
        • uid
          • Reference : sample_db.left_table

  • age (derived)

    • age (join)
      • age (derived)
        • age
          • Reference : sample_db.right_table

  • uid (derived)

    • uid (join)
      • uid (derived)
        • uid
          • Reference : sample_db.right_table
Clone this wiki locally