서브 쿼리
이번에는 서브 쿼리에 대해 알아보도록 하겠습니다. 서브 쿼리는 하나의 쿼리 내에 또 다른 쿼리가 존재하는 형태를 띄는 쿼리로서 앞서 살펴본 SELECT, INSERT, UPDATE, DELETE 등 모든 쿼리에서 사용될 수 있습니다.
예를 들어 다음의 코드를 살펴보겠습니다.
SELECT title, type, price
FROM titles
WHERE title_id IN
(
SELECT title_id
FROM titleauthor
WHERE au_id = ‘998-72-3567’
)
이 쿼리는 도대체 무슨 의미를 가질까요? 일단 처음 나타나는 SELECT 구문부터 살펴보겠습니다. 이 구문은 titles 테이블에서 title, type, price 필드의 데이터를 가져오는 쿼리입니다. titles 테이블은 개별 서적에 대한 데이터를 가지고 있는 테이블이며 서적의 제목과 가격은 물론 저자에 대한 정보도 저장하고 있습니다.
이 구문은 WHERE 절을 통해 title_id 필드의 값을 비교하는데 이 title_id 필드 내에서 검색될 값을 괄호 안에 작성된 두 번째 쿼리, 즉 서브 쿼리의 결과로부터 가져오게 되는 것입니다.
서브 쿼리는 titleauthor 테이블에서 title_id 필드의 데이터를 가져오는데 이 때 au_id 필드가 998-72-3567인 데이터만 가져옵니다. 즉, 저자 ID가 998-72-3567인 title_id 필드 값만 가져오는 것이지요.
결과적으로 이 쿼리는 998-723567이라는 ID를 갖는 저자가 집필한 책의 ID들을 title_author라는 테이블에서 가져와 이 책의 ID들을 이용해서 titles 테이블로부터 각 서적들의 정보를 가져오는 쿼리입니다.
서브 쿼리의 특징은 서브 쿼리로 작성된 쿼리는 개별적으로도 실행이 되지만 서브 쿼리를 포함하는 쿼리는 개별 실행이 되지 않으며 서브 쿼리의 실행 결과에 따라 다른 실행 결과를 보인다는 것입니다.
테이블 조인
테이블 조인 (Join)이란 두 개 이상의 테이블에 존재하는 데이터를 하나의 가상 테이블로 가져와 통합하는 방법을 말합니다. 테이블 조인은 크게 두 가지로 나뉘는데 내부 조인 (Inner Join)과 외부 조인 (Outer Join)이 바로 그것이며 외부 조인의 경우 왼쪽 외부 조인 (Left Outer Join)과 오른쪽 외부 조인 (Right Outer Join)으로 구분 됩니다.
내부 조인
내부 조인은 두 개의 테이블에 존재하는 레코드 중 일정 조건을 만족하는 레코드들을 병합하여 하나의 테이블처럼 취급하기 위한 구문으로 쉽게 말하면 두 테이블 사이의 교집합을 구하는 것과 비슷합니다. 내부 조인의 구문은 다음과 같습니다.
SELECT 필드 목록
FROM 조인에 사용할 테이블 목록
WHERE 두 테이블 사이의 조인 조건
자, 지금까지의 설명만으로는 언뜻 이해가 쉽지 않을 것입니다. 예제 구문을 보면서 다시 한 번 이야기해보도록 하지요. 다음 쿼리는 titles 테이블과 publishers 테이블을 대상으로 내부 조인을 통해 개별 서적에 대한 정보와 해당 서적을 출판한 출판사의 정보를 함께 출력하는 예제입니다.
SELECT t.title, t.price, p.pub_name, p.city, p.state
FROM titles AS t, publishers AS p
WHERE t.pub_id = p.pub_id;
이제 위의 쿼리를 하나씩 살펴보면서 그 의미를 해석해 보겠습니다. 먼저 FROM 절을 살펴보면 titles 테이블과 publishers 테이블이 콤마를 이용하여 나열되어 있습니다. 그런데 AS라는 못 보던 녀석이 등장했네요. 이 녀석은 테이블이나 필드에 대해 별칭을 지정하기 위한 키워드 입니다.
즉, titles 테이블은 이 쿼리 안에서 앞으로 t라는 이름으로 참조하겠다 라는 의미를 갖게 되는 것이지요. 그런 이유로 SELECT 절과 WHERE 절에서는 모두 t와 p라는 별칭이 사용되었음을 눈 여겨 보시기 바랍니다.
다음으로 SELECT 절을 살펴보겠습니다. SELECT 절에는 t라는 별칭으로 참조하는 titles 테이블에서 title과 price 두 필드 데이터를 가져옵니다. 또한 p라는 별칭으로 참조하는 publishers 테이블에서는 pub_name, city, state 등 세 필드의 데이터를 가져옵니다.
이와 같이 조인을 이용하면 두 테이블에 흩어져있는 데이터를 마치 하나의 테이블에 있는 것처럼 참조할 수 있게 됩니다.
마지막으로 가장 중요한 WHERE 절입니다. 여기에는 조인에 대한 조건이 설정되는데 예제에서 사용한 조건은 titles 테이블의 pub_id 필드 데이터와 publishers 테이블의 pub_id 필드의 데이터가 동일한 레코드만을 검색하도록 하고 있습니다.
내부 조인을 통해 조인된 가상 테이블이 리턴 하는 레코드의 개수는 WHERE 절에 지정된 조건을 만족하는 레코드의 개수입니다. 예를 들어 아래 그림을 통해 pub_id가 0736인 레코드의 경우를 생각해 보겠습니다.
이 그림을 보면 pub_id 필드의 데이터가 0736인 레코드는 titles 테이블에는 총 5개의 레코드가 존재하며 publishers 테이블에는 하나의 레코드가 존재합니다. WHERE 절에 지정된 조건은 두 테이블의 pub_id 필드 데이터가 동일한 레코드를 대상으로 하고 있지요.
먼저 publishers 테이블에서 pub_id 필드가 0736인 레코드와 그림에 나타난 titles 테이블의 pub_id가 모두 동일합니다. 따라서 결과로 리턴 되는 레코드의 개수는 총 5개가 되겠지요.
지금까지 살펴본 내부 조인 구문은 SQL Server의 T-SQL 문장입니다. 그런데 SQL Server의 온라인 설명서를 보면 마이크로소프트는 내부 조인에 대해 T-SQL 문장보다는 표준 SQL 문장의 사용을 권장하고 있습니다. 그렇다면 표준 SQL 구문에 의한 내부 조인은 어떤 식으로 작성할 수 있을까요?
SELECT t.title, t.price, p.pub_name, p.city, p.state
FROM titles AS t INNER JOIN publishers AS p
ON t.pub_id = p.pub_id
자, 앞서 살펴본 T-SQL 내부 조인 문장과 비교해서 바뀐 것은 많지 않습니다. 일단 FROM 절에서 콤마 대신 INNER JOIN이라는 키워드가 사용되었으며 WHERE 절 대신 ON이라는 키워드가 사용된 것 뿐 입니다. 물론 WHERE 절을 추가하여 검색 조건 역시 지정할 수 있습니다.
외부 조인
다음으로 외부 조인에 대해 알아보도록 하겠습니다. 외부 조인은 내부 조인과 동작하는 방식이 조금 다릅니다. 결론적으로 이야기 하자면 내부 조인이 두 테이블 사이의 교집합을 구하는 것이라면 외부 조인은 두 테이블 중 하나의 테이블을 기준으로 하여 두 테이블 사이의 합집합을 구하는 것입니다. 여기서 기준이 되는 테이블이 FROM 절의 좌측에 위치하는지 우측에 위치하는지에 따라 왼쪽 외부 조인과 오른쪽 외부 조인으로 나뉘는 것입니다. 일단 구문 형식부터 살펴보면 다음과 같습니다.
SELECT 가져올 필드 목록
FROM 테이블1 LEFT | RIGHT OUTER JOIN 테이블2
ON 조인 조건
이상과 같습니다. 그러면 방금 전 내부 조인에 사용했던 구문을 외부 조인으로 변경해 보도록 하겠습니다. 다음 쿼리를 쿼리 분석기에서 실행해 보시기 바랍니다.
SELECT t.title, t.price, p.pub_name, p.city, p.state
FROM titles AS t LEFT OUTER JOIN publishers AS p
ON t.pub_id = p.pub_id
이 쿼리를 실행하면 titles 테이블을 기준으로 외부 조인을 실행하게 됩니다. 외부 조인을 실행하면 기준 테이블의 레코드는 무조건 모두 출력됩니다. 이 때 ON 절의 조건에 따라 다른 테이블에 존재하는 데이터를 검색하여 출력하되, 조건에 일치하는 레코드가 없을 경우에는 NULL 값이 채워집니다.
현재 왼쪽 외부 조인의 결과는 내부 조인의 결과와 전혀 다르지 않습니다. 왜냐하면 기준이 되는 titles 테이블의 레코드 개수가 우측에 있는 publishers 테이블의 전체 레코드 개수보다 많으며 좌측 테이블을 기준으로 하기 때문에 좌측 테이블의 pub_id 필드에 나타나지 않은 데이터가 publishers 테이블에 존재한다 해도 무시되기 때문입니다.
즉 titles 테이블의 pub_id 필드 데이터를 기준으로 publishers 테이블에 있는 pub_id 필드 데이터를 비교하기 때문에 publishers 테이블에 존재하는 출판사의 ID라 해도 titles 테이블에 없으면 해당 출판사 ID는 나타나지 않습니다.
결국 이 쿼리는 각 서적을 출간한 출판사의 정보를 서적의 정보와 함께 출력하는 것이지요. 그러면 이 코드를 LEFT OUTER JOIN에서 RIGHT OUTER JOIN으로 바꾸어볼까요?
SELECT t.title, t.price, p.pub_name, p.city, p.state
FROM titles AS t RIGHT OUTER JOIN publishers AS p
ON t.pub_id = p.pub_id;
이 쿼리의 실행결과는 왼쪽 외부 조인과 다릅니다. 두 테이블에 독자 여러분이 어떠한 데이터도 추가하지 않았다면 총 23개의 데이터가 출력될 것입니다.
특히 19번 레코드부터 23번 레코드는 서적과 관련된 내용이 모두 NULL 값으로 채워져 나타날 것입니다. 이러한 결과가 나타나는 이유는 titles 테이블에서는 나타나지 않는 출판사 ID가 publishers 테이블에 존재하기 때문입니다.
우측 외부 조인에 의해 기준 테이블은 publishers 테이블이 되며 따라서 이 쿼리는 publishers 테이블의 pub_id 필드 데이터를 기준으로 titles 테이블의 pub_id 필드 데이터를 비교합니다.
따라서 publishers 테이블에 존재하는 출판사 ID는 모두 출력되는데 이 때 몇몇 출판사 ID는 titles 테이블에 나타나지 않기 때문에 서적에 대한 정보가 모두 NULL로 출력되는 것입니다.
