JOIN 종류와 사용(4)-서브쿼리를 사용한 복수 테이블 검색

서브쿼리(2)

상관 서브쿼리

서브쿼리는 상관관계가 있거나 없을 수 있다.

• 비사고나 서브쿼리는 외부 쿼리로부터의 값에 대한 참조가 없다. 비상관 서브쿼리는 구분 문장으로 실행될 수 있다.
  예를들면, 테이블 t1에 대해서는 참조를 하지만 t2는 그렇지 않기 때문에 다음 문장의 서브쿼리는 비상관관계에 있다.
  SELECT j FROM t2 WHERE j IN (SELECT i FROM t1);
• 상관 서브쿼리는 외부 쿼리로부터의 값에 대한 참조를 가지고 있으며, 독립적이다.
  이러한 관계 때문에, 상관 서브쿼리는 구분 문장의 형태로 실행될 수 없다.
  예를들면, 다음 문장의 서브쿼리는 t1의 칼럼 i값에 매칭되는 t2내 칼럼 j의 각 값들에 대해서 참이 된다.
  SELECT j FROM t2 WHERE (SELECT i FROM t1 WHERE i = j);

상관 서브쿼리들은 EXISTS와 NOT EXISTS 서브쿼리로서 사용되며,
어느 테이블의 행이 다른 테이블의 행에 매칭되거나 매칭되지 않는지를 찾는데 유용하다.

상관 서브쿼리들은 외부 쿼리로부터 특정 조건에 매칭되는지를 찾기 위한 서브쿼리로 값을 전달할 수 있다.
이러한 이유로, 만약 애매모호하다면(하나이상의 테이블엣 나타나므로), 테이블 이름으로 칼럼 이름을 지정하는 것이 필요하다.

SELECT STUDENT_ID, NAME
FROM STUDENT
WHERE EXISTS (
	SELECT *
	FROM ABSENCE
	WHERE ABSENCE.STUDENT_ID = STUDENT.STUDENT_ID);

/**************************************************/

SELECT STUDENT_ID, NAME
FROM STUDENT
WHERE NOT EXISTS (
	SELECT *
	FROM ABSENCE
	WHERE ABSENCE.STUDENT_ID = STUDENT.STUDENT_ID);

위의 EXISTS 서브쿼리는 테이블 간에 매칭 값들을 지정한다. 즉, 둘 다에 존재하는 값이다. (결석을 한번이상 한 모든 학생들을 검색)
아래의 NOT EXISTS는 다른 테이블에 존재하지 않는 어떤 테이블의 비매칭 값들을 지정한다. (개근한 모든 학생들을 검색)

FROM절의 서브쿼리

서브쿼리는 FROM절에서 값을 생성하기 위해서 사용된다. 이 경우, 서브쿼리의 결과는 테이블처럼 동작한다.
FROM절의 서브쿼리는 조인될 수 있으며, 그 값은 WHERE 절에서 테스트 될 수 있다.
이러한 형태의 서브쿼리가 사용될 때, 서브쿼리를 명명하기 위한 테이블 ALIAS를 제공한다.
SELECT * FROM (SELECT 1, 2) AS t1 INNER JOIN (SELECT 3, 4) AS t2;

조인으로 서브쿼리 재작성

조인을 이용하여 서브쿼리를 사용하는 쿼리를 재작성하는 것이 가능하다.
서브쿼리를 사용하는 쿼리를 이용한다는 것이 나쁜 생각은 아니다.
조인은 이따금 서브쿼리보다 더 효율적이다. 그래서 만약 서브쿼리에서 사용된 SELECT가 실행하는데 많은 시간을 소요한다면,
조인을 사용할 때 더 나은지의 여부를 확인해야 한다.

매칭 값을 검색하는 서브쿼리의 재작성

다음은 서브쿼리가 포함된 예제문이다. 모든 시험에 대해서 SCORE 테이블로부터 점수를 검색한다. (퀴즈점수는 무시).

SELECT * FROM SCORE
WHERE EVENT_ID IN (
	SELECT EVENT_ID FROM GRADE_EVENT WHERE CATEGORY='T');

