[SQL] 서브쿼리

서브쿼리

 

: 메인 쿼리 내에 또 다른 쿼리문을 작성하는 것

: 서브쿼리는 괄호 안에 작성

: 주로 WHERE 절에 쓰임

: SELECT문에 작성하는 서브쿼리는 조인과 동일하며, 조인이 더 효율적

: FROM 절에서도 사용 가능

SELECT 칼럼명1, 칼럼명2 ...
FROM 테이블명
WHERE 조건문 (SELECT 칼럼명3, 칼럼명4
                           FROM 테이블명
                           WHERE 조건문);

---

[단일행 서브쿼리]

: 서브 쿼리의 결과가 단일 행

: 연산자 - =, <> (!=), >, >=, <, <= 

 

[예시1]

-- comm 테이블에서 ename이 WARD인 사람의 comm이 작은 ename과 comm 조회
--  서브쿼리로 작성
SELECT ename, comm
FROM emp
WHERE comm < (SELECT comm FROM emp WHERE ename='WARD');

-- join으로 나타낸 것
SELECT e1.ename, e1.comm
FROM emp e1 JOIN emp e2
WHERE e2.ename='WARD' AND e1.comm < e2.comm;

[예시2]

-- student, department 테이블을 이용하여 서진수 학생과 주전공이 동일한 학생들의 이름과 전공 조회
SELECT s.name, d.dname
FROM student s JOIN department d
ON s.deptno1=d.deptno
WHERE s.deptno1 = (SELECT deptno1 FROM student WHERE name='서진수');

[예시3]

-- professor, department 테이블을 이용하여 박원범 교수보다 나중에 입사한 사람의 이름, 입사일, 학과명 조회
SELECT p.name, p.hiredate, d.dname
FROM professor p 
JOIN department d ON p.deptno=d.deptno
WHERE p.hiredate > (SELECT hiredate FROM professor WHERE name='박원범');

[예시4]

-- 위 예시보다 아래 예시처럼 명시적으로 주는 것이 더 많이 사용됨
-- student 테이블에서 주전공이 201 학과(전자공학과)의 평균 몸무게보다 몸무게가 많은 학생들의 이름과 몸무게 조회
SELECT s.name, s.weight
FROM student s
WHERE s.weight > (SELECT AVG(weight) FROM student WHERE deptno1='201');

-- student 테이블에서 주전공이 전자공학과인 학과의 평균 몸무게보다 몸무게가 많은 학생들의 이름과 몸무게 조회
SELECT s.name, s.weight
FROM student s
WHERE s.weight > (SELECT AVG(st.weight) 
					FROM student st 
					JOIN department d ON d.deptno = st.deptno1 
					WHERE d.dname = '전자공학과')
ORDER BY 2;

[예시5]

-- gogak, gift 테이블을 이용하여 노트북을 받을 수 있는 고객의 이름, 포인트 조회
SELECT go.gname, go.point
FROM gogak go
WHERE go.point > (SELECT g_start FROM gift WHERE gname='노트북');

[예시6]

-- gogak, gift 테이블을 이용하여 노트북을 받을 수 있는 고객의 이름, 포인트 조회
SELECT go.gname, go.point
FROM gogak go
WHERE go.point > (SELECT g_start FROM gift WHERE gname='노트북');

[예시7]

-- emp, dept 테이블을 이용하여 sales 부서를 제외한 나머지 부서에 속한 직원의 사번, 이름, 부서명 조회
SELECT e.empno, e.ename, d.dname
FROM emp e JOIN dept d USING(deptno)
WHERE deptno <> (SELECT deptno FROM dept WHERE dname='SALES');

[예시8] 서브쿼리 2개 이용

-- student, exam_01, hakjum 테이블을 이용하여 학점이 A0인 학생의 학번, 이름, 점수 조회
-- USING도 사용 가능
-- between 사용 가능
SELECT s.studno, s.name, e.total
FROM student s JOIN exam_01 e ON s.studno=e.studno
WHERE e.total > (SELECT min_point FROM hakjum WHERE grade='A0')
				AND e.total < (SELECT min_point FROM hakjum WHERE grade='A+')
