🏫 한동대학교/Database

[DB] DB09ab - Advanced SQL

pangil_kim 2025. 4. 28. 15:27
728x90
Agenda
  • Join (조인)
  • Views (뷰)
  • Window functions (윈도우 함수)
  • Keys (키)

 

Join Operations
  • Join operations take two relations and return another relation
    조인 연산은 두 관계를 받아 또 다른 관계를 반환한다.
    • A join is a Cartesian product that requires tuples in the two relations match
      조인은 두 관계의 튜플이 일치해야 하는 '데카르트 곱'이다.
      -> 두 테이블의 특정 컬럼 값이 같을 때만 서로 연결해서 새로운 결과를 만든다는 의미한다.
      • It also specifies the attributes that are present in the result of the join (project)
        또한, 조인의 결과에 나타나는 속성들(프로젝트)을 지정한다.
        -> 조인 결과에서 필요한 컬럼(속성)만 골라서 보여주는 걸 의미한다.
    • Typically used as subquery expressions in the FROM clause
      일반적으로, FROM 절의 서브쿼리 표현식으로 사용된다.
  • Join types (조인의 유형)
    1. INNER JOIN : 조건에 맞는 튜플만 결과에 포함
      -> 매칭된 것만!
    2. OUTER JOIN : 조건에 맞지 않아도, 한쪽 데이터는 결과에 포함
      -> 매칭 안 돼도 살려준다.
  • Join conditions (조인의 조건)
    • NATURAL : 두 테이블에서 이름이 같은 컬럼끼리 자동으로 매칭해서 조인한다.
      -> 자동 매칭
    • ON <predicate> : 직접 조건을 써서 어떤 컬럼끼리 맞출지 지정한다. (ex: ON 학생.ID = 수강.ID)
      -> 직접 조건 작성
    • USING (A1, A2, …, An) : 공통 컬럼 이름만 지정해서 조인한다. (ex: USING (ID))
      -> 공통 컬럼 이름만 작성

 

Natural Join (내추럴 조인)
  • Natural join matches tuples with the same values for all common attributes, and retains only one copy of each common column
    내추럴 조인은 1) 모든 공통 속성에 대해 동일한 값을 가진 튜플을 매칭하고, 2) 공통 컬럼 중 하나만 유지한다.
    -> 1) 공통 값 매칭, 2) 중복 제거
    • E.g., List the names of students along with the course ID of the courses that they took
      예 : 학생들의 이름과 그들이 수강한 수강 ID를 나열하시오.
      1. 데카르트 곱 + WHERE 조건 매칭
        • SELECT name, course_id
          FROM student, takes
          WHERE student.ID = takes.ID;
      2. Same query in SQL with natural join:
        SQL에서 네추럴 조인으로 만든 같은 쿼리
        • SELECT name, course_id
          FROM student NATURAL JOIN takes;
  • The FROM clause can have multiple relations combined using natural join:
    FROM 절에는 여러 관계를 내추럴 조인으로 결합할 수 있다.
    • SELECT A1, A2, … An
      FROM r1 NATURAL JOIN r2 NATURAL JOIN … NATURAL JOIN rn
      WHERE P ;

 

 

Caveat
  • E.g., (Incorrect) (잘못된 예시)
    SELECT dept_name, course_id, name, title, credits
    FROM student NATURAL JOIN takes NATURAL JOIN course;
    • student, takes, course 테이블을 NATURAL JOIN 하면,
      dept_name 같은 공통 이름 컬럼을 자동으로 매칭하는데,
      이게 진짜 관련 있는 컬럼인지 검증 없이 그냥 이어버린다.
      그래서 학생 전공과 과목 개설 학과를 엉뚱하게 매칭할 위험이 있다!

  • Beware of unrelated attributes with same name getting equated incorrectly 
    이름이 같은 관련 없는 속성끼리 잘못 연결될 수 있으니, 주의해야 한다.
    -> 테이블에 이름만 같고, 의미는 다른 컬럼이 있을 때,
    NATURAL JOIN이 무조건 이름만 보고 자동으로 연결해버릴 수 있어서
    엉뚱한 조인이 될 수 있다!
    • E.g., List the names of students along with the titles of courses that they have taken
      학생들의 이름과 그들이 수강한 과목의 제목을 나열하시오.
      • Correct
        • SELECT name, title
          FROM student NATURAL JOIN takes, course
          WHERE takes.course_id = course.course_id;
      •  Incorrect
        • SELECT name, title
          FROM student NATURAL JOIN takes NATURAL JOIN course;
        • This query omits all (student name, course title) pairs where the student takes acourse in a department other than the student's own department
          이 쿼리는 학생이 자신의 학과 외의 과목을 수강한 경우 (학생 이름, 과목 제목) 쌍을 모두 생략한다.
          -> 즉, 이건 course까지 NATURAL JOIN 해버려서,
          dept_name 같은 것도 자동 연결해버릴 위험이 있다.

 

