집합연산

1. SQL과 집합

집합을 설명할 때는 벤 다이어그램을 이용하기도 하는데 이 벤 다이어그램에서의 하나의 원이 곧 하나의 집합이다. 원 안에는 몇 가지 요소가 포함되는데 데이터베이스에서는 테이블의 행이 요소에 해당된다. 행은 여러 개의 열로 구성되는 경우도 있으므로, 수치 상으로는 복수의 값이 존재한다. 하지만 집합의 요소라는 측면에서 보면 하나의 행이 곧 하나의 요소가 된다.

SELECT 명령을 실행하면 데이터베이스에 질의하여 그 결과 몇 개의 행이 반환되는데 이때 반환된 결과 전체를 하나의 집합이라고 생각하면 된다.

2. UNION으로 합집합 구하기

UNION

합집합을 계산할 경우에는 수학에서 사용하던 𝖴기호 대신 UNION 키워드를 사용한다. 즉, 수학에서의 ‘A 𝖴 B’ 는 SQL에서는 A UNION B라고 표현된다.

정리하자면 한 번의 쿼리 실행으로 두 개의 SELECT명령이 내부적으로 실행되는 형식이다. 이때 각 SELECT 명령의 실행결과(집합)를 합집합(UNION)으로 계산하여 최종적으로 결과를 반환한다.

UNION으로 두 개의 SELECT 명령을 하나로 묶을 수 있다. 이때 각각의 SELECT 명령의 열의 내용은 서로 일치해야 한다. 완전히 열 구성이 다른 테이블을 UNION으로 묶을 수는 없다.

다만 전체 데이터를 반환하는 애스터리스크(*)를 쓰지 않고, 열을 따로 지정하여 각 SELECT 명령에서 집합의 요소가 될 데이터를 서로 맞춰주면 UNION으로 실행할 수 있는 쿼리가 된다.

SELECT 명령들을 UNION으로 묶을 때 나열 순서는 합집합의 결과에 영향을 주지 않는다. 단, 결과값의 나열 순서는 달라질 수도 있다. ORDER BY를 지정하지 않은 SELECT 명령은 결과가 내부처리의 상황에 따라 바뀌기 때문이다.

UNION을 사용할 때의 ORDER BY

UNION으로 SELECT명령을 결합해 합집합을 구하는 경우, 각 SELECT 명령에 ORDER BY를 지정해 정렬할 수는 없다. ORDER BY를 지정할 때는 마지막 SELECT 명령에만 지정하도록 한다.

위와 같이 첫 번째 SELECT명령에 ORDER BY를 지정할 수 없다.

하지만 위와 같이 마지막 SELECT 명령에 ORDER BY를 지정할 수 있다고 해도 에러가 발생하는데 이는 SELECT 명령의 결과만 정렬하는 것이 아니고 합집합의 결과를 정렬하는 것이기 때문이다. 이때 두 개의 SELECT 명령에서 열 이름이 서로 일치한다면 문제가 없겠지만 일치하지 않는다면 서로 동일하게 별명을 붙여 정렬할 수 있다.

UNION ALL

UNION은 두 명령의 실행 결과에 DISTINCT를 걸어 중복을 제거한 것과 같다고 생각하면 이해하기 쉽다. 수학에서 말하는 집합은 중복값이 존재하지 않는 것을 전제로 하기 때문이다.

하지만 경우에 따라서는 중복을 제거하지 않고 2개의 SELECT명령의 결과를 그냥 합치고 싶은 때도 있을 것이다. 이러한 경우에는 UNION ALL을 사용한다. 이때 UNION은 기본 동작이 DISTINCT고 모든 결과를 얻고 싶을때는 ALL을 추가적으로 지정한다. 다만, UNION DISTINCT라는 문법은 허용되지 않으므로 주의가 필요하다.

UNION에서는 이미 존재하는 값인지를 검사하는 처리가 필요한 만큼, UNION ALL쪽이 성능적으로는 유리할 경우가 있다. 즉, 중복값이 없는 경우에는 UNION ALL을 사용하는 편이 좋은 성능을 보여준다.

3. 교집합과 차집합

MySQL에서는 지원되지 않지만 SQL을 이용하여 교집합, 차집합을 구할 수 있다.

• 교집합 : INTERSECT
• 차집합 : EXCEPT (Oracle의 경우는 MINUS)

