[Basic] Subquery

1 서브쿼리

• 하나의 SQL 명령문(하위, subquery)의 결과를 다른 SQL(상위, main)에 전달. 그걸 연결하여 처리하는 방법.

SELECT name, position
FROM professor
WHERE position = (SELECT position
					FROM professor
        			WHERE name = '전은지');

• 전은지라는 교수의 직위를 전달
  => 그와 같은 직위의 교수 명단을 출력

2 단일행(Single-row) 서브쿼리

• 서브쿼리에서 단 하나의 행만 검색 => 메인 쿼리에 반환
• 주의: 행 갯수가 안맞으면 에러 뜬다
  => 서브쿼리의 결과로 하나의 행만 출력 되어야 함 + WHERE 절에서 단일행 비교연산자(=, >, >=, <, <=, <>)를 통해 비교해야 함
• 방법 2가지
  • 1 subquery에 하나의 행 반환 => 그 결과와 WHERE절에서
  • 2 subquery에 그룹함수(min, max) 등을 적용해 반환 => 그 결과와 WHERE절에서

방법 1

SELECT studno, name, grade
FROM student
WHERE grade = (SELECT grade
				FROM student
                WHERE userid = 'jun123');

이 구문을 보면, 서브쿼리에서 조건절을 통해 하나의 행으로 제약 => 그 행(grade)와 같은 grade를 출력

방법 2

SELECT studno, name, weight
FROM student
WHERE weight < (SELECT avg(weight)
				FROM student
                WHERE deptno = 101)
AND height > (SELECT avg(height)
				FROM student
                WHERE deptno = 101)

이 구문을 보면, 서브쿼리에서 그룹함수로 하나의 행(값)을 반환 => 그 값보다 작은 weight를 출력
그리고 2개의 조건을 이렇게 전달 가능

3 다중행(Multi-row) 서브쿼리

• subquery에서 반환되는 겨로가 행이 하나 이상일 때.
• 주의: 행 갯수가 안맞으면 에러 뜬다
  => WHERE 절에서 다중행 비교연산자(IN, ANY, SOME, ALL, EXISTS)를 통해 비교해야 함 but 단일 행 비교 연산자와 결합해서 사용 가능

3-1 IN

• 서브쿼리의 출력 결과 여러 행 중 하나라도 일치하면 메인쿼리 조건 절이 참이 된다.
  즉, '=' 연산자를 OR로 연결한 것과 같은 의미.

SELECT name, grade, deptno
FROM student
WHERE deptno IN (SELECT deptno
				FROM department
                WHERE college in (100, 102));

SELECT name, grade, deptno
FROM student
WHERE deptno = 101
OR deptno = 102;

3-2 ANY(SOME)

• 출력 결과 여러 행 중 하나라도 조건을 만족하면 메인쿼리 조건 절이 참이 된다.
  즉, >, < 같은 범위 비교도 가능
• ANY와 SOME은 같다.

SELECT studno, name, height
FROM student
WHERE height > ANY (SELECT height
					FROM student
                    WHERE grade = 4);

SELECT studno, name, height
FROM student
WHERE height > (SELECT min(height)
				FROM student
                WHERE grade = 4);

이 둘은 같다. 이유는 서브쿼리의 height에서 출력되는 다중 행 중 하나라도 조건을 만족하면 참이 된다, 즉 가장 작은 값보다 크기만 하면 참이 되기 때문.

3-3 ALL

• 출력 결과 여러 행을 모든 조건을 만족하면 메인쿼리 조건 절이 참이 된다.

SELECT studno, name, height
FROM student
WHERE height > ALL (SELECT height
					FROM student
                    WHERE grade = 4);

SELECT studno, name, height
FROM student
WHERE height > (SELECT max(height)
				FROM student
                WHERE grade = 4);

여기서는 max(height)과 같다. 왜냐하면 모든 조건을 만족하려면, 최댓값보다 크면 되기 때문.

3-5 EXISTS/NOT EXISTS

• EXISTS: 서브쿼리에서 검색된 결과가 하나라도 존재하면 메인쿼리 조건절이 참이 된다(즉, 서브쿼리 검색 결과가 존재하지 않을 때만 거짓)
• NOT EXISTS: 반대로, 서브쿼리의 검색 결과가 존재하지 않을 때, 메인쿼리의 조건절이 참이 된다.

SELECT profno, name, sal, comm
FROM professor
WHERE EXISTS (SELECT position
				FROM professor
                WHERE comm IS NOT NULL);

