[Oracle] Main Query, Sub Query

💡Main Query

---

Main Query(일반 쿼리)는 하나의 문장 안에 하나의 SELECT(INSERT, UPDATE, DELETE)로 구성 되어 있는 쿼리이다.

 

💡Sub Query

---

Sub Query(서브 쿼리)는 하나의 문장(SELECT(INSERT, UPDATE, DELETE)) 안에 또 다른 문장(SELECT)이 들어 있는 쿼리이다.
SELECT문 안에 SELECT문이 들어갈 수도 있고, INSERT문 안에 SELECT문이 들어갈 수도 있다. 이때 또 다른 문장은 SELECT만 가능하다.

삽입 위치는 모든 곳(SELECT절, FROM절, WHERE절, GROUP BY절, HAVING절, ORDER BY절..)이 될 수 있다.

즉, 컬럼(값)을 넣을 수 있는 장소면 어디든지 서브쿼리가 들어갈 수 있다.

 

Sub Query의 사용

인구수가 가장 많은 나라의 이름

SELECT max(population) FROM tblcountry;
SELECT name FROM tblcountry WHERE population = 120760;

상수를 입력해도 결과가 출력되긴 하지만, 값이 변경되었을 때 매번 수정해줘야 한다는 불편함이 있다.

이를 서브 쿼리를 이용하여 해결할 수 있다.

SELECT name FROM tblcountry WHERE population = (SELECT max(population) FROM tblcountry);

숫자 120760은 max 함수가 아니라 SELECT max(population) FROM tblcountry; 문장이 만들어 낸 것이다.
서브 쿼리를 사용한다고 해서 코드의 양이 줄진 않지만, 상수를 적어서 매번 확인해야 하는 것, 그리고 인구 변화가 일어나서 에러가 날 확률이 줄어들었다.

 

몸무게가 가장 많이 나가는 사람의 이름

SELECT max(weight) FROM tblComedian;
SELECT * FROM tblComedian WHERE weight = 129;

SELECT * FROM tblComedian WHERE weight = (SELECT max(weight) FROM tblComedian);

 

💡서브 쿼리 삽입 위치

---

조건절

서브 쿼리를 조건절에 넣으면 비교값으로 사용한다.

서브 쿼리가 값을 하나만 돌려주는 게 아니라 값을 여러 개 돌려주는 경우가 있을 수 있다.
WHERE절에 서브 쿼리를 쓸 때 사용할 수 있는 방법은 총 4종류가 있다.

 

• a. 반환값이 1행 1열인 경우 단일값을 반환한다. > 상수 취급 > 값 1개
• b. 반환값이 N행 1열인 경우 다중값을 반환한다. > 열거형 비교 > IN 연산자 사용
• c. 반환값이 1행 N열인 경우 다중값을 반환한다. > 그룹 비교 > N컬럼:N컬럼
• d. 반환값이 N행 N열인 경우 다중값을 반환한다. > IN 연산자 + 그룹 비교

 

tblInsa 테이블에서 급여가 260만원 이상 받는 직원이 근무하는 부서의 직원 명단을 가져와 보도록 하자.

--ORA-01427: single-row subquery returns more than one row
SELECT
	*
FROM TBLINSA
	WHERE buseo = (SELECT buseo FROM tblInsa WHERE basicpay >= 2600000);

아이코! 싱글 로우 서브 쿼리가 여러 개의 행을 리턴했다는 오류가 발생했다.

 

SELECT buseo FROM tblInsa WHERE basicpay >= 2600000

이는 동등 비교 연산자는 오른쪽도 값을 하나 가져야 하는데, 부서 값으로 2개의 값을 돌려줘서 생긴 오류이다.

해결 방법으로 IN 연산자를 사용한다.

 

SELECT
	*
FROM TBLINSA
	WHERE buseo In(SELECT buseo FROM tblInsa WHERE basicpay >= 2600000);

'='(equal)을 쓰면 1:1 비교를 하고, IN 연산자를 쓰면 1:N 비교를 하기 때문에 열거형으로 취급하여 결과값이 올바르게 출력된다.

 

-- 1:1 비교를 N번 한 코드
SELECT * FROM TBLINSA
	WHERE city = (SELECT city FROM tblInsa WHERE name = '한석봉')
		AND buseo = (SELECT buseo FROM tblInsa WHERE name = '한석봉');

-- 2:2 비교를 한 코드
SELECT * FROM TBLINSA
	WHERE (city, buseo) = (SELECT city, buseo FROM tblInsa WHERE name = '한석봉');

