이번에는 두개의 테이블을 조인(Join)하는 방법에 대해서 알아 보겠다. 실제 관계형 데이터베이스에서 제일 많이 쓰이는 기법이 바로 이 조인이다. 일단 조인 예제를 실습하기 위해 두개의 테이블을 만들고 그 값을 넣기위해 다음의 쿼리를 줘보도록 하자.
예제 4-1. 예제를 위한 테이블생성 쿼리
CREATE TABLE product ( prod_num SERIAL PRIMARY KEY, prod_name VARCHAR(20) NOT NULL, prod_cost INT ); CREATE TABLE sale ( sale_num SERIAL PRIMARY KEY, prod_num INT NOT NULL, sale_amount INT NOT NULL, sale_time DATETIME DEFAULT now() );
위와 같이 설계를 했다고 하자.
여기서 못보던 SERIAL이라는 타입이 나왔다. SERIAL타입은 따로 지정하지 않으면 레코드를 입력할 때마다 1씩 증가하여 넣어주는 타입이다.
각각의 테이블을 설명하면 product는 제품을 관리하는 테이블로써 제품번호, 제품명, 제품단가를 각각 필드로 가지고 있다. sell테이블은 판매번호, 제품번호, 판매수량, 판매시간을 각 필드로 가지고 있다. 이제 데이터를 입력해 보도록 하자.
예제 4-2. 데이터 입력을 위한 쿼리
INSERT INTO product (prod_name, prod_cost) VALUES ('초코파이', '200');
INSERT INTO product (prod_name, prod_cost) VALUES ('신라면', '350');
INSERT INTO product (prod_name, prod_cost) VALUES ('진라면', '300');
INSERT INTO product (prod_name, prod_cost) VALUES ('초코바', '500');
INSERT INTO product (prod_name, prod_cost) VALUES ('핑클빵', '500');
INSERT INTO sale (prod_num, sale_amount, sale_time) VALUES(2, 3, '2000-04-12');
INSERT INTO sale (prod_num, sale_amount, sale_time) VALUES(1, 5, '2000-04-12');
INSERT INTO sale (prod_num, sale_amount, sale_time) VALUES(5, 2, '2000-04-12');
INSERT INTO sale (prod_num, sale_amount, sale_time) VALUES(2, 3, '2000-04-12');
INSERT INTO sale (prod_num, sale_amount, sale_time) VALUES(2, 3, '2000-04-12');
INSERT INTO sale (prod_num, sale_amount, sale_time) VALUES(1, 10, '2000-04-13');
INSERT INTO sale (prod_num, sale_amount, sale_time) VALUES(2, 2, '2000-04-13');
INSERT INTO sale (prod_num, sale_amount, sale_time) VALUES(4, 3, '2000-04-13');
이제 위와 같이 구성한 데이터베이스에서 다음과 같은 쿼리를 주어보도록 하자.
예제 4-3. 조인(join)을 위한 쿼리 예제
SELECT sale.sale_num, product.prod_name, product.prod_cost, sale.sale_amount, product.prod_cost * sale.sale_amount AS sale_cost, date(sale.sale_time) AS sale_date FROM product, sale WHERE product.prod_num = sale.prod_num ORDER BY sale.sale_num;
위의 쿼리는 product테이블과 sale테이블 두개의 테이블간의 관계를 따져서 서로 조인한 결과이다. product테이블에서 제품명과 제품가격을 가져오고 sale테이블에서 판매수량과 판매날짜를 가져와서 서로 곱한 결과를 나타낸 것이다. sale테이블에서 각 레코드는 판매한 제품의 제품번호를 가지고 있기 때문에 그 제품번호만 참조하면 제품명과 제품가격을 알 수 있다. 이러한 키를 원래 FOREIGN KEY라고 한다. 이것을 테이블에 Primary key처럼 제약조건으로 걸어줄 수도 있지만 PorstgreSQL 6.X버젼대에서는 FK를 지원하지 않는다. (트리거로 이것이 구현가능하기는 하다.) 다만 위와 같이 관계를 표현함으로써 서로 조인은 할 수 있다.
위와 같이 테이블을 서로 조인하지 않고 하나의 테이블로 설계할 수도 있다. 제품가격, 제품명, 판매수량, 판매일자등을 하나의 테이블로 구성할 수도 있지만 그렇게 테이블을 구성했을 경우 제품명과 제품가격의 경우 데이터의 중복이 각 레코드에서 일어나게 된다. 이는 낭비이며 데이터를 효율적으로 관리하기 힘들어진다. 그래서 테이블을 분리한 후 필요할때만 서로 조인하게 되는 것이다. 데이터베이스 모델링이론에서 반복되는 속성은 따로 테이블로 빼는 것이 정규화의 방법중에 하나이다.
하지만 매번 위와 같은 결과를 출력하기 위해서 저 기나긴 조인 문장을 타이핑하는 것은 일종의 낭비이다. 그래서 만든 것이 바로 VIEW라는 것이다. 다음과 같은 쿼리를 줘보도록 하자. 아까의 쿼리에 앞부분에 CREATE VIEW product_sale AS가 더 붙고, ORDER BY가 빠졌다. (VIEW에서의 ORDER BY는 아직 구현이 되지 않았다.)
예제 4-4. VIEW생성 예제
CREATE VIEW product_sale AS SELECT sale.sale_num, product.prod_name, product.prod_cost, sale.sale_amount, product.prod_cost * sale.sale_amount AS sale_cost, date(sale.sale_time) AS sale_date FROM product, sale WHERE product.prod_num = sale.prod_num;
이렇게 만들어진 뷰는 \d명령어를 쳐보면 view로 나타나게 된다. 이를 어떻게 활용할 지 살펴 보도록 하겠다.
우선 뷰는 SELECT시 테이블과 동일하게 사용할 수 있다. 다만, INSERT나 DELETE가 동작한다고 하지만 시도하지 않는 것이 좋다. (어떠한 결과가 나타나게 될지 모른다.) 다음과 같은 쿼리를 줘 보도록 하자.
예제 4-5. 생성한 View에서 데이터를 추출하는 쿼리
SELECT * FROM product_sale ORDER BY sale_num;
아까와 동일한 결과를 아주 손쉽게 얻어낼 수 있었다. 이렇게 만들어진 뷰는 물리적인 저장공간을 사용하지 않는다. 각 테이블을 참조해서 그 결과를 조인해서 사용하게 된다. 단, 일반적으로 매번 조인 문장을 사용하는 것보다는 뷰를 사용하는게 성능 측면에서는 효과적이다. 그 이유는 버퍼의 사용여부와 SQL문장 파싱에 있다. 매번 조인 문장을 사용한다면 조인 문장을 사용할때 마다 SQL서버 내부적으로 문장을 파싱한 후에 수행하는데 그 때문에 한번 수행했던 결과가 버퍼에 저장되지 않는다. 그러나 뷰로 만들었을 경우는 파싱을 수행하지 않고 게다가 한번 수행한 결과는 계속 버퍼상에 존재하므로 그만큼 효율적인 결과를 얻어낼 수 있다.