컴알못의 SQL 공부 (2)

저번 글에 이어서 다시 글을 이어가겠습니다.

저번에는 SELECT라는 DML에 대해서 알아보고 있었습니다.

 

집합 연산자

SELECT는 테이블을 인자로 받아 테이블을 반환하는 함수입니다.

그렇기 때문에 결과는 항상 테이블이 나올 것입니다.

그렇다면 SELECT 함수들로 반환받은 테이블들을 집합 연산자로 다루는 것도 가능할 겁니다.

예를 들어서, 두개의 SELECT 함수로 반환한 테이블들을 UNION으로 합집합 연산을 한다거나?

SELECT 이름
FROM 학생
WHERE 학년 = 3
UNION
SELECT 이름
FROM 교사
WHERE 담당학년 = 3

이런 식으로 말이죠.

관계 대수에는 수학적 집합 개념을 적용한 집합 연산과 관계형 데이터베이스를 위한 관계 연산으로 나뉜다고는 알고 계실 겁니다.

집합 연산자는 두 개의 테이블이 있고 속성의 의미와 그 순서 그리고 수가 일치한다면 얼마든지 사용가능합니다.

UNION 외에도 INTERSECTION, MINUS 같은 녀석들 말이에요.

그리고 같은 집합 연산자지만 수평적 결합을 지원하는 카티션 프로덕트는 두개의 테이블만 있다면 굳이 속성과 수가 일치하지 않아도 사용가능합니다.

 

부질의문

부질의문에 대해서도 알아봅시다.

SQL을 질의문이라고 부른다고 하는 건 알고 계실겁니다.

부질의문은 질의문 안에 포함된 다른 질의문입니다.

즉, SELECT 검색문 안에 또 다른 SELECT 검색문을 포함할 수 있다는 의미가 됩니다.

SELECT 이름
FROM 학생
WHERE 학번 IN ( SELECT 학번
		FROM 수강
                WHERE 과목명 = '수학' )

이런 식으로 쓰입니다.

IN이라고 하는 건 괄호 안에 나열된 값들 중에 하나라도 일치하는게 있는 경우 TRUE를 반환하는 연산자입니다.

부질의문은 IN 연산자를 위해서 별도의 학번 테이블을 제공해주는 역할을 하는 거고요.

부질의문은 계속해서 중첩해서 사용가능합니다. 그걸 중첩 부질의문이라고 합니다.

 

부질의문은 결과를 하나만 반환하냐, 여러개를 반환하냐에 따라 단일값 부질의문과 다중값 부질의문으로 구분됩니다.

부질의문에 사용될 수 있는 비교 연산자는 아래에 정리해두었습니다.

=, <, > 등등과 같은 비교 연산자...	단일값 부질의문에만 사용 가능
=ALL	다중값 부질의문의 결과 전부의 비교 결과가 TRUE일 경우
=ANY 혹은 =SOME	다중값 부질의문의 결과 중 하나의 비교 결과만 TRUE면
IN	부질의문 결과 중 하나라도 포함되면
NOT IN	부질의문 결과 중 하나도 포함되지 않으면
EXISTS	부질의문 결과가 하나라도 존재하면
NOT EXSISTS	부질의문 결과가 하나라도 존재하지 않으면

부질의문의 종류는 스칼라 부질의문(SELECT절 위치), 인라인 뷰(FROM절 위치), 중첩 질의문(WHERE절 위치)이 있는데 흔히 부질의문을 말할때는 중첩 질의문을 의미하는 경우가 많습니다.

 

JOIN

검색을 위해 다른 테이블을 참조해야할 때는 위에서처럼 부질의문을 사용하거나,

JOIN을 사용해볼 수도 있습니다.

관계 대수를 공부할 때 JOIN은 카티션 프로덕트 + 셀렉트의 조합으로서, 테이블의 결합을 위해 쓰인다고 했었습니다.

이처럼 조인 검색은 둘 이상의 테이블로부터 연관된 행들의 결합을 통해서 검색 결과를 얻어옵니다.

조인 명세 방법에 대해서는 두가지가 있습니다.

SELECT (속성 리스트)
FROM (테이블 리스트)
WHERE (조인 조건식) AND (검색 조건식)

SELECT (속성 리스트)
FROM 테이블1 {[INNER] JOIN 테이블2 ON (조인 조건식)}
WHERE (검색 조건식)

둘 다 의미적으로는 같으며 다만 조인 조건식을 어디에 명시하냐의 차이입니다.

후자처럼 FROM절에 명세를 하는게 조인 조건식과 검색 조건식이 분리되어 보기에는 편할것 같습니다.

 

그리고 외래키의 개념을 기억하고 계셨다면 다른 테이블을 참조할 때 사용하는 녀석이라는 것도 기억하고 계실 겁니다.

조인으로 결합하기 위해서는 이 외래키를 사용해야 할겁니다.

 

CROSS JOIN은 카티션 프로덕트를 의미합니다.

모든 조합의 결과를 구하는 기계적인 결합입니다.

 

동등 조인은 조인 조건식에 "=" 비교 연산자를 사용한 조인을 의미합니다.

SELECT *
FROM 학생, 과목
SELECT 학생.학번 = 과목.학번

===============================

SELECT *
FROM 학생 JOIN 과목 ON 학생.학번 = 과목.학번

