서브쿼리에 대해 알아보자 3편 (feat. 인라인 뷰)

안녕하세요, 끙정입니다.

 

오늘도 서브쿼리에 대해서 알아보겠습니다.

오늘은 서브쿼리에서 인라인 뷰에 대해서 알아보고자 합니다.

아주 유용하고 활용도가 높은 문법입니다.

 

1. SELECT의 중첩

서브쿼리는 값이 올 수 있는 곳이면 어디나 올 수 있지만 주로 다음 세 곳에서 사용합니다.

 

1) WHERE 절: 조건에 사용할 값을 찾는다.

2) SELECT 절의 필드 목록: 출력할 값을 찾는다.

3) FROM 절: 출력 대상 테이블을 생성한다.

 

WHERE 절이나 SELECT 절의 서브쿼리는 둘 다 값을 리턴합니다.

이에 비해 FROM 절에 오는 서브쿼리는 값이 아닌 테이블을 리턴합니다.

FROM 절은 조회 대상 테이블을 명시하는 문장이니 FROM 다음의 서브쿼리는 테이블과 자격이 같습니다.

 

이러한 FROM 절의 서브쿼리를 특별히 인라인뷰(INLINE VIEW) 라고 부릅니다.

뷰는 테이블의 정보를 가지는 DB 오브젝트입니다.

인라인이 앞에 붙은 이유는 CREATE로 만들어 영구적으로 저장하는 것이 아니라 서브쿼리에서 임시적으로 쓰고 버리는 뷰라는 뜻입니다.

 

간단한 인라인뷰는 아래와 같습니다.

 

SELECT * FROM (SELECT * FROM tCity) A;

SELECT * FROM (SELECT name, popu, area FROM tCity) A;

SELECT * FROM (SELECT * FROM tCity WHERE metro = 'n') B;

 

 

사실 위와 같이 단순히 출력만 할 것이라면 굳이 인라인뷰를 사용할 이유는 없습니다.

그러나 조건이 까다로워질수록 인라인뷰는 큰 힘을 가집니다.

 

/* 직급이 차장 or 부장이면서 score가 70이상인 직원을 찾아라 */
SELECT * FROM (SELECT * FROM tStaff WHERE grade = '차장' OR grade = '부장') A
WHERE A.score >= 70;

 

 

사실 이마저도 IN 과 ADN 조건으로 구현할 수 있지만,

더 복잡한 조건이 생기기 시작하면 WHERE 조건절 만으로는 정확한 값을 출력하기 어렵습니다.

인라인뷰는 조건을 독립적이고 순차적으로 해결할 수 있으므로 아주 유용합니다.

 

2. 인라인뷰의 별명

인라인뷰는 서브쿼리에 의해 메모리상에서 잠시 생성되었다가 다 사용하고 나면 사라지는 임시 테이블입니다.

영구적으로 저장하는 DB객체가 아니어서 이름이 없습니다.

쿼리 실행 중에 이 테이블을 칭하려면 이름이 있어야 하는데 임시 객체에 이름을 붙일 때는 별명을 사용합니다.

 

SELECT name, popu FROM (SELECT * FROM tCity WHERE popu >= 50) A;

 

그런데 위 쿼리는 인라인뷰의 필드를 참조하지 않아 별명을 사용하지 않습니다.

쓰지 않는 별명을 생략해도 되지 않을까 생각하지만, DBMS는 인라인뷰에 꼭  별명을 붙일 것을 권장합니다.

SQL Server와 MariaDB는 인라인뷰의 별명을 생략하면 에러가 뜨고,

오라클은 생략할 수 있지만 호환성을 위해 쓰는 것이 좋습니다.

 

오라클은 별명의 생략을 허용하지만 특이하게 AS를 쓰면 에러가 뜹니다.

SQL Server와 MariaDB에서는 AS를 써서 별명을 붙여도 허용되지만 오라클은 아예 쓰지 말든지, AS없이 붙이든지 해야 합니다.

 

AS는 허용하지만, 없는 건 허용하지 않는 SQL Server. 오라클은 반대다.

 

즉, 이렇게 기억하는 게 좋습니다.

필드의 별명은 필요할 때만 붙이고, AS는 있어도 되고 없어도 됩니다.

인라인뷰의 별명은 항상 붙이되 AS는 쓰지 말아야 합니다.

이러면 오라클도 SQL Server도 MariaDB도 다 에러없이 잘 굴러갑니다.

 

인라인뷰를 이용하면 임시 필드를 활용할 수도 있습니다.

