9-3.인라인뷰
1.SELECT의 중첩
서브쿼리는 값이 올 수 있는 곳이면 어디나
올 수 있지만 주로 다음 세 곳에서 사용한다. 위치에 따라 값을 사용하는 용도가 다르다.
l
WHERE 절 : 조건에 사용할 값을 찾는다.
l
SELECT 절의 필드 목록 : 출력할 값을 찾는다.
l
FROM 절 :
출력 대상 테이블을 생성한다.
WHERE
절이나 SELECT 절의 서브쿼리는 둘 다 값을 리턴한다.
하나일 수도 있고 여러 개일 수도 있지만 어쨌거나 값이다. 이에 비해 FROM 절에 오는 서브쿼리는 값이 아닌 테이블을 리턴한다. FROM 절은
조회 대상 테이블을 명시하는 문장이니 FROM 다음의 서브쿼리는 테이블과 자격이 같다.
FROM
절의 서브쿼리를 특별히 인라인뷰(inline view)라고 부른다. 뷰는 테이블의 정보를 가지는 DB 오브젝트이다. 인라인이 앞에 붙은 이유는 CREATE로 만들어 영구적으로 저장하는
것이 아니라 서브쿼리에서 잠시 만들어 쓰고 버리는 임시적인 뷰라는 뜻이다.
서브쿼리가 FROM 절에 오면 SELECT 문끼리 중첩된다. 인라인뷰의 SELECT가 리턴하는 결과셋은 하나의 테이블이다. SELECT * FROM tCity는 도시 목록이며 따라서 SELECT 문
자체가 FROM 절에 올 수 있다. SELECT한 걸 또
SELECT하는 식이다. 다음은 가장 간단한 인라인뷰이다.
SELECT *
FROM (SELECT * FROM tCity) A;
FROM
절에 있는 안쪽의 SELECT 문이 리턴하는 도시 목록은 하나의 테이블이니 결과셋으로부터
또 SELECT 명령을 실행할 수 있다. 이후 인라인뷰의
필드를 칭하기 위해 당장 쓰지 않더라도 별명을 꼭 붙여야 한다. 위 문장에서는 A라고 붙였다.
테이블에서 모든 필드를 읽어 결과셋을
만들고 그 테이블에서 또 모든 필드를 읽으니 결국 원본 테이블과 똑같다. 당장은 아무 짝에도 쓸모 없는
중첩이지만 일단 중첩해 놓으면 원하는 부분에 변화를 줄 수 있다. 안쪽 서브쿼리를 변형하면 외부쿼리가
보는 테이블이 달라진다.
SELECT *
FROM (SELECT name, popu, area FROM tCity) A;
SELECT *
FROM (SELECT * FROM tCity WHERE metro = 'y') B;
첫 번째 서브쿼리는 열의 일부만을 취한다. 두 번째 서브쿼리는 행의 일부만을 취한다. 기존에 존재하는 테이블로부터
부분 테이블을 만들어 낸다.
열과 행을 동시에 제한하여 일부열의 일부
레코드만 취할 수도 있다. 외부쿼리는 아직까지는 서브쿼리의 추출 결과 전체를 출력하기만 한다. 출력만을 위해서라면 굳이 중첩할 필요가 없지만 단계를 거치면 여러 가지 기교를 부릴 여지가 생긴다.
인라인뷰를 사용하면 큰 문제를 작은 문제로
분할하여 사고의 단계가 단순해진다. 회원 목록에서 미성년자중에 예치금이 10만원 이상인 사람을 찾아 보자. 먼저 인라인뷰로 19세 미만의 미성년자를 찾는다. 그리고 그 중에 예치금 10만원 이상을 찾는다.
SELECT
member, addr FROM (SELECT * FROM tMember WHERE age < 19) A
WHERE
A.money >= 100000;
애초의 문제를 미성년자를 찾는 것과 예치금
10만원 이상인 회원을 찾는 것 둘로 쪼개서 생각할 수 있다. 인라인뷰로
미성년자로 구성된 테이블을 만들어 별명 A를 붙인다. 그리고
A 테이블에서 예치금 10만원 이상을 찾으면 된다. 이 경우는 조건을 연결하여 더 짧게 해결할 수 있다.
SELECT
member, addr FROM tMember WHERE age < 19 AND money >= 100000;
그러나 샘플이 워낙 단순해 한 번에 복합
조건을 찾아 낼 수 있지만 실무에서는 이런 방식이 비효율적이거나 불가능한 경우가 있다. 여러 복합 조건을
한 번에 정확히 만들어내는 것이 예상보다 어렵다. 과장 또는 부장인 직원 중 성취도가 70점 이상인 사람을 찾아 보자.
SELECT *
FROM (SELECT * FROM tStaff WHERE grade = '과장' OR grade
= '부장') A
WHERE
A.score >= 70;
인라인뷰로 과장 또는 부장인 직원을 먼저
찾는다. 두 조건을 OR로 연결하는 것은 아주 쉽다. 인라인뷰가 만든 목록은 다음과 같다.
이 테이블은 전체 직원 목록과는 달리
과장과 부장만 모아 놓은 것이다. 이렇게 만든 인라인뷰 A에서
성취도가 70점 이상인 직원을 다시 골라낸다. 윤봉길과 신사임당만
남는다.
그럼 이 경우도 조건식을 조합하면 될까? 과장 또는 부장이면서 성취도가 70점 이상인 복합 조건으로 직원을
필터링했다. 얼핏 보기에는 맞는 것 같지만 결과는 완전히 딴판으로 나타난다.
SELECT *
FROM tStaff WHERE grade = '과장' OR grade = '부장' AND score >= 70;
OR 보다는
AND의 우선 순위가 높아 위 조건문은 과장 또는 성취도 70점
이상인 부장을 골라내기 때문이다. 이 정도 문제는 괄호로 우선순위만 조정해도 쉽게 해결할 수 있지만
큰 문제를 한 번에 풀려고 하면 이런 함정이 곳곳에 도사리고 있다.
인라인뷰를 사용하면 큰 문제를 잘게 분할하여
하나씩 풀 수 있다. 과장 또는 부장을 먼저 찾아 중간 테이블을 만들어 두고 그 중에서 성취도 70점 이상을 고르는 것이 더 쉽다. 이런 기법을 분할 정복이라고 하며
사고가 직선적이어서 문제를 단계적으로 풀어 나갈 수 있다.
인라인뷰를 제대로 써 먹으려면 더 복잡하고
골치 아픈 문제를 만나 봐야 한다. 실무에서 그런 문제는 얼마든지 만들 수 있으므로 여기서는 정의와
사용 방법만 잘 알아 두고 넘어가자.
연습 문제
8.주문
받은 것 중 10개 이상인 대량 주문 품목을 인라인뷰로 조사해 보자.
2.인라인뷰의 별명
인라인뷰는 서브쿼리에 의해 메모리상에서
잠시 생성되었다가 다 사용하고 나면 사라지는 임시 테이블이다. 영구적으로 저장하는 DB 객체가 아니어서 이름이 없다. 쿼리 실행 중에 이 테이블을 칭하려면
이름이 있어야 하는데 임시 객체에 이름을 붙일 때는 별명을 사용한다.
SELECT
member, addr FROM (SELECT * FROM tMember WHERE age < 19) A
WHERE
A.money >= 100000;
서브쿼리로 미성년자 테이블을 만들었으며
이 테이블에서 예치금 필드를 읽어 조건을 걸어야 한다. 인라인뷰는 tMember에서
일부 데이터만 간추린 것이어서 tMember와는 다른 테이블이며 따라서 예치금을 tMember.money라고 칭할 수는 없다. 그래서 인라인뷰에 별명
A를 붙이고 A.money로 읽는다.
별명은 구분이 목적이고 임시적인 것이어서
어떤 것이라도 상관없다. 가급적 의미있는 이름을 붙여 tChild 정도가
적당하다. 그러나 입력하기 귀찮으니 보통 알파벳 한두자로 짧게 붙인다.
인라인뷰가 임시적인 테이블이다 보니 차후 참조를 위해 별명이 꼭 필요함을 알 수 있다. 이
쿼리는 모든 DBMS에서 다 잘 실행된다. 그렇다면 다음
쿼리는 어떨까?
SELECT *
FROM (SELECT * FROM tCity WHERE metro = 'y') A;
광역시만으로 임시 테이블을 생성하고 별명
A를 붙였지만 인라인뷰의 필드를 참조하지 않아 이 별명을 사용하지는 않는다. 그럼 쓰지도 않을 별명은 생략해도 되지 않을까? 오라클은 미사용
별명을 생략할 수 있지만 SQL Server와 MariaDB는
별명을 생략하면 에러 처리한다.
인라인뷰의 필드를 참조할 경우가 흔하고
당장은 쓰지 않더라도 앞으로 쓸 일이 있을거 같으니 미리 이름을 붙여 놓으라는 뜻이다. 강제가 좀 심한
거 같지만 인라인뷰를 만들어 놓고 그냥 출력만 하는 경우가 더 드물다. 쓰든 안쓰든 일관되게 별명을
붙일 것을 권한다.
별명의 생략을 허용하는 오라클이 더 관대하다. 그러나 오라클은 또 다른 제약이 있다. 필드나 테이블에 별명을 붙일
때는 전치사 AS를 사용하여 별명임을 확실히 표현하는 것이 보통이다.
A만 혼자 있으면 잘 보이지 않지만 AS가 있으면 눈에 잘 띄어 가독성이 향상된다.
SELECT *
FROM (SELECT * FROM tCity WHERE metro = 'y') AS A;
그러나 이 문법은 SQL Server와 MariaDB에서는 통하지만 오라클은 에러 처리한다. 오라클은 아예 별명을 붙이지 말든가 아니면 AS 없이 별명만 붙여야
한다. 참 까다롭다.
다음은 필드에 대한 별명 규칙을 정리해
보자. 필드에도 별명을 붙일 수 있으며 이때 전치사 AS는
있어도 되고 없어도 상관없다. 모든 DBMS가 AS의 생략을 허용하되 붙이는 것이 보기 좋다.
SELECT
name, popu * 10000 AS ingu FROM tCity;
tCity의
인구가 만명 단위여서 10000을 곱해 명수 단위로 바꾸고 ingu라는
별명을 붙였다. 그럼 인구가 백만 이상이라는 조건은 어떻게 붙일까?
SELECT
name, popu * 10000 AS ingu FROM tCity WHERE ingu > 1000000;
이건 안된다. ingu는 tCity에 있는 진짜 필드명이 아니라 필드 목록에서
잠시 정의한 별명이기 때문이다. 꼭 조건을 붙이려면 WHERE
popu > 100 이렇게 붙여야 하는데 명수 단위가 아니어서 또 헷갈린다. 정 ingu 필드를 쓰고 싶으면 인라인뷰에 별명을 붙이고 이 별명을 앞에 붙이면 된다.
SELECT *
FROM (
SELECT name, popu *
10000 AS ingu FROM tCity
) A
WHERE
A.ingu > 1000000;
이건 잘 된다. ingu는 인라인뷰 A의 필드이며 외부쿼리에서 A를 읽고 있으므로 그 필드를 조건에 사용할 수 있다. 오히려 WHERE popu > 100은 에러 처리된다. 오라클은 인라인뷰의
별명을 아예 빼 버리고 ingu를 그냥 칭해도 된다.
SELECT *
FROM (
SELECT name, popu *
10000 AS ingu FROM tCity
)
WHERE ingu
> 1000000;
인라인뷰 바로 다음의 WHERE 절이니 누구의 필드인지 애매하지는 않다. 오라클은 꼭 필요치
않은 별명의 생략을 허용하지만 SQL Server와 MariaDB에서는
인라인뷰의 별명이 필수이다. 쿼리가 더 복잡해지면 모호함이 발생할 가능성이 있어 아예 인라인뷰의 별명
생략을 허용하지 않는다.
양쪽 다 이유는 있지만 별명을 붙이는
규칙이 좀 복잡하다. 필드의 별명은 필요할 때만 붙이고 AS는
있어도 되고 없어도 된다. 인라인뷰에 대한 별명은 항상 붙이되 AS는
쓰지 말아야 한다. 이 규칙만 지키면 모든 DBMS에서 다
실행할 수 있는 호환성 좋은 쿼리가 된다.
3.반복되는 변수
tCity에는
인구와 면적에 대한 정보가 있어 이 둘을 조합하면 인구밀도를 계산할 수 있다. 인구수를 면적으로 나누는
간단한 수식이면 된다. 계산식으로 만든 필드에 dens라는
별명을 붙였다. 다음 쿼리는 도시명과 인구밀도를 출력한다.
SELECT
name, (popu * 10000 / area) AS dens FROM tCity;
긴 계산식을 여러 번 참조하면 쿼리문도
같이 길어진다. 인구밀도로부터 등급을 나누어 표시해 보자. 이럴
때는 조건에 따라 값을 선택하는 CASE 문을 사용하는데 미리 예습을 하고 오자. 1000명 이상은 고밀도 100명 이상은 중밀도 그 이하는 저밀도로
등급을 나누었다.
SELECT
name, (popu * 10000 / area) AS dens
,CASE
WHEN
(popu * 10000 / area) > 1000 THEN '고밀도'
WHEN
(popu * 10000 / area) > 100 THEN '중밀도'
ELSE
'저밀도'
END densgrade
FROM
tCity;
인구밀도 계산식이 출력문과 CASE 문의 조건식에 여러 번 나타난다. 이 정도 수식이면 그나마
짧은 것이며 여러 개의 값을 조합하면 100자 이상의 수식도 흔하다.
똑같은 수식을 반복하면 짜증날 뿐만 아니라 실수할 가능성도 높아진다. 반복적인 구문은 합치는
것이 좋다. 수식에 dens라는 별명을 주었으니 이걸 쓰면
되지 않을까?
SELECT
name, (popu * 10000 / area) AS dens
,CASE
WHEN
dens > 1000 THEN '고밀도'
WHEN
dens > 100 THEN '중밀도'
ELSE
'저밀도'
END densgrade
FROM
tCity;
될 거 같지만 FROM 절의 앞 필드에서 붙인 별명을 바로 뒤의 필드에서 참조할 수는 없다. 인구밀도와
등급은 같은 레벨의 필드 목록에 속해 있으며 dens를 정의하는 중에 사용할 수는 없다. 이럴 때는 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;
CD 인라인뷰에서
계산식을 하나의 필드를 정의하고 dens 별명까지 붙여 놓았다. 인라인뷰만
따로 실행하면 마치 name과 dens를 필드로 가지는 테이블과
같다. 외부쿼리에서는 CD.dens로 인구밀도를 얼마든지
여러 번 참조할 수 있다.
계산식을 인라인뷰에 포함시키고 외부쿼리에서는
별명으로 필드 읽듯이 읽는다. 워낙 정형화된 패턴이며 사용 빈도가 높아 잘 기억해 두어야 한다. 식이 짧고 고작 세 번이어서 실감이 안나겠지만 실무에서는 수식도 길고 더 자주 사용한다.
이 패턴을 이해했으면 다음 문제를 계속
풀어 보자. 인구와 면적으로 인구밀도를 계산하고 인구밀도로부터 등급을 만들었다. 이 등급도 여러 번 사용할 수 있다. 예를 들어 등급에 따른 필요
예산이나 도로망 확충 계획 등을 또 만들 수 있다. 인구밀도를 구하는 계산식에 비해 등급을 구하는 CASE 문이 훨씬 더 길어 반복의 정도가 심해진다.
SELECT
name, dens
,CASE
WHEN
dens > 1000 THEN '고밀도'
WHEN
dens > 100 THEN '중밀도'
ELSE
'저밀도'
END densgrade
,
CASE
WHEN
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;
인구밀도가 높으면 8차로 도로를 깔고 적당하면 4차로,
밀도가 낮으면 2차로 도로를 깐다. 문제는 등급을
구하는 CASE 문이 계속 반복된다는 것이다. 앞 실습에서
dens를 수식에 바로 쓸 수 없었던 것과 마찬가지 이유로 필요한 도로를 결정하는데도 densgrade를 바로 쓸 수 없다.
같은 레벨에서는 CASE 문을 반복하는 수밖에 없고 그러다 보니 길고 지저분해진다. 자, 이 상황을 보면 반복을 어떻게 해소할지 감이 올 것이다. 한 번
더 인라인뷰로 감싸 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;
densgrade를
필드로 포함하는 인라인뷰를 정의한다. 인라인뷰에서 이미 등급을 다 계산해 두었으니 외부쿼리는 densgrade를 그냥 이름으로 참조하면 된다. CASE 문의 중첩은
사라지고 제일 바깥의 필드 목록에서 densgrade로부터 필요한 도로폭을 한 번에 선택할 수 있다. SELECT가 세 번 중첩되었다.
안쪽 : name 필드와
계산에 의해 생성한 dens 필드
중간 : name,
dens 필드와 dens로부터 계산한 densgrade 필드
바깥 : name,
dens, densgrade 필드와 densgrade로부터 계산한 roadplan
실무에서는 이런 식으로 네 번, 다섯 번 중첩하는 경우가 허다하지만 방식은 똑같다. 여러 번 사용할
계산식을 인라인뷰에서 필드로 정의하고 그 바깥에서는 필드를 사용하는 것이다. 지금 당장은 이 실습을
해 보기 귀찮겠지만 여기서 확실히 개념을 익혀 두면 어떤 중첩이든 척척 만들어낼 수 있다.
이런 중첩된 SELECT 문을 분석할 때는 안쪽부터 순서대로 어떤 테이블을 만드는지 잘 봐야 한다. 인라인뷰에서 뭘 정의하는지, 외부쿼리에서 인라인뷰를 어떻게 활용하는지
살펴보면 왜 이런 복잡한 문장이 필요한지 이해할 수 있다.