Natural Join with USING Clause
  • To avoid the danger of equating attributes erroneously, use the USING construct
    속성을 잘못 일치시키는 위험을 피하기 위해 'USING' 구문을 사용한다.
    -> NATURAL JOIN처럼 "이름이 같으면 다 자동 연결"하는 게 아니라,
    "내가 원하는 컬럼만" 정확하게 골라서 연결할 수 있게 해주는 방법이다.
    • USING: allows us to specify exactly which columns should be equated
      USING : '정확히 어떤 컬럼을 일치시킬지'를 지정할 수 있다. 
      • 같은 이름 컬럼은 결과에 하나만 남는다!
      • 내부적으로는 조인할 때 두 테이블 다 컬럼을 보지만,
        결과에서는 중복 컬럼을 하나로 합쳐서 깔끔하게 보여줌.
    • E.g.,
      • SELECT name, title
        FROM (student NATURAL JOIN takes) JOIN course USING (course_id)

 

 

JOIN … ON
  • The ON condition allows a general predicate over the relations being joined
    ON 조건은 조인되는 관계에 대해 '일반적인 조건을 지정'할 수 있다.
    • Written like a WHERE clause predicate
      WHERE절 조건처럼 작성된다.
    • E.g.,
      • SELECT *
        FROM student JOIN takes ON student.ID = takes.ID
        • The ON condition specifies that a tuple from student matches a tuple from takes if their ID values are equal
          ON 조건은 student의 튜플과 takes의 튜플이 ID 값이 같을 때 매칭됨을 지정한다.
        • Equivalent to: 
          • SELECT name, course_id
            FROM student, takes
            WHERE student.ID = takes.ID;

 

Inner Join
  • Inner join: Does not preserve nonmatched tuples
    Inner join : 매칭되지 않은 튜플을 보존하지 않는다.
    -> 조건에 맞지 않는 행은 결과에 아예 안 나온다!
    • Tables are joined based on common columns mentioned in the ON or USING clause
      테이블은 ON 또는 USING 절에 언급된 공통 컬럼을 기준으로 조인된다.
      -> ON이나 USING으로 어떤 컬럼을 기준으로 연결할지 딱 정해서 조인한다.
    • One can specify the condition with an ON or USING construct
      조건은 ON 또는 USING 구문으로 명시할 수 있다.
      -> 어떤 컬럼을 맞출지 직접 ON 또는 USING을 이용해서 써줄 수 있다.
  • C.f., Natural join: assumes the join condition to be where same- named columns in both tables match
    참고 : 내추럴 조인은 두 테이블 모두에 이름이 같은 컬럼이 있을 때, 이를 기준으로 조인한다.
    • One cannot use ON or USING
      내추럴 조인에서는 ON 또는 USING을 사용할 수 없다.
      -> 그냥 컬럼 이름 같으면 무조건 매칭!
    • In the result of a natural join, repeated columns are avoided
      내추럴 조인의 결과에서는 중복 컬럼이 제거된다. 

 

Natural Join
  • Natural join: Some tuples in either or both relations being joined may be lost
    내추럴 조인 : 조인되는 두 관계 중 일부 튜플이 소실될 수 있다.
    -> 이름이 같은 컬럼끼리 매칭하는데,
    값이 안 맞는 행은 결과에서 아예 사라진다!
    (매칭 성공한 행만 남기고, 매칭 실패한 건 버린다.)
  • SELECT *
    FROM course NATURAL JOIN prereq;
    • 만약 course에는 있는데 prereq에는 없는 과목,
      또는 prereq에는 있는데 course에는 없는 과목은?
      → 결과에 포함되지 않는다! (소실돼)

 

 

Outer Join
  • An extension of the join operation that avoids loss of information
    정보 손실을 방지하기 위한 조인 연산의 확장이다.
    -> NNER JOIN처럼 매칭된 것만 가져오는 게 아니라,
    매칭 안 된 데이터도 살려서 결과에 포함하는 조인이다.
    • Outer join preserves those tuples that would be lost in a join by creating tuples in the result containing null values
      Outer join은 조인 시, 손실될 튜플을 null 값을 포함한 결과 튜플로 보존한다.
      -> 원래 매칭 실패했으면 버렸을 튜플도,
      null을 채워서 결과에 살려둔다.
      (없는 부분은 빈칸(null)으로 표시!)
    • Computes the join and then adds tuples form one relation that does not match tuples in the other relation to the result of the join
      조인을 수행한 후 한 관계에만 존재하는 튜플들을 결과에 추가한다.
      -> 일단 매칭되는 건 정상적으로 조인하고,
      매칭 안 되는 데이터는 null 채워서 추가로 붙인다.
  • Three forms of outer join:
    • LEFT OUTER JOIN : 왼쪽 테이블 데이터는 무조건 살린다
    • RIGHT OUTER JOIN : 오른쪽 테이블 데이터는 무조건 살린다
    • FULL OUTER JOIN : 양쪽 테이블 모두 살린다.

 

