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

서브쿼리(1)

서브쿼리는 다른 SELECT 쿼리 내부에 SELECT 쿼리를 중첩시키는 것을 허용하는 기능이다.
다음은 GRADE_EVENT테이블의 시험('T')에 해당하는 이벤트 행들에 대한 ID를 찾아서,
이 ID를 사용하여 이 시험에 대한 점수들을 검색하는 예문이다.

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

서브쿼리는 다른 형태의 정보를 반환할 수 있다.

• 스칼라 서브쿼리는 단일 값을 반환한다.
• 스칼라 서브쿼리는 = 또는 < 와 같은 상대 비교 연산자를 사용하여 테스트한다.
• 칼럼 서브쿼리는 하나 이상의 값을 가지는 단일 칼럼을 반환한다.
• 행 서브쿼리는 하나 이상의 값을 가지는 단일 행을 반환한다.
• 테이블 서브쿼리는 하나 이상의 칼럼과 하나 이상의 행을 가지는 테이블을 반환한다.
• IN 과 NOT IN은 서브쿼리에 의해서 반환되는 값들 중에 해당되는 값이 존재하는 경우에만 테스트한다.
• ALL, ANY, SOME은 서브쿼리에 의해서 반환되는 값과 비교한다.
• EXISTS와 NOT EXISTS는 서브쿼리 결과의 존재유무를 테스트한다.

스칼라 서브쿼리는 오직 하나의 값만 생성하기 때문에 가장 제한적이다.
하지만, 스칼라 서브쿼리는 다양한 용도로 사용될 수 있다.
출력 칼럼 리스트 내부 또는 함수 인자와 같은 일종의 표현식으로 스칼라 오퍼랜드가 필요한 곳이면 어디든지 필수적으로 사용될 수 있다.

스칼라 서브쿼리의 특징

• 스칼라 서브쿼리란 Select 절에서 또 다른 select 절이 사용 될때 이를 스칼라 서브쿼리라 칭한다.
• 하나의 레코드만 리턴이 가능하며, 두개 이상의 레코드는 리턴할 수 없다.
• 일치하는 데이터가 없더라도 NULL값을 리턴할 수 있다.
• 이는 원래 그룹함수의 특징중 하나인데 스칼라 서브쿼리 또한 이 특징을 가지고 있다.

많은 정보를 제공하는 칼럼, 행, 테이블 서브쿼리들은 단일 값을 필요로 하는 컨텍스트에서 사용될 수 있다.

서브쿼리들은 상관성 여부가 중요하지 않다. 이는 서브쿼리의 참조 여부에 대한 함수이며, 외부 쿼리의 값과 의존관계가 있다.
SELECT 이외의 다른 문장을 사용하여 서브쿼리를 사용할 수도 있다.
그러나 테이블 수정에 대한 문장(INSERT, REPLACE, DELETE, UPDATE, LOAD DATA)들에 대해서는
서브쿼리가 수정되고 있는 테이블에 대해서는 참조할 수 없다는 제한을 가지고 있다.

상대 비교 연산자를 이용한 서브쿼리

연산자(=, <>, >, >=, <, <=)는 상대 값 비교를 수행하여 스칼라 서브쿼리를 사용할 때
서브쿼리에 의해서 반환되는 값에 특정 관계를 유지하는 외부 쿼리의 모든 행들을 찾는다.
SELECT가 단일 값을 식별하여 외부 SELECT와 비교하는데 사용되도록 하기를 원하는 것이다.

예를 들어, GRADE_EVENT테이블의 퀴즈에 해당하는 EVENT_ID를 알아내고 나서,
외부 SELECT안에서 그 EVENT_ID에 대해 SCORE테이블의 점수 행들을 대응시킨다.

SELECT *
FROM SCORE
WHERE EVENT_ID IN =
	(
		SELECT EVENT_ID
		FROM GRADE_EVENT
		WHERE DATE='2008-09-23' AND CATEGORY = 'Q'
	);

이러한 형태의 문장에서는 서브쿼리가 비교 연산자 뒤에 나오게 되는데,
필수적으로 오직 단일 값만을 만들어내야만 한다.
어떤 경우에는 이러한 조건을 만족시키기 위해서 내부 쿼리의 결과를 LIMIT 1 로 제한하는 것이 적당할 때가 있다.

예를들어, PRESIDENT테이블에 BIRTH라는 칼럼을 사용해 어느 대통령이 먼저 태어났는지 알고 싶다면,
SELECT * FROM PRESIDENT WHERE BIRT = MIN(BIRTH);
WHERE절 안에서 집단 함수를 사용할 수 없으므로, 위 문장은 작동하지 않는다.
WHERE절은 어느 행을 선택할지 결정하지만, MIN()의 값은 행이 이미 선택되기 전에는 알 수 없는 것이 된다.
SELECT * FROM PRESIDENT WHERE BIRT = (SELECT MIN(BIRTH) FROM PRESIDENT);

