Key 정리 및 JOIN

Key의 종류

Key란 릴레이션 안에서 각 튜플을 유일하게 식별할 수 있는 애트리뷰트 집합(set of attributes)

• 후보 키(candidate key)
  • 릴레이션 R(A1, A2...An)에 대한 애트리뷰트 부분 집합, K({Ai, Aj .. Ak})이 유일성(uniqueness)와 최소성(minimality)를 만족하는 키이다. 슈퍼키들 중에서 속성을 최소한의 개수로 개수로 하는 것이다.
• 복합 키(Composite Key): 2개 이상의 속성을 사용한 키이다.
• 슈퍼 키(Super key) : 유일성을 만족하는 키이다. 즉 최소성은 만족하지 않아도 되는데 어떤 속성끼리 묶던 중복값만 안나오고 서로 구별만 할 수 있으면 된다.
• 기본 키(Primary key) : 후보 키에서 선택된 키이다. NULL 값이 들어갈 수 없으며, 기본키로 선택된 속성(Attribute)은 동일한 값이 들어갈 수가 없다.
  • 더 짧게 말하면 relation에서 tuples를 unique하게 식별하기 위해 선택된 candiate key
• 대체 키(Surrogate key, alternate key, unique key) : 후보 키 중에 기본 키로 선택되지 않은 키이다.
• 외래 키(Foreign key) : 어떤 테이블(Relation) 간의 기본 키(Primary Key)를 참조하는 속성이다. 테이블(Relation)들 간의 관계를 나타내기 위해 사용된다. 외래키는 참조되는 테이블의 기본키와 동일한 키 속성을 가진다.
  • 한마디로 다른 relation의 PK를 참조하는 attributes set이다.
  • 참조되는 부모테이블이 먼저 생성된 뒤 데이터를 넣고, 참조하는 자식 테이블이 다음에 생겨야한다.
  • 관계형 데이터 모델에서는 기본키와 외래키에 의해서 개체(entity)간의 관계(relationship)을 유지한다.
  • 기본키-외래키 개념은 관계형 데이터베이스의 핵심 아이디어이다.

Constraints

relational database의 relations들이 언제나 항상 지켜줘야 하는 제약사항들이다.

implicit constraints

relational data model 자체가 가지는 constraints이다. 예를 들면 relation은 중복되는 tuple을 가질 수 없다는 예시가 있고 다른 예시론 relation 내에서는 같은 이름의 attribute를 가질 수 없다는 예시도 있다.

explicit constraints(schema-based constraints)

주로 DDL을 통해 schema에 직접 명시할 수 있는 constraints이다.

JOIN

join이란 둘 이상의 테이블을 연결해서 데이터를 검색하는 방법을 말한다. 연결하려면 테이블들이 적어도 하나의 컬럼을 공유하고 있어야 한다.

• 세타조인이란 두 릴레이션 사이에 곱집합에서 세타 조건을 만족하는 튜플만 모은 수평적 부분집합이다.
• 세타조인에서 세타가 =인 경우 동일 조인이라고 한다.
• 자연 조인이란 동일 조인의 결과에서 중복되는 애트리뷰트를 제거한 조인이다. 연결된 데이터를 두 테이블로 분리하여 데이터의 중복성을 피하고, 필요할 떄에만 연결하는 원리이다.
• 세미조인이란 R과 S의 릴레이션이 있을 때 R 세미 S라고 하면 R과 S가 세미조인이 되는 R의 튜플들의 모음이라는 뜻이다.

implicit join vs explicit join

implicit join

from 절에는 table들만 나열하고 where절에 join condition을 명시하는 방식이다.

SELECT D.name FROM employee AS E, department AS D WHERE E.id = 1 and E.dept_id = D.id;

이는 old-style join syntax이다. where절에 selection condition(E.id)과 join condition(E.dept_id = D.id)이 같이 있기 때문에 가독성이 떨어진다. 이때문에 복잡한 join 쿼리를 작성하다 보면 실수로 잘못된 쿼리를 작성할 가능성이 커지게 된다.

explicit join

위의 문제로 JOIN을 명시할 수 있는 join이 필요하게 되었다. 따라서 나온 것이 explicit join이다.

explicit join은 from절에 JOIN 키워드와 함께 joined table들을 명시하는 방식이다. from 절에서 ON 뒤에 join condition이 명시된다. 이 때문에 위의 단점들이 확실히 줄어들게 된다.

즉 가독성이 좋아지고 복잡한 join문을 작성할 때에도 실수할 가능성이 줄어들게 된다.

INNER JOIN