아래 예시에서 첫 번째 쿼리는 에러가 뜹니다.FROM 절 앞에 오는 임시 필드는 참조할 수 없기 때문입니다.그러나 FROM 절에서 서브쿼리로 인라인뷰를 생성하면 참조하여 활용할 수 있습니다.

 

/* ingu는 임시 필드이므로 다른 곳에서 불러올 수 없다. */
SELECT name, popu * 10000 AS ingu FROM tCity WHERE ingu > 1000000;

/* 참조를 위해선 인라인뷰를 활용하는 것이 편하다 */
SELECT * FROM (
	SELECT name, popu * 10000 AS ingu FROM tCity
	) A
WHERE A.ingu > 1000000;

 

 

3. 반복되는 변수

쿼리를 짜다 보면 변수를 반복적으로 사용해야 할 상황이 옵니다.

위에서도 보았듯이 FROM 절의 앞 필드에서 붙인 별명을 바로 뒤의 필드에서 참조할 수는 없습니다.

그래서 인라인뷰를 통해서 변수를 참조하는 것이 좋습니다.

바로 다음과 같이 말이죠.

 

/* FROM절 앞의 임시 필드 dens를 곧바로 참조해서 쓸 수 없다. */
SELECT name, (popu * 10000 / area ) AS dens
	,CASE
		WHEN dens > 1000 THEN '고밀도'
		WHEN dens > 100 THEN '중밀도'
		ELSE '저밀도'
	END densgrade
FROM tCity;

/* FROM 절에서 인라인뷰를 통해 임시 테이블을 만든 후, dens를 참조한다. */
SELECT name, dens
	,CASE
		WHEN dens > 1000 THEN '고밀도'
		WHEN dens > 100 THEN '중밀도'
		ELSE '저밀도'
	END densgrade
FROM (
	SELECT name, (popu * 10000 / area) AS dens FROM tCity
) CD;

 

인라인뷰를 통해 dens 변수를 참조 가능하다.

 

비슷한 패턴으로 그 다음 문제를 풀어보겠습니다.

인구와 면적으로 인구밀도를 계산하고 인구밀도로부터 등급을 만들었습니다.

이 등급을 다시 사용해 예싼이나 도로망 확충 계획 등을 만들 수 있습니다.

쿼리를 짜보겠습니다.

 

/* 불필요한 반복으로 비효율적인 쿼리 */
SELECT name, dens
	,CASE /* densgrade 필드 출력 */
		WHEN dens > 1000 THEN '고밀도'
		WHEN dens > 100 THEN '중밀도'
		ELSE '저밀도'
	END densgrade
	,
	CASE /* roadplan 필드 출력 */
	WHEN
		CASE /* 그러나 densgrade를 참조할 수 없기에 참조 가능한 dens로 CASE문을 또 써야 한다. */
			WHEN dens > 1000 THEN '고밀도'
			WHEN dens > 100 THEN '중밀도'
			ELSE '저밀도'
		END = '고밀도' THEN '8차로'
	WHEN
		CASE /* 조건마다 써야 한다.. */
			WHEN dens > 1000 THEN '고밀도'
			WHEN dens > 100 THEN '중밀도'
			ELSE '저밀도'
		END = '중밀도' THEN '4차로'
	ELSE '2차로'
	END roadplan
FROM (
	SELECT name, (popu * 10000 / area) AS dens FROM tCity
) CD;

/* 인라인뷰를 두 번 감싸서 densgrade를 정의 내리고 읽어버리는 효율적인 쿼리 */
SELECT name, dens, densgrade,
CASE
	WHEN densgrade = '고밀도' THEN '8차로'
	WHEN densgrade = '중밀도' THEN '4차로'
	ELSE '2차로'
END roadplan
FROM
(
	SELECT name, dens
		,CASE
			WHEN dens > 1000 THEN '고밀도'
			WHEN dens > 100 THEN '중밀도'
			ELSE '저밀도'
		END densgrade
	FROM
	(
		SELECT name, (popu * 10000 /area) AS dens FROM tCity
	) CD
) CR;

 

 

꽤 복잡한 쿼리이지만,

이런 식으로 세 번, 네 번 중첩하는 경우가 많으니 개념을 확실히 익혀두는 것이 좋습니다.

SQL문법은 영어와 비슷하기 때문에 개념적으로 확 와닿지 않을 수 있습니다.

꾸준한 연습만이 살 길인 것 같습니다.

 

그만 알아보겠습니다.

 

출처

http://www.yes24.com/Product/Goods/101637633

김상형의 SQL 정복 : 소문난 명강의 (무료특별판) - YES24