/*** 위와 동일한 쿼리를 서브쿼리 없이 단순한 조인을 사용하는 것으로 변환할 수 있다. ***/
SELECT SCORE.* FROM SCORE INNER JOIN GRADE_EVENT
ON SCORE.EVENT_ID = GRADE_EVENT.EVENT_ID
WHERE GRADE_EVENT.CATEGORY='T';

또 다른 예제로, 다음의 쿼리는 여학생에 대한 점수를 검색한다.

SELECT * FROM SCORE
WHERE STUDENT_ID IN (SELECT STUDENT_ID FROM STUDENT WHERE GENDER='F');

/*** 위의 쿼리는 다음과 같이 조인을 사용하는 쿼리로 변환될 수 있다. ***/
SELECT SCORE.* FROM SCORE INNER JOIN STUDENT
ON SCORE.STUDENT_ID = STUDENT.STUDENT_ID
WHERE STUDENT.GENDER='F';

여기에 한가지 패턴이 있다. 서브쿼리는 다음과 같은 형식을 따른다.

SELECT * FROM table1
WHERE column1 IN (SELECT column2a FROM table2 WHERE column2b=value);

이러한 쿼리들은 다음과 같은 형식을 사용해서 조인을 이용한 쿼리로 변환될 수 있다.

SELECT table1.* FROM table1 INNER JOIN table2
ON table1.column1 = table2.column2a
WHERE table2.column2b=value;

어떤 경우에는, 서브쿼리와 조인은 다른 결과를 반환한다. table2가 column2a의 다중 인스턴스를 포함할 때 발생한다.
서브쿼리는 각 column2a 값의 오직 한 인스턴스만을 생성하지만,
조인은 모두 생성하고, 중복된 행을 가지게 된다.
이러한 중복을 막기 위해서 SELECT보다 SELECT DISTINCT를 가지고 조인을 사용한다.

비매칭(유실) 값을 검색하는 서브쿼리의 재작성

어떤 하나의 테이블에 있는 값이지만 또 다른 테이블에는 존재하지 않는 값을 검색할 때에도 서브쿼리문의 다른 일반적인 타입을 주로 사용한다.
이전에 보았던 것과 같이, "어떤 값이 없는가"하는 문제에는 LEFT JOIN이 효과적으로 적용되는 문제이다.
다음은 ABSENCE 테이블에 나열되지 않은 학생들에 대한 시험을 서브쿼리를 이용해서 검색하는 쿼리이다. (개근한 학생들)

SELECT * FROM STUDENT
WHERE STUDENT_ID NOT IN (SELECT STUDENT_ID FROM ABSENCE);

이 쿼리는 LEFT JOIN을 이용하여 다음과 같이 재작성할 수 있다.

SELECT STUDENT.*
FROM STUDENT LEFT JOIN ABSENCE ON STUDENT.STUDENT_ID = ABSENCE.STUDENT_ID
WHERE ABSENCE.STUDENT_ID IS NULL;

일반적인 서브 셀렉트 쿼리 형태와 LEFT JOIN 쿼리는 다음과 같다.

SELECT * FROM table1
WHERE column1 NOT IN (SELECT column1 FROM table2);

/******************************************************************/

SELECT table1.*
FROM table1 LEFT JOIN table2 ON table1.column1 = table2.column2
WHERE table2.column2 IS NULL;

위의 문장들은 table2.column2 가 NOT NULL로 선언되었다고 가정한다.

서브쿼리는 LEFT JOIN보다 더 직관적인 점에서 장점을 가지고 있다.
"없다"는 개념은 DB Programming의 영역 밖에 해당하기 때문에 대부분의 사람들이 어려움 없이 이해할 수 있다.
하지만, 일반적으로 이해할 때 그러한 기본이 없기 때문에 "왼쪽 조인"의 개념에서는 이러한 것이 동이랗게 적용될 수 없다.

출처: Paul DuBois, 김형훈 역, 2009. 한국어판 MySQL 4th ed., chapter2 section9.