# Join



# μ‘°μΈμ΄λž€?

두 개 μ΄μƒμ˜ ν…Œμ΄λΈ”μ΄λ‚˜ λ°μ΄ν„°λ² μ΄μŠ€λ₯Ό μ—°κ²°ν•˜μ—¬ 데이터λ₯Ό κ²€μƒ‰ν•˜λŠ” 방법

ν…Œμ΄λΈ”μ„ μ—°κ²°ν•˜λ €λ©΄, 적어도 ν•˜λ‚˜μ˜ μΉΌλŸΌμ„ μ„œλ‘œ κ³΅μœ ν•˜κ³  μžˆμ–΄μ•Ό ν•˜λ―€λ‘œ 이λ₯Ό μ΄μš©ν•˜μ—¬ 데이터 검색에 ν™œμš©ν•œλ‹€.


# JOIN μ’…λ₯˜


  • INNER JOIN
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • SELF JOIN


  • # INNER JOIN

    κ΅μ§‘ν•©μœΌλ‘œ, κΈ°μ€€ ν…Œμ΄λΈ”κ³Ό join ν…Œμ΄λΈ”μ˜ μ€‘λ³΅λœ 값을 보여쀀닀.

    SELECT
    A.NAME, B.AGE
    FROM EX_TABLE A
    INNER JOIN JOIN_TABLE B ON A.NO_EMP = B.NO_EMP
    

  • # LEFT OUTER JOIN

    κΈ°μ€€ν…Œμ΄λΈ”κ°’κ³Ό μ‘°μΈν…Œμ΄λΈ”κ³Ό μ€‘λ³΅λœ 값을 보여쀀닀.

    μ™Όμͺ½ν…Œμ΄λΈ” κΈ°μ€€μœΌλ‘œ JOIN을 ν•œλ‹€κ³  μƒκ°ν•˜λ©΄ νŽΈν•˜λ‹€.

    SELECT
    A.NAME, B.AGE
    FROM EX_TABLE A
    LEFT OUTER JOIN JOIN_TABLE B ON A.NO_EMP = B.NO_EMP
    

  • # RIGHT OUTER JOIN

    LEFT OUTER JOINκ³ΌλŠ” λ°˜λŒ€λ‘œ 였λ₯Έμͺ½ ν…Œμ΄λΈ” κΈ°μ€€μœΌλ‘œ JOINν•˜λŠ” 것이닀.

    SELECT
    A.NAME, B.AGE
    FROM EX_TABLE A
    RIGHT OUTER JOIN JOIN_TABLE B ON A.NO_EMP = B.NO_EMP
    

  • # FULL OUTER JOIN

    합집합을 λ§ν•œλ‹€. A와 B ν…Œμ΄λΈ”μ˜ λͺ¨λ“  데이터가 κ²€μƒ‰λœλ‹€.

    SELECT
    A.NAME, B.AGE
    FROM EX_TABLE A
    FULL OUTER JOIN JOIN_TABLE B ON A.NO_EMP = B.NO_EMP
    

  • # CROSS JOIN

    λͺ¨λ“  경우의 수λ₯Ό μ „λΆ€ ν‘œν˜„ν•΄μ£ΌλŠ” 방식이닀.

    Aκ°€ 3개, Bκ°€ 4개면 총 3*4 = 12개의 데이터가 κ²€μƒ‰λœλ‹€.

    SELECT
    A.NAME, B.AGE
    FROM EX_TABLE A
    CROSS JOIN JOIN_TABLE B
    

  • # SELF JOIN

    μžκΈ°μžμ‹ κ³Ό μžκΈ°μžμ‹ μ„ μ‘°μΈν•˜λŠ” 것이닀.

    ν•˜λ‚˜μ˜ ν…Œμ΄λΈ”μ„ μ—¬λŸ¬λ²ˆ λ³΅μ‚¬ν•΄μ„œ μ‘°μΈν•œλ‹€κ³  μƒκ°ν•˜λ©΄ νŽΈν•˜λ‹€.

    μžμ‹ μ΄ κ°–κ³  μžˆλŠ” μΉΌλŸΌμ„ λ‹€μ–‘ν•˜κ²Œ λ³€ν˜•μ‹œμΌœ ν™œμš©ν•  λ•Œ 자주 μ‚¬μš©ν•œλ‹€.

    SELECT
    A.NAME, B.AGE
    FROM EX_TABLE A, EX_TABLE B
    


# [μ°Έκ³ ]

μ΅œμ’… μˆ˜μ • : 12/17/2022, 7:23:59 AM