만약 서브쿼리가 단일 행을 반환한다면, 서브쿼리 결과에 대한 일련의 값을 비교하기 위한 열 생성자를 사용할 수 있다.
이러한 문장은 John Adams와 같은 도시와 주에서 태어난 대통령에 대한 레코드들을 반환한다.
(city, state)와 동등한 ROW(city, state)를 사용할 수도 있다. 이 둘 다 튜플을 나타내는 행 생성자로서 동작한다.

SELECT LAST_NAME, FIRST_NAME, CITY, STATE
FROM PRESIDENT
WHERE (CITY, STATE) =
	(
		SELECT CITY, STATE
        FROM PRESIDENT
        WHERE LAST_NAME = 'Adams' AND FIRST_NAME='John'
    );

LAST_NAME	FIRST_NAME	CITY	STATE
Adams	John	Braintree	MA
Adams	John Quincy	Braintree	MA

IN과 NOT IN 서브쿼리

서브쿼리가 IN과 NOT IN 연산자를 사용할 수 있는 경우는 외부 쿼리와 비교해서 사용될 수 있는 복수의 행들을 반환하고자 할 때이다.
검증을 위해, 비교할 값이 존재하는지의 여부를 테스트 한다. IN은 외부 쿼리의 행이 서브쿼리에 의해서 반환되는 행과 매칭되지 않을 때 참이다.
NOT IN은 외부 쿼리의 행이 서로 쿼리에 의해서 반환되는 행과 매칭되지 않을 때 참에 해당된다.
다음의 문장은 absence테이블에서 나열된 결석한 학생들과 완전하게 출석한 학생들을 찾고자 할 때 IN과 NOT IN이 사용되는 경우에 대해서 나타내었다.

파일

create tables.sql
insert data.sql

SQL문1

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

한번이라도 결석한 학생들

NAME	GENDER	STUDENT_ID
Kyle	M	3
Abby	F	5
Peter	M	10
Will	M	17
Avery	F	20

SQL문2

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

개근한 학생들. 총 26명이 나열될 것이다.

IN과 NOT IN은 복수의 칼럼을 반환하는 서브쿼리에 대해서도 동작한다.

ALL, ANY, SOME 서브쿼리

ALL과 ANY 연산자는 칼럼 서브쿼리의 결과를 테스트하기 위해서 상대 비교 연산자와 관련되어 사용될 수 있다.
이는 서브뭐리에 의해서 반환되는 값들의 일부 또는 전체가 비교 값이 특정 관계가 있는지를 테스트한다.
예를 들면, <=ALL은 비교 값이 서브쿼리가 반환한 값과 동일하거나 적으면 참이다.
SOME은 ANY와 동의어 이다.

SQL문 및 파일

SELECT LAST_NAME, FIRST_NAME, BIRTH FROM PRESIDENT
WHERE BIRTH <= ALL(SELECT BIRTH FROM PRESIDENT);

LAST_NAME	FIRST_NAME	BIRTH
Washington	George	1732-02-22

ALL과 ANY 또는 SOME은 = 비교 연산자와 함께 사용될 때, 서브쿼리는 테이블 쿼리가 된다.
이러한 경우에 비교 값을 제공하는 행 생성자를 사용하여 행이 반환되는지를 테스트할 수 있다.
이전 절에서 언급한 것과 같이, IN과 NOT IN은 =ANY와 <>ALL의 다른 표현이다.
즉, IN은 "서브쿼리에 의해서 반환되는 어떤 행과 동일한"의 의미이고,
NOT IN은 "서브쿼리에 의해서 반환되는 모든 행과 동일하지 않은"의 의미이다.

EXISTS와 NOT EXISTS 서브쿼리

EXISTS와 NOT EXISTS 연산자는 단순히 서브쿼리가 행들을 반환하는지를 테스트한다.
만약 그렇게 반환된 값이 존재하면, EXISTS는 참이며 NOT EXISTS는 거짓이다.
다음의 문장은 이러한 서브쿼리들에 대한 몇가지 예제를 보여준다.
만약 ABSENCE 테이블이 비어있다면, 첫 번째는 0을 반환하며, 두 번째는 1을 반환한다.

SELECT EXISTS (SELECT * FROM ABSENCE);
SELECT NOT EXISTS (SELECT * FROM ABSENCE);

EXISTS와 NOT EXISTS는 실제로 상관 서브쿼리에서 더 많이 사용될 수 있다. 이는 다음 글에서 다루겠다.
EXISTS와 NOT EXISTS를 사용할 때, 서브쿼리는 출력 칼럼 리스트를 위해서 *를 사용한다.
명백하게, 카럶의 이르믕ㄹ 사용할 피룡가 없다.
이유는 서브쿼리는 행들을 반화하는가, 아니면 반환하지 못하는가의 여부에 따라서 참 또는 거짓 값이 사용되기 때문이며,
행이 포함하고 있는 특정 값과는 상관이 없다.

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