이번에는 한석봉'과 같은 지역, 같은 부서인 직원 명단을 출력해 보자.

IN 연산자를 동일한 성격을 가지고 있을 때에만 사용할 수 있다.

 

--IN 연산자 + 그룹 비교
SELECT
	*
FROM TBLINSA
	WHERE (buseo, city) IN (SELECT buseo, city FROM TBLINSA WHERE basicpay >= 2600000);

이번에는 급여가 260만원 이상인 사람을 출력해보았다.

이때 IN 연산자 + 그룹으로 비교를 했다.

 

컬럼 리스트

컬럼 리스트에 서브 쿼리를 넣으면 컬럼값(출력값으로 사용)으로 사용한다.

반드시 결과값이 1행 1열이어야 한다. 이를 스칼라 쿼리라고 하며, 원자값을 반환한다.

 

정적 쿼리

정적 쿼리는 모든 행에 동일한 값을 반환하는 서브 쿼리를 말한다.

정적 쿼리는 값이 변하지 않으므로 활용도가 낮아 잘 사용하지 않는다.

 

SELECT
	name, buseo, basicpay,
	(SELECT round(avg(basicpay)) FROM tblInsa) AS avg
FROM tblInsa;

위 코드에서 출력하는 평균은 전체 직원에 대한 급여의 평균이다.

 

상관 서브 쿼리

상관 서브 쿼리는 서브 쿼리의 값과 바깥쪽 메인쿼리의 값을 서로 연결한다.

SELECT
	name, buseo, basicpay,
	(SELECT round(avg(basicpay)) FROM tblInsa WHERE buseo = a.buseo) AS avg
FROM tblInsa a;

상관 서브 쿼리를 이용하면 각 부서의 급여 평균 값을 구하는 게 가능한데, 이는 마치 재귀 함수 같은 느낌이 든다.
테이블에 Alias로 별칭을 붙여주었다. 안쪽에 있는 tblInsa과 바깥쪽에 있는 tblInsa는 같은 것 같지만, 사실은 서로 다른 인스턴스이다.
안쪽에 있는 buseo가 바깥쪽에 있는 a.buseo를 호출하였다. 이와 같이 안쪽과 바깥쪽이 연관지어진다고 해서 상관 서브 쿼리라고 부른다.

 

SELECT
	name AS 남자이름,
	height AS 남자키,
	weight AS 남자몸무게,
	couple AS 여자이름,
	(SELECT height FROM tblWomen WHERE name = tblMen.couple) AS 여자키,
	(SELECT weight FROM tblWomen WHERE name = tblMen.couple) AS 여자몸무게
FROM tblMen;

서브 쿼리가 서로 다른 위치에 있는 테이블을 한 번에 가져오는 게 가능해진다.
이처럼 컬럼리스트에 쓰여서 서로 관계된 값들을 연동하여 출력할 수 있다.

 

FROM절

서브 쿼리의 결과 테이블을 하나의 테이블이라고 생각하고 메인 쿼리를 실행한다.

이를 다른 말로 인라인 뷰(Inline View)라고 부른다.

 

인라인 뷰

SELECT
	*									--4.
FROM									--1.
	(
		SELECT name, buseo				--3.
		FROM tblInsa					--2.
	);

FROM절에 쿼리를 작성하는 인라인 뷰는 위와 같은 형태로 작성한다.

 

인라인 뷰의 컬럼 별칭

SELECT name, gender
FROM (SELECT name, substr(ssn, 1, 8) AS gender FROM tblInsa);

인라인 뷰의 컬럼 별칭은 바깐쪽 메인 쿼리에서 그대로 전달하여 사용한다.

 

식별자는 영어로 작성

SELECT
	name, height, couple,
	(SELECT height FROM tblWomen WHERE name = tblMen.couple) AS 여자키
FROM tblMen
	ORDER BY 여자키;

오라클은 식별자에 한글을 적지 않는다.
별칭을 구문의 일부로 들어오게 할 때에는 한글 사용을 피하는 게 좋다.

 

식별자의 중복 발생

SELECT
	name, height, couple,
	(SELECT height FROM tblWomen WHERE name = tblMen.couple) AS height2
FROM tblMen
	ORDER BY height2;

ORA-00960: ambiguous column naming in select list
Alias를 height로 하면 이름이 중복되어 모호한 컬럼 이름이 발견되었다는 오류가 발생한다.

height를 height2로 변경하였다.