교집합으로, 기준 테이블과 join 테이블의 중복된 값을 보여준다. 보통 그냥 JOIN을 적게 되면 INNER가 생략되었다고 생각하면 된다. 가장 기본적인 타입의 JOIN이다.

기본적으로 JOIN table ON 조건과 같이 사용할 때 조건을 만족시키는 테이블을 표시해준다고 생각하면 된다.

따라서 inner join은 두 table에서 join condition을 만족하는 tuple들로 result table을 만드는 join이다. 이때문에 조건을 만족하지 못하는 튜플들은 테이블에 포함되지 않는다.

join condition의 연산자는 대부분의 연산자가 사용 가능하며 null 값을 가지는 tuple은 result table에 포함되지 못한다.

OUTER JOIN

내부 조인의 경우에는 공통 컬럼명 기반으로 결과 집합을 생성하지만 외부 조인은 조건문에 만족하는 않는 행도 표시해주는 조인입니다. 그래서, 조인을 했을 때 한쪽의 테이블에 데이터가 없어도 조인 결과에 포함시키는 조인이다.

보통 LEFT(RIGHT,FULL) JOIN 이라고 쓰면 LEFT(RIGHT, FULL) OUTER JOIN처럼 OUTER가 생략됐다고 생각하면 된다.

LEFT OUTER JOIN

왼쪽 외부 조인은 왼쪽에 A 테이블과 오른쪽에 B 테이블이 있을때 테이블 A의 모든 데이터와 테이블 B와 매칭이 되는 레코드를 포함하는 조인이다. 즉 왼쪽 테이블을 기준으로 똑같으면 값을 출력하고 아니라면 B테이블의 값을 null로 출력해서 A테이블은 일단 모두 나오는게 보장된다.

즉 A테이블은 모두 result table에 보장되며 B테이블은 조건이 매칭되지 않을 경우 null값으로 채워진다.

RIGHT OUTER JOIN

LEFT의 반대이다. B테이블이 모두 나오며 A테이블이 매칭되지 않을경우 null로 표시된다. 나머지 튜플들은 Join Condition에 매칭이 된 튜플들이다.

FULL OUTER JOIN

MtSQL에서는 명시적인 SQL 구문은 지원하지 않지만, UNION을 사용해 완전 외부 조인을 할 수 있다. 왼오른 모두 나오고 같은건 둘 다 나온다.

즉 result 테이블에 조건에 맞는 튜플 + 조건에 맞지 않는 A,B테이블의 모든 튜플을 담는다.

EQUI JOIN

join condition에서 = 컨디션을 사용해서 조회를 한다면 이를 equi join라고 한다.

SELF JOIN

셀프 조인은 자기 자신과 조인하는 조인이다. 예를 들어 자신의 테이블 중에 같은 부서나 같은 과목을 든는 아이를 출력하고 싶을때 사용한다.

ANTI JOIN

서브 쿼리내에서 존재하지 않는 데이터만 추출하여 메인 쿼리에서 추출하는 조인이다. NOT EXISTS나 NOT IN을 통해 사용 가능하다. 즉 이게 안들어있는걸 추출한다는 의미

USING

예를 들어 같은 컬럼이 조인을 통해 같은 이름의 컬럼의 id를 통해 조인을 할 때 result table에 두 번 표시되는 경우가 있다. 이 때 USING을 사용하면 된다.

SELECT * FROM empl E INNER JOIN depart D ON E.dept_id = D.dept_id;

위의 코드를 실행하게 되면 dept_id 컬럼이 테이블에 두 개가 나오게 된다. 하지만 USING을 사용하면 아래처럼 표현이 가능하다.

SELECT * FROM emp E INNER JOIN depart D USING (dept_id);

또한 dept_id가 맨 앞으로 가게 된다.

정리하자면 두 테이블이 equi join할 때 join하는 attribute의 이름이 같다면, USING으로 간단하게 작성이 가능하다. 이 때 같은 이름의 attribute는 한 번만 표시된다.

NATURAL JOIN

두 테이블에서 같은 이름을 가지는 모든 attribute pair에 대해서 equi join을 수행한다. join condition을 따로 명시하지 않는다. 사용 예시는 아래와 같다.

FROM table1 NATURAL [INNER] JOIN table 2

CROSS JOIN

두 table의 tuple pair로 만들 수 있는 모든 조합(= Catesian Product)을 result table로 반환한다. 그리고 위와 마찬가지로 join condition이 없다.

이는 implicit cross join과 explicit cross join이 각각 다르다.

• implicit cross join : FROM table1, table2
• explicit corss join : FROM table1 CROSS JOIN table2

SELF JOIN

table이 자기 자신에게 join하는 경우이다.