이런 조인 조건식을 동등 조인이라고 부릅니다.

카티션 프로덕트와는 달리 의미있는 행들의 조합이 생성되었죠.

 

셀프 조인은 하나의 테이블의 속하는 특정 행들과 행들을 결합하는 것을 의미합니다.

즉, 테이블 간의 결합은 아닙니다. 스스로 조인하는 거죠.

같은 테이블 안의 행들에 의미나 역할을 다르게 부여할 경우 다른 두개의 테이블이 있는 것처럼 다룰 수도 있습니다.

 

외부 조인이라는 건 내부 조인의 반대말입니다.

내부 조인이란 조인 조건에 해당하는 튜플만 결합합니다.

외부 조인은 그 반대로, 조인 조건에 해당하지 않는 튜플들도 수평적 결합을 시킵니다.

조인 조건에 해당하지 않으면 수평적 결합이 이루어지지 않아서 빈 데이터값이 나올 수 밖에 없는데 그 자리는 NULL로 메꾸는게 외부 조인입니다.

SELECT 학생, 학번, 이름, 평가학점
FROM 학생 LEFT OUTER JOIN 과목 ON 학생.학번 = 과목.학번

외부 조인은 왼쪽 외부 조인과 오른쪽 외부조인으로 나뉘는데, 이는 오른쪽을 NULL로 채울 것이냐 왼쪽을 NULL로 채울것이냐의 차이입니다. 풀 외부 조인은 조인 조건에 만족하지 않는 모든 튜플들을 포함함으로서 왼쪽에도 오른쪽에도 NULL값이 채워지도록 하는 연산을 말합니다.

 

INSERT문

드디어 SELECT 문에 벗어나서 INSERT 문입니다.

SELECT는 검색을 위해 쓰인다면 INSERT는 데이터의 추가를 위해 쓰입니다.

형식은 다음과 같습니다.

INSERT INTO 테이블 [속성 리스트] VALUES [값 리스트]

기본적으로 INTO~ VALUES~ 로 구성이 됩니다.

어차피 모든 속성에다가 값을 입력할거면 [속성 리스트] 부분은 생략해도 됩니다.

이럴 때는 모든 속성에다가 순서대로 입력할 값들을 [값 리스트]에 써주면 됩니다.

기본키 속성이거나 NULL값 금지가 아니라면 NULL 값을 넣어도 상관없습니다.

 

그리고 INSERT문에 부질의문을 활용하는것도 가능합니다.

이미 존재하는 테이블에 검색 결과로 반환받은 테이블을 추가를 하는 거죠.

INSERT INTO 학생(학번, 이름, 나이)
SELECT 학번, 이름, 나이
FROM 친구
WHERE 나이 = 18

문법은 이런 형태가 됩니다.

이런 활용이 있다는 것만 기억해 두시면 좋을 것 같습니다.

INSERT문 뿐만 아니라 CREATE문 역시 부질의문의 활용이 가능합니다.
이를 테이블의 복제라고 부릅니다.

CREATE TABLE 친구2 AS (SELECT * FROM 친구1)

이런식으로 친구1 테이블을 복사해서 친구2 테이블을 만들 수도 있습니다.
다만 이건 속성과 튜플의 복사일 뿐, 기본키나 제한 조건 같은 설정값은 복사되지 않았다는 사실에 유의해야합니다.

 

UPDATE문

UPDATE문은 수정의 기능입니다.

형식은 다음과 같아요.

UPDATE 테이블 SET (수정할 조건식) WHERE (목표 조건식)

이런 식으로, WHERE로 어느 부분을 수정할지를 명시한 다음에 SET으로 어떤 속성을 어떻게 수정할지를 명시하면 됩니다.

예제를 보여드리면 다음과 같겠군요.

UPDATE 친구
SET 친밀도 = 친밀도 + 1
WHERE 이름 = '강영수'

여러개의 속성을 변경시키고 싶다면 SET의 수정 조건식을 여러개 넣으시면 됩니다.

UPDATE문 역시 부질의문을 이용한 수정이 가능합니다.

WHERE 문이 존재하니까, 중첩 질의문처럼 사용이 가능하겠죠.

UPDATE 친구
SET 지역 = 서울
WHERE 이름 IN ( SELECT 이름 
               FROM 땡땡고등학교 )

그리고 WHERE 문이 아니더라도 SET절에도 충분히 사용가능할 겁니다.

 

DELETE문

DELETE문은 삭제를 위한 기능입니다.

DELETE FROM 테이블 WHERE (삭제 조건식)

DELETE는 SELECT와 형식이 매우 유사하죠.

삭제를 원하는 튜플만 특정하면 그만이기 때문에 검색과 기능이 유사합니다.

SELECT문처럼 WHERE절을 생략가능하지만, 그 경우 모든 튜플들이 삭제되니까 유의하셔야합니다.

 

가끔 DDL인 DROP문과 비교되는 경우가 있는데, DELETE문은 테이블의 값을 삭제하는 거고 속성같은 메타 데이터는 여전히 존재합니다만, DROP은 테이블 자체를 통째로 없애는 함수이기 때문에 실행 뒤 메타 데이터가 남질 않습니다.

이런 점에서 차이가 있기 때문에 둘은 아주 다른 함수입니다.