Inner Join with NATURAL
  • Natural join: Some tuples in either or both relations being joined may be lost
    내추럴 조인 : 조인되는 두 관계 중 일부 튜플이 소실될 수 있다.
    -> 이름이 같은 컬럼끼리 매칭하는데,
    값이 안 맞는 행은 결과에서 아예 사라진다!
    (매칭 성공한 행만 남기고, 매칭 실패한 건 버린다.)
  • SELECT *
    FROM course NATURAL JOIN prereq;
    -> course 테이블과 prereq 테이블을 조인하는데, 둘 다 같은 이름의 컬럼(course_id)가 있을 것이다.
    그래서 같은 course_id를 가진 과목들만 결과에 남는다.
    • 만약 course에는 있는데 prereq에는 없는 과목,
      또는 prereq에는 있는데 course에는 없는 과목은?
      → 결과에 포함되지 않는다! (소실돼)

 

Left Outer join with NATURAL
  • Left outer join: Preserves tuples only in the relation named before (to the left of) the operation
    Left outer join : 연산 왼쪽에 명시된 관계의 튜플만 보존한다.
    -> 왼쪽 테이블(course)의 데이터는 무조건 결과에 남긴다!
    오른쪽 테이블(prereq)과 매칭이 안 되어도,
    왼쪽 테이블의 튜플은 NULL을 채워서라도 보여준다.
  • SELECT *
    FROM course NATURAL LEFT OUTER JOIN prereq;
    -> course 테이블 기준으로,
    prereq랑 매칭되든 안 되든
    course에 있는 과목은 다 결과에 나옴.
    prereq 쪽 정보가 없으면 NULL로 채움.

 

 

Right Outer Join with NATURAL
  • Right outer join: Preserves tuples only in the relation named after (to the right of) the operation
    Right outer jon : 연산 오른쪽에 명시된 관계의 튜플만 보존한다.
    -> 오른쪽 테이블(prereq)의 데이터는 무조건 결과에 남긴다!
    왼쪽테이블(course)과 매칭이 안 되어도,
    오른쪽 테이블의 튜플은 NULL을 채워서라도 보여준다.
  • SELECT *
    FROM course NATURAL RIGHT OUTER JOIN prereq;
    ->     prerequ 테이블 기준으로,
    course랑 매칭되든 안 되든
    prereq에 있는 과목은 다 결과에 나옴.
    course 쪽 정보가 없으면 NULL로 채움.

 

 

Full Outer Join with NATURAL
  • SELECT *
    FROM course NATURAL FULL OUTER JOIN prereq;

  •  MySQL does NOT support FULL join
    MYSQL은 FULL 조인을 지원하지 않는다.
    • Alternative: use the UNION of left and right joins
      대안 : LEFT와 RIGHT 조인을 UNION으로 결합해서 사용한다. 
    • SELECT course_id, title, dept_name, credits, prereq_id
      FROM course NATURAL LEFT OUTER JOIN prereq
      UNION
      SELECT course_id, title, dept_name, credits, prereq_id
      FROM course NATURAL RIGHT OUTER JOIN prereq;
      • In order to perform UNION properly, the attributes of both join queries must be aligned
        UNION을 올바르게 사용하기 위해서는 두 조인 쿼리의 속성들이 일치해야 한다.
        -> UNION을 쓰려면,         왼쪽 쿼리랑 오른쪽 쿼리 결과의 컬럼 수와 컬럼 순서가 완전히 똑같아야 한다.

 

Join Types and Conditions
  • Join type: Defines how tuples in each relation that do not match any tuples in the other relation are treated
    조인 유형 : 한 관계에 매칭되지 않는 튜플을 다른 관계와 어떻게 처리할지를 정의한다.
    • INNER JOIN
    • LEFT OUTER JOIN
    • RIGHT OUTER JOIN
    • FULL OUTER JOIN
  • Join condition: Defines which tuples in the two relations match
    조인 조건 : 두 관계의 어떤 튜플이 매칭될지를 정의한다.
    • NATURAL
    • ON <predicate>
    • USING (A1, A2, …, An)

 