두 개의 집합에 공통부분이 존재하지 않으면 차집합을 구해도 결과는 바뀌지 않는다. 또한 완전히 같은 집합끼리 차집합을 계산하면 아무런 요소도 존재하지 않는 공집합이 된다.

테이블 결합

1. 곱집합과 교차결합

곱집합은 합집합이나 교집합처럼 집합의 연산 방법 중 하나이다. 두 개의 집합을 곱하는 연산 방법으로 ‘적집합’ 또는 ‘카티전곱 (Cartesian product)‘이라 한다.

교차결합(Cross Join)

SELECT * FROM 테이블명1, 테이블명2

SELECT 명령에서는 FROM 구에 하나의 테이블만 지정했다. 만약 테이블을 두 개 지정하면 이들은 곱집합으로 계산된다. 이때 FROM 구에서는 ,로 구분하여 두 테이블을 지정하며 지정된 복수의 테이블은 교차결합을 한다.

UNION연결과 결합연결의 차이

UNION으로 합집합을 구했을 경우에는 세로 방향으로 더해지게된다. 한편 FROM구로 테이블을 결합할 경우에는 가로 방향으로 더해지게 된다.

⚠️ UNION

⚠️ 결합연결

2. 내부결합

결합 방법으로는 교차결합보다 내부결합이 자주 사용된다. 수학에서의 집합은 유일한 요소로 구성된다. 즉, 중복된 값이 존재하지 않는다는 뜻이다. 마찬가지로 관계형 데이터베이스에서도 테이블의 데이터가 유일한 값을 가지도록 권장한다. 간단히 말하면 기본키(Primary key, PK)를 가지도록 하는게 좋다는 것이다.

데이터베이스에는 다양한 데이터가 저장됩니다만 동일한 데이터를 중복해서 여러곳에 저장하지 않도록 하는편이 좋다. 만약 데이터가 변경되는 경우 여기저기 저장되어 있는 데이터를 모두 동일한 값으로 변경하기란 힘든 일이다. 이때 기본키는 하나의 데이터행을 대표할 수 있는 속성을 가진다.

예를 들어 상품 테이블을 작성 한다고 가정하고 이때 상품의 속성으로는 상품명, 메이커명, 가격, 상품분류 등을 꼽을 수 있다. 그 중에서 상품명을 기본키로 사용한다면 값이 중복할 우려가 있으므로 기본키로는 적합하지 않다. 이러한 이유로 상품코드를 기본키로 사용하는 경우가 많다.

전자상거래 시스템에서 상품 테이블 하나만으로 운영하는 곳은 없다고 가정해도 무방하다. 즉, 상품 데이터를 참조하는 별도의 테이블이 존재한다는 이야기이다 가령 재고도 같이 관리하는 경우에는 입출고나 재고 수를 상품단위로 관리하는 테이블이 있을것이다.

여기서는 재고 테이블을 간단하게 만들기 위해 기본키를 따로 지정하지 않았지만 재고수 테이블을 참조하는 다른테이블이 있다면 기본키를 지정하는 것이 좋다.

재고수 테이블에서는 상품코드를 통해 상품 테이블과 연결할 수 있다. 상품 테이블의 기본키는 ‘상품코드’이다. 이열의 값을 알면 상품명을 포함한 상품 데이터를 참조할 수 있다. 요컨대 다른 테이블의 데이터를 참조해야 하는 경우, 참조할 테이블의 기본키와 동일한 이름과 자료형으로 열을 만들어서 행을 연결하는 경우가 많다.

이때, 상품테이블과 재고테이블을 교차결합 한다면 다음과 같을 것이다.

이렇게 만들어진 집합에서 상품분류가 식료품인 상품의 재고수를 얻고 싶다고 가정해보자. 먼저 원하는 데이터를 검색하기 위해 WHERE구로 조건을 지정한다. 다만, 상품코드가 같다는 조건이 필요하다. 위 예제에서 상품테이블의 상품코드와 재고테이블의 상품코드가 서로 같은것을 찾아나가면 다음과 같다.

열 이름이 ‘상품코드’로 서로 동일하므로 WHERE구에 조건식을 지정할 때 테이블 이름도 같이 지정할 필요가 있다.

이렇게 교차결합으로 계산된 곱집합에서 원하는 조합을 검색하는 것을 ‘내부결합 (Inner Join)‘이라 부른다. (결합 조건으로 보면 등결합 이라고도 부를 수 있다.)