여기서 position에 NOT NULL이 하나라도 있으면 => 메인 쿼리를 전체 출력. subquery에서 not null인 행만 출력하는 게 아니라!!
position이 모두 NULL이면 => 메인 쿼리는 출력 아예 출력 X

SELECT 1 userid_exist
FROM dual
WHERE NOT EXISTS (SELECT userid
					FROM student
                    where userid = 'goodstudent');

여기서 dual은 1열 1행으로 1개의 스칼라값을 넣을 수 있는 공간. 그냥 값 확인하는 용도로 이용.
1 userid_exist는 1 AS userid_exist로, WHERE절이 TRUE면 그냥 1을 출력한다는 뜻이고, 그 칼럼의 이름이 userid_exist.
서브쿼리에 저 아이디가 없으면 => WHERE절이 TRUE이므로 => 1 출력
서브쿼리에 저 아이디가 있으면 => WHERE절이 FALSE이므로 => 출력 안됨

4 다중 컬럼(Multi-column) 서브쿼리

• 서브쿼리에서 여러 개의 칼럼 값을 검색 => 겁색 결과를 메인쿼리 조건절로 반환
• 주의: 컬럼 갯수가 안맞으면 에러 뜬다

4-1 PAIRWISE/UNPAIRWISE

• PAIRWISE: 비교대상 칼럼을 쌍으로 묶어서 행별로 비교
• UNPAIRWISE: 비교대상 칼럼을 각각 분리해서 개별적으로 비교 후 AND 연산
  => 각 칼럼이 동시에 만족 안해도 개별적으로 만족하는 경우도 참으로 출력
• ex) 각 학년별 몸무게가 최소인 학생을 출력한다.
  => PAIRWISE: 1학년 최저 52, 2학년 최저 70, 3학년 최저 72, 4학년 최저 42 이렇게 4개 출력
  => UNPAIRWISE: 저 4개 외에도, 2학년인데 몸무게가 52, 72, 42 중 하나에 맞으면 다 출력된다.

PAIRWISE

SELECT name, grade, weight
FROM student
WHERE (grade, weight) IN (SELECT grade, MIN(weight)
							FROM student
                            GROUP BY grade);

UNPAIRWISE

SELECT name, grade, weight
FROM student
WHERE grade IN (SELECT grade
				FROM student
                GROUP BY grade)
AND weight IN (SELECT MIN(weight)
					FROM student
                    GROUP BY grade);

5 상호연관 서브쿼리(하지마!)

• 서브쿼리의 결과값을 메인쿼리로 반환하는데, 서브쿼리를 돌리기 위해 다시 메인쿼리를 들고오기 => 성능 저하된다

SELECT name, deptno, height
FROM student s1
WHERE height > (SELECT AVG(height)
				FROM student s2
                WHERE s2.deptno = s1.deptno)

이처럼 서브쿼리의 조건절에 메인쿼리의 s1을 들고오기

6 실무에서 주의사항

• 1 메인과 서브의 불일치 에러
  • 1 '복수행 값 반환'와 '단일행 비교연산자' 사용
  • 2 반환되는 칼럼의 수와, 메인쿼리의 비교되는 칼럼 수 불일치
• 2 서브쿼리 내에서 ORDER BY절 사용 => ORDER BY는 메인쿼리 마지막에 1개만
• 3 SUBQUERY 결과가 NULL인 경우

7 Scalar Subquery

• 하나의 행, 하나의 열인 스칼라값을 돌리면서 반환하는 서브쿼리.
  => 소량의 데이터는 효과적이지만, 대량 데이터에서는 성능 저하
• 반환되는 값의 데이터형은, 서브쿼리에서 선택된 데이터 형과 일치해야 한다

SELECT employee_id, last_name, 
		(SELECT department_name
        FROM department d
        WHERE e.department_id = d.department_id
        ) AS department_name
FROM employees e
ORDER BY department;

• 서브쿼리가 외부커리의 각 행마다 실행되서 조건에 맞는(e.department_id = d.deparment_id)인 스칼라값을 반환. => 매번 검색, 들고와서, 연결을 반복 => 하나의 칼럼처럼 보인다.

SELECT e.employee_id, e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
ORDER BY department;

• 아래는 조인으로, 결과로만 보면 아래와 같다.
  하지만 아래는, 그냥 테이블을 통으로 연결한 것.
• SELECT LIST 외에도 WHERE절, ORDER BY 절, CASE 수식, 함수에도 다 사용 가능

SQL 함수 여러 개 처리 시: 맨 안쪽 함수부터 처리 => 처리 결과를 바깥쪽 함수로 넘김