Join Types

 

Join Condition
  • Join condition (조인 조건)
    • NATURAL: Joins two tables based on same attribute name and datatypes
      NATURAL : 같은 속성의 이름과 데이터 타입을 기준으로 두 테이블을 조인한다.
      • SELECT * FROM course NATURAL JOIN prereq;
    • ON <predicate>: Joins two tables based on the column(s) explicitly specified in the ON clause
      ON <predicate>: ON 절에서 명시적으로 지정한 컬럼을 기준으로 두 테이블을 조인한다. 
      • SELECT * FROM course
        JOIN prereq ON course.course_id = prereq.prereq_id;
    • USING (A1, A2, …, An): Joins two tables based on common attribute name(s) listed next to USING
      USING (A1, A2, …, An): USING 뒤에 나열한 공통 속성 이름을 기준으로 두 테이블을 조인한다.
      • SELECT * FROM course
        JOIN prereq USING (course_id)

 

Inner Join vs. Natural Join
INNER JOIN NATURAL JOIN
ON 절에 명시적으로 지정된 컬럼을 기준으로 두 테이블을 조인한다. 동일한 속성 이름과 데이터 타입을 기준으로 두 테이블을 조인한다.
결과 테이블은 두 테이블의 모든 속성(중복된 컬럼 포함)을 가진다. 결과 테이블은 두 테이블의 모든 속성을 가지지만, 공통 컬럼은 하나만 유지한다.
두 테이블 모두에 존재하는 레코드만 반환된다. LEFT, RIGHT, FULL 조인의 명시가 없으면, 공통 컬럼을 기준으로 행을 반환한다.

 

  • Inner join
    • SELECT * FROM course
      INNER JOIN prereq ON course.course_id = prereq.prereq_id;
  • Natural join
    • SELECT *
      FROM course NATURAL JOIN prereq
      ON course.course_id = prereq.prereq_id;
      <- NOT VALID!
  • Inner join 
    • SELECT * FROM course
      INNER JOIN prereq ON course.course_id = prereq.course_id;
      • Equivalent to:
        • SELECT * FROM course
          JOIN prereq ON course.course_id = prereq.course_id;

  • Natural join
    • SELECT *
      FROM course NATURAL JOIN prereq;

 

Outer Join vs. Natural Join
  • Right outer join
    • SELECT *
      FROM course NATURAL RIGHT OUTER JOIN prereq;
      • Equivalent to:
        • SELECT *
          FROM course RIGHT OUTER JOIN prereq
          USING (course_id);
        • SELECT *
          FROM course RIGHT OUTER JOIN prereq
          ON course.course_id = prereq.course_id;

  • Left outer join
    • SELECT *
      FROM course NATURAL LEFT OUTER JOIN prereq; Equivalent to:
      • SELECT *
        FROM course LEFT OUTER JOIN prereq
        USING (course_id);
      • SELECT *
        FROM course LEFT OUTER JOIN prereq
        ON course.course_id = prereq.course_id;

 

Natural Joins Are Often Avoided
  • Natural joins are often avoided in practice, because:
    내추럴 조인은 실무에서 종종 피한다. 이유는 아래와 같다.
    1. 가독성 문제
      : Natural joins are not particularly readable (by most SQL coders) and possibly not supported by various tools/libraries
      내추럴 조인은 대부분의 SQL 개발자에게 가독성이 좋지 않다. 다양한 도구/라이브러리에서도 지원되지 않을 수 있다.
    2. 정보 부족 문제
      : Natural joins are not informative; you cannot tell what columns are being joined on without referring to the schema
      내추럴 조인은 정보가 부족하다. 어떤 컬럼을 기준으로 조인하는지, 스키마를 보지 않고는 알 수 없다.
    3. 스키마 변경 취약석
      : Your join conditions are invisibly vulnerable to schema changes
      스키마 변경에 의한 조인 조건이 눈에 띄지 않게 손상될 위험이 있다.
      • Even if there are multiple natural join columns and one such column is removed from a table, the query will still execute
        여러 개의 내추럴 조인 컬럼이 있더라도, 하나의 컬럼이 테이블에서 제거되면, 쿼리는 여전히 실행되지만 결과는 잘못될 수 있다. 
      • But the result may not be correct and this change in behavior will be silent
        이 변경은 조용히 일어나기 때문에, 문제를 알아차리기 어렵다.
  • Hardly worth the effort; you are only saving about 10 seconds by not typing specific conditions
    구체적인 조건을 입력하는 데 10초밖에 걸리지 않기 때문에, 내추럴 조인은 그만한 가치가 없다.
728x90
댓글수0