다음으로는 상품분류가 ‘식료품’이라는 조건이 필요하다. 이 조건을 WHERE구에 추가해보자. 추가할 때는 기존 조건식과 상품분류의 조건식이 모두 참이어야 하므로 AND로 조건식을 연결한다. 또한 상품명과 재고수만 반환하도록 SELECT구에 열을 지정한다.

WHERE구에는 두 개의 조건식이 지정되어 있다. 첫 번째 조건식은 교차결합으로 계산된 곱집합에서 원하는 조합을 검색하는 것이다. 두 번째 조건식은 결합 조건이 아닌 검색 조건이다. 여기에서 첫 번째 조건식의 조건을 ‘결합조건’이라 부른다.

3. INNER JOIN으로 내부결합하기

SELECT * FROM 테이블명1 INNER JOIN 테이블명2 ON 결합조건

지금까지 소개한 결합방법에 관해 다음과 같이 간단히 정리 할 수 있다.

• FROM 구에 테이블을 복수 지정해 가로 방향으로 테이블을 결합할 수 있다.
• 교차결합을 하면 곱집합으로 계산된다.
• WHERE조건을 지정해 곱집합에서 필요한 조합만 검색할 수 있다.

사실 지금까지 소개된 결합방법은 구식이다. 최근에는 INNER JOIN 키워드를 사용한 결합방법이 일번적으로 통용된다. 그럼 이제 앞선 예제를 INNER JOIN을 활용하여 바꿔보겠다.

구식 방법에서는 ,로 구분하여 테이블을 FROM구에 지정하였다. 새로운 형식에서는 테이블과 테이블 사이에 INNER JOIN이라는 키워드를 넣는다. 또한 구식 방법에서는 WHERE구에 결합조건을 지정하였지만 INNER JOIN에서는 ON을 사용하여 결합조건을 지정한다.

4. 내부결합을 활용한 데이터관리

‘하나의 데이터는 한 군데에 저장한다’라는 룰에 따라 데이터 구조를 설계한다고 했을 때, 메이커코드와 메이커명을 가지는 메이커 테이블을 작성해 데이터를 관리해보겠다.

이에 따라 상품 테이블은 다음과 같다.

현재 두 개의 테이블로 분할되어 있지만 상품명과 메이커명을 같이 출력하고 싶을 때는 내부결합을 사용한다.

테이블 명을 매번 지정하는 것은 번거로운 일이므로 짧게 줄여 별명을 붙이는 경우가 많다. 위 예제에서도 메이커 테이블은 ‘M’, 상품 테이블은 ‘S’라는 짧은 별명을 붙였다.

상품 테이블의 메이커코드만을 살펴보면 중복하는 행이 있다. 이것은 실제로도 있을 수 있는 ‘상품1과 상품2는 같은 메이커1이 제조한 상품’인 경우에 해당된다.

이 부분은 결합이나 데이터베이스의 테이블 설계를 이해하는 동시에 핵심이 되는 부분이다. A테이블과 B테이블을 결합했을 때, A와 B중 어느 쪽이 하나의 행만 가지는지 (1:M, M:1)아니면 양쪽 모두 하나의 행을 가지는지 (1:1)등과 같은 ‘서로 결합하는 테이블 간의 관계’가 중요하다.

외부키

위 예제에서 메이커 테이블의 메이커코드는 기본키이다. 그에 비해 상품 테이블의 메이커코드는 ‘외부키’라 불리는 것으로 다른 테이블의 기본키를 참조하는 열이 외부키가 된다.

자기결합(Self Join)

자기 결합은 테이블에 별명을 붙일 수 있는 기능을 이용해 같은 테이블끼리 결합 하는 것을 말한다.

자기결합에서는 결합의 좌우가 같은 테이블이 되기 때문에 이를 구별하기 위해서 반드시 별명을 붙여야 한다. 사실 평소에는 이러한 결합을 할 일은 없으나 자기 자신의 기본키를 참조하는 열을 자기 자신이 가지는 데이터 구조로 되어 있을 경우에 자주 사용된다.

5. 외부결합

‘어느 한 쪽에만 존재하는 데이터행을 어떻게 다룰지’를 변경할 수 있는 결합방법이다. 앞선 예제에서 상품 테이블과 재고수 테이블 중에 상품 테이블에만 데이터가 존재하는 상황을 생각해 보자. 실제로 상품 데이터를 등록한 직후에는 이러한 상황이 존재 할 수 있다.