ORDER BY 3;

[예시9] 메인 쿼리의 테이블을 서브쿼리 내에서 사용

-- emp2 테이블에서 본인이 속한 부서의 평균 연봉보다 적게 받는 직원의 이름, 연봉, 부서명 조회
-- 단일행
SELECT name, pay, d.dname
FROM emp2 e JOIN dept2 d ON e.deptno = d.dcode
WHERE pay < (SELECT AVG(pay) FROM emp2 WHERE deptno=e.deptno);

---

[다중행 서브쿼리]

: 서브 쿼리 결과가 여러 행

: 연산자 - in, exists, > any, > any, <all, >all

: any - 하나라도 만족하면 true >> 데이터들 중 하나라도 만족하면 출력, all - 모두 만족해야 true >> 데이터 중 모든 것이 만족해야 함

 

[예시1]

-- emp2 테이블을 이용하여 '과장' 직급의 최소 연봉자보다 연봉이 높은 직원의 사번, 이름, 연봉 조회
-- 단일행
SELECT empno, name, pay
FROM emp2
WHERE pay > (SELECT MIN(pay) FROM emp2 WHERE position='과장');

-- 다중행
SELECT empno, name, pay
FROM emp2
WHERE pay >any (SELECT pay FROM emp2 WHERE position='과장')
ORDER BY 3;

[예시2]

-- student 테이블에서 학년별로 키가 가장 큰 학생의 이름과 학년 조회
SELECT name, grade, height
FROM student
WHERE (grade, height) IN (SELECT grade, max(height) FROM student GROUP BY grade)
ORDER BY 2;

[예시3]

-- student 테이블에서 학년이 1학년인 학생중에 몸무게가 가장 적은 학생보다 몸무게가 적은 학생의 이름, 학년, 몸무게 조회
SELECT name, grade, weight
FROM student
WHERE weight <all (SELECT weight FROM student WHERE grade=1);

[예시4]

-- emp2, dept2 테이블을 이용해 각 부서별 평균 연봉을 구하고 평균 연봉이 가장 적은 부서의 평균 연봉보다 많이 받는 직원들의 직원명, 부서명, 연봉 조회
SELECT e.name, d.dname, e.pay
FROM emp2 e JOIN dept2 d ON e.deptno=d.dcode
WHERE e.pay >any (SELECT AVG(pay) FROM emp2 GROUP BY deptno);

[예시5]

-- student, exam_01, department 테이블에서 같은 학과 같은 학년 학생의 평균 점수보다 점수가 높은 학생의 학번, 이름, 학과, 학년, 점수 조회
SELECT s.studno, s.name, d.dname, s.grade, e.total
FROM student s JOIN department d ON s.deptno1=d.deptno
JOIN exam_01 e USING(studno)
WHERE e.total >any (SELECT AVG(e1.total) 
					FROM student s1 JOIN exam_01 e1 USING(studno) 
					GROUP BY grade, deptno1)
ORDER BY s.deptno1, s.grade;

[예시6] Exists

-- student, professor 테이블에서 담당 학생이 있는 교수들의 교수번호, 교수명 조회
SELECT DISTINCT s.profno, p.name
FROM student s JOIN professor p USING(profno)
WHERE profno IS NOT NULL;

SELECT p.profno, p.name
FROM professor p
WHERE EXISTS (SELECT * FROM student WHERE profno=p.profno);

SELECT e.profno, p.name
FROM (SELECT DISTINCT profno FROM student) e JOIN professor p USING(profno);


-- student, professor 테이블에서 담당 학생이 없는 교수들의 교수번호 교수명 조회
SELECT p.profno, p.name
FROM professor p
WHERE NOT EXISTS(SELECT * FROM student s WHERE p.profno=s.profno);