관계 대수
- 관계형 데이터베이스 관리 시스템에서 데이터를 조회하기 위한 수학적인 언어를 관계 대수라고 한다. 즉 원하는 데이터를 얻기 위해 What(무엇)을 설명하고, How(어떻게)에 대한 구체적인 처리 과정은 DBMS(데이터베이스 관리 시스템)가 결정한다.
- 일반 집합 연산자와 순수 관계 연산자로 구분할 수 있으며, 전자는 테이블을 집합으로 간주하고 집합 간의 연산을 수행한다. 후자는 테이블을 더 세밀하게 조작하기 위해 여러 연산을 제공한다.
- 구체적인 쿼리는 Mysql 8.2.0 기준으로 살펴보도록 한다.
일반 집합 연산자
1. UNION(∪)
- 두 릴레이션에 존재하는 튜플의 합집합을 구한 결과 릴레이션에서 중복되는 튜플은 제거한 연산자
- UNION ALL: 합집합에서 중복을 제거하지 않은 연산자
- UNION 연산자를 사용할 때는 반환하는 열의 수와 데이터 유형이 일치해야 한다.
select id, name from student
union
select student_id, name from attendance;2. INTERSECT(∩)
- 두 릴레이션에 존재하는 튜플의 교집합을 구하는 연산자
- Mysql에서는 INTERSECT 연산자를 지원하지 않기 때문에 JOIN하여 교집합을 찾을 수 있다.
select id, s.name
from student s
join attendance a
on s.id = a.student_id
where a.student_id is null;3. EXCEPT(−)
- 두 릴레이션에 존재하는 튜플의 차집합을 구하는 연산자
- Mysql에서는 직접적으로 EXCEPT연산자를 지원하지 않기 때문에 일반적으로 left join을 사용하거나 서브 쿼리 not exists 절을 사용한다.
select s.id, s.name
from student s
left join attendance a
ON s.id = a.student_id
where a.student_id is null;select s.id, s.name
from student s
where not exists (
select 1
from attendance a
where a.student_id = s.id
);4. 카티션 프로덕트(×)
- 두 릴레이션에 있는 튜플들의 순서쌍을 구하는 연산자
- cross join을 사용한다.
select *
from student
cross join attendance;순수 관계 연산자
1. PROJECT(π)
- 릴레이션에서 속성 리시트에 제시된 속성 값만을 추출하는 연산자
select * from student where name = "juny"2. SELECT(σ)
- 릴레이션에서 주어진 조건을 만족하는 튜플을 선택하는 연산자
select name, age from student; 3. JOIN(⨝)
- 두 개의 릴레이션에서 연관된 튜플을 결합하는 연산자
select *
from student s
join attendance a
on s.id = a.student_id;4. DEVISION(÷)
- 릴레이션 S의 모든 튜플과 관련 있는 릴레이션 R의 튜플들을 반환하는 연산자
- 대부분 관계형 데이터베이스 시스템에서 해당 연산을 직접적으로 제공하지 않는다.
- 특정 조건을 만족하는 튜플을 검색하는 쿼리를 작성한다.
계층형 질의
- 계층형 데이터란 같은 테이블에 계층적으로 상위와 하위가 있는 데이터를 말한다. 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해 계층형 질의를 사용한다.
- Mysql에서는 CTE(Common Table Expression)을 이용하여 재귀 호출할 수 있다.
- 하나의 쿼리 스크립트에서 마치 함수처럼 동작한다. 복잡한 쿼리를 여러 단계로 나누어 쿼리의 가독성을 향상 시킬 뿐만 아니라 작성한 CTE는 여러 번 참조되어 재사용될 수 있다.
- 재귀 종료 조건(base condition)은 일반적으로 재귀 쿼리가 더 이상 새로운 행을 생성하지 않을 때 자연스럽게 충족된다. (join CTE AS b on a.manager_id = b.member_id)
+-----------+------------+
| member_id | manager_id |
+-----------+------------+
| 1000 | NULL |
| 1001 | 1000 |
| 1005 | 1000 |
| 1002 | 1001 |
| 1003 | 1002 |
| 1004 | 1002 |
| 1006 | 1005 |
| 1007 | 1006 |
+-----------+------------+
with recursive CTE(member_id, manager_id, lvl)
as (
select member_id, manager_id, 0 as lvl
from MEMBER
where manager_id is null
// (1)
UNION ALL
// (2)
select a.member_id, a.manager_id, b.lvl + 1
from MEMBER a
join CTE AS b
on a.manager_id = b.member_id
// (3)
)
select member_id, manager_id, lvl
from CTE
order by member_id, lvl;
+-----------+------------+------+
| member_id | manager_id | lvl |
+-----------+------------+------+
| 1000 | NULL | 0 |
| 1001 | 1000 | 1 |
| 1002 | 1001 | 2 |
| 1003 | 1002 | 3 |
| 1004 | 1002 | 3 |
| 1005 | 1000 | 1 |
| 1006 | 1005 | 2 |
| 1007 | 1006 | 3 |
+-----------+------------+------+- with recursive는 재귀호출을 하겠다는 표현이다.
- (1)은 manager_id가 NULL인 행을 찾아 최상위 관리자(0)으로 설정한다.
- (3)은 CTE 테이블과 MEMBER 테이블을 조인하여 계층적 관계를 구한다.
- (2)은 구한 결과를 UNION ALL하여 그 결과를 CTE 테이블에 다시 저장한다.
- 재귀 동작 과정
- 1번째
- (1)
- member_id(1000), manager_id(NULL), lvl(0)
- (3)
- CTE 테이블은 아무 것도 없음
- (2)
- CTE: member_id(1000), manager_id(NULL), lvl(0)
- 2번째
- (1)
- member_id(1000), manager_id(NULL), lvl(0)
- (3)
- CTE: member_id(1001), manager_id(1000), lvl(1)
- CTE: member_id(1005), manager_id(1000), lvl(1)
- (2)
- CTE: member_id(1000), manager_id(NULL), lvl(0)
- CTE: member_id(1001), manager_id(1000), lvl(1)
- CTE: member_id(1005), manager_id(1000), lvl(1)
- 3번쨰
- (1)
- member_id(1000), manager_id(NULL), lvl(0)
- (3)
- CTE: member_id(1001), manager_id(1000), lvl(1)
- CTE: member_id(1005), manager_id(1000), lvl(1)
- CTE: member_id(1002), manager_id(1001), lvl(2)
- CTE: member_id(1006), manager_id(1005), lvl(2)
- (2)
- CTE: member_id(1000), manager_id(NULL), lvl(0)
- CTE: member_id(1001), manager_id(1000), lvl(1)
- CTE: member_id(1005), manager_id(1000), lvl(1)
- CTE: member_id(1002), manager_id(1001), lvl(2)
- CTE: member_id(1006), manager_id(1005), lvl(2)
- 1번째
꾸준하게