이런 상태에서 곱집합을 구해도 상품코드가 0009 = 0009가 되는 행은 존재하지 않으므로 내부결합 결과에서는 상품코드가 0009인 상품이 제외된다.

이런 경우에 외부 결합을 사용하면 도니다. 외부 결합은 결합하는 테이블 중에 어느 쪽을 기준으로 할지 결정할 수 있다. 이번에는 상품 테이블 (결합의 왼쪽)을 기준으로 INNER JOIN 대신 LEFT JOIN을 사용한다.

재고수 테이블에는 0009에 대한 데이터가 없으므로 값이 NULL로 표시되는 점을 주목한다. 만약 상품 테이블을 오른쪽에 지정하는 경우나 재고 테이블을 기준으로 삼고 싶을 경우에는 RIGHT JOIN을 사용해 외부 결합을 시행한다.

구식방법에서의 외부결합과 표준 SQL

MySQL은 비교적 최근에 나온 데이터베이스이다. 따라서 구식방법을 이용해도 내부결합은 가능하지만 외부결합은 불가능하다. 다만 Oracle과 SQL Server에서는 구식방법으로 외부 결합이 가능하다.

# Oracle
SELECT 상품3.상품명, 재고수.재고수
	FROM 상품3, 재고수
	WHERE 상품3.상품코드 = 재고수.상품코드(+)
		AND 상품3.상품분류 = '식료품';

하지만 현재는 표준화로 인해 내부결합은 INNER JOIN, 외부결합은 LEFT JOIN이나 RIGHT JOIN을 사용하도록 권장하며 현재는 별다른 장점이 없는 구식 결합방법은 사용하지 않는다.

관계형 모델

1. 관계형 모델

관계형 모델(Relational Model)의 기본적인 요소는 릴레이션(Relation)이다. 관계형 모델의 릴레이션은 SQL에서 말하는 테이블에 해당되며 데이블끼리의 관계가 아니다.

관계형 모델의 릴레이션에는 몇 가지 ‘속성(attribute)‘이 있다. 이 속성은 속성 이름과 형 이름으로 구성된다. 속성은 SQL에서 말하는 열에 해당된다. SQL에서의 행은 관계형 모델에서 ‘튜플(tuple)‘이라 불린다.

관계형 모델은 데이터 구조에 관해 정의한다. 릴레이션은 튜플의 집합이며, 릴레이션에 대한 연산이 집합의 대한 연산에 대응된다는 이론을 ‘관계대수’라고 한다. 이 같은 관계대수의 기본규칙은 다음과 같다.

• 하나 이상의 관계를 바탕으로 연산한다.
• 연산한 결과, 반환되는 것 또한 관계이다. (릴레이션 = 테이블)
• 연산을 중첩 구조로 실행해도 상관없다.

2. 관계형 모델과 SQL

합집합

합집합 (Union)은 릴레이션끼리의 덧셈을 말한다. SQL에서는 UNION이 해당

차집합

차집합(Difference)은 릴레이션끼리의 뺄셈을 말한다. SQL에서는 EXCEPT가 해당

교집합

교집합(Intersection)은 릴레이션끼리의 공통부분(교집합)을 말한다. SQL에서는 INTERSECT가 해당

곱집합

곱집합(Cartesian product)은 릴레이션끼리의 대전표를 조합하는 연산을 말한다. SQL에서는 FROM구에 복수의 테이블을 지정한 경우 곱집합으로 계산된다. 또한 CROSS JOIN으로 교차결합을 하면 곱집합을 구할 수 있다.

선택

선택(Selection)은 튜플의 추출을 말한다. 선택은 제한이라 불리기도 한다. 튜플은 SQL에서 행을 말하기 때문에 WHERE구에 조건을 지정해 데이터를 검색하는 것에 해당된다.

투영

투영(Projection)은 속성의 추출을 말한다. SQL에서 속성은 열을 말하기 때문에 SELECT구에 결과로 반환된 열을 지정하는 것에 해당된다.

결합

결합(Join)은 릴레이션끼리 교차결합해 계산된 곱집합에서 결합조건을 만족하는 튜플을 추출하는 연산이다. SQL에서는 내부결합에 해당된다. 관계대수에도 내부결합과 외부결합이 있다.

Question

1. 테이블을 결합할 때 결합조건을 지정하지 않고 실행하면 구할 수 있는 집합

• 곱집합

2. 내부결합 키워드

• INNER JOIN

3. 관계형 모델에서 릴레이션에 해당하는 SQL 용어

• 테이블