09 서브쿼리(Subquery) 서브쿼리의 개념 서브쿼리의 종류 데이터베이스 실무에서 서브쿼리 사용시 주의 사항.

Presentation on theme: "09 서브쿼리(Subquery) 서브쿼리의 개념 서브쿼리의 종류 데이터베이스 실무에서 서브쿼리 사용시 주의 사항."— Presentation transcript:

1 09 서브쿼리(Subquery) 서브쿼리의 개념 서브쿼리의 종류 데이터베이스 실무에서 서브쿼리 사용시 주의 사항

2 서브쿼리 개요 하나의 SQL 명령문의 결과를 다른 SQL 명령문에 전달하기 위해 두 개 이상의 SQL 명령문을 하나의 SQL명령문으로 연결하여 처리하는 방법 서브쿼리의 필요성 예 : 교수 테이블에서 ‘전은지’ 교수와 직급이 동일한 모든 교수의 이름 검색 처리방식 1. 교수 테이블에서 ‘전은지’ 교수의 직급 검색 SQL 명령문 실행 2. 교수 테이블의 직급 칼럼에서 1 에서 얻은 결과 값과 동일한 직급을 가진 교수 검색 명령문 실행 1, 2 SQL 명령문 처리과정을 통합하여 하나의 SQL 명령문으로 처리 9장. 서브쿼리

3 서브쿼리의 개념 개요 서브쿼리 필요성 - 계속 처리과정 ② ① 메인쿼리(main query)
1. 서브쿼리는 메인쿼리가 실행되기 전에 한번씩 실행됨 2. 서브쿼리에서 실행된 결과가 메인 쿼리에 전달되어 최종적인 결과를 출력 서브쿼리를 이용한 교수명과 직급 검색 SELECT name, position FROM professor WHERE position ② 메인쿼리(main query) = (SELECT positon FROM professor WHERE name = ‘전은지’); ① 서브쿼리(subquery) 9장. 서브쿼리

4 서브쿼리의 개념 ① ② ① ① ② ② ③ ③ 서브쿼리를 사용하지 않은 경우 서브쿼리를 사용한 경우
교수 테이블에서 ‘전은지’ 교수의 직급 검색 SELECT position FROM professor WHERE name = ‘전은지’; 서브쿼리를 이용하여 교수 이름과 직급을 동시 검색 SELECT name, position FROM professor WHERE position ① ② 결과 전달 ① = (SELECT positon FROM professor WHERE name = ‘전은지’); PROFNO NAME … POSITION 9901 김도훈 교수 9902 이재우 조교수 ……… 9907 전은지 전임강사 PROFNO NAME POSITION 9901 김도훈 교수 9902 이재우 조교수 ……… …… 9904 염일웅 전임강사 9907 전은지 PROFNO NAME POSITION 9901 김도훈 교수 9902 이재우 조교수 ……… …… 9904 염일웅 전임강사 9907 전은지 교수 테이블에서 ‘전임강사’ 직급의 교수 검색 SELECT name, position FROM professor WHERE position = ‘전임강사’; ① ② ② ③ ③ NAME POSITION 염일웅 전임강사 전은지 NAME POSITION 염일웅 전임강사 전은지 9장. 서브쿼리

5 단일행 서브쿼리 개요 서브쿼리에서 단 하나의 행만을 검색하여 메인쿼리에 반환하는 질의문
메인쿼리의 WHERE 절에서 서브쿼리의 결과와 비교할 경우에는 반드시 단일행 비교 연산자 중 하나만 사용해야함 단일 행 비교 연산자 : =, >, >=, <, <>, <= 서브쿼리의 결과로 하나의 행만이 출력되어야 함 방법 1. 서브쿼리의 조건절에서 기본 키나 고유 키를 ‘=‘ 비교 하는 방식 방법 2. 서브쿼리의 SELECT 절에서 전체 집합을 대상으로 그룹 함수 사용 9장. 서브쿼리

6 ‘=’ 연산자를 이용한 단일행 서브쿼리 9장. 서브쿼리

7 ‘<’ 연산자를 이용한 단일행 서브쿼리
9장. 서브쿼리

8 단일행 서브쿼리 9장. 서브쿼리

9 다중 행 서브쿼리 개요 서브쿼리에서 반환되는 결과 행이 하나 이상일 때 사용하는 서브쿼리
메인쿼리의 WHERE 절에서 서브쿼리의 결과와 비교할 경우에는 다중 행 비교 연산자 를 사용하여 비교 다중 행 비교 연산자 : IN, ANY, SOM, ALL, EXISTS 다중 행 비교 연산자는 단일 행 비교 연산자와 결합하여 사용 가능 9장. 서브쿼리

10 IN 연산자를 이용한 다중 행 서브쿼리 개요 메인쿼리의 비교 조건에서 서브쿼리의 출력 결과와 하나라도 일하면 메인쿼리 조건절이 참이 되는 연산자 ‘=‘ 연산자를 OR로 연결한 것과 같은 의미 IN 연산자를 이용한 서브쿼리의 예 SELECT name, grade, deptno FROM student WHERE deptno IN (SELECT deptno FROM department WHERE college = 100); NAME … GRADE DEPTNO 전인하 4 101 박미경 1 지은경 2 이광훈 ... 오유석 102 = SELECT name, grade, deptno FROM student WHERE deptno = 101 OR deptno = 102 9장. 서브쿼리

11 IN 연산자를 이용한 다중 행 서브쿼리 9장. 서브쿼리

12 ANY 연산자를 이용한 다중 행 서브쿼리 개요 =
메인쿼리의 비교 조건에서 서브쿼리의 출력 결과와 하나라도 일치하면 메인쿼리 조건절이 참이 되는 연산자 ‘>,<‘ 등과 같은 범위 비교도 가능 ANY 연산자를 이용한 서브쿼리의 예 SELECT studno, name, height FROM student WHERE height > ANY (SELECT height WHERE grade = ‘4’); NAME … GRADE HEIGHT 전인하 4 176 김영균 3 172 오유석 177 …… 이광훈 ... 175 류민정 2 162 = ANY SELECT name, grade, height FROM student WHERE height > (SELECT MIN(height) FROM student WHERE grade = ‘4’ ); 학생의 키가 176, 177, 175 중 최소인 175보다 크면 참 9장. 서브쿼리

13 ANY 연산자를 이용한 다중 행 서브쿼리 9장. 서브쿼리

14 ALL 연산자를 이용한 다중 행 서브쿼리 개요 =
메인쿼리의 비교 조건에서 서브쿼리의 검색 결과와 모두 일치하면 메인쿼리 조건절이 참이 되는 연산자 ANY 연산자와 차이점 ‘ >ANY’ : 서브쿼리 결과 중에서 최소 값보다 크면 메인쿼리의 비교 조건이 참 ‘>ALL’ : 서브쿼리의 결과 중에서 최대 값보다 크면 메인쿼리의 비교 조건이 참 SELECT studno, name, height FROM student WHERE height > ALL (SELECT height WHERE grade = ‘4’); NAME … GRADE HEIGHT 전인하 4 176 김영균 3 172 오유석 177 …… 이광훈 ... 175 류민정 2 162 = ALL SELECT name, grade, height FROM student WHERE height > (SELECT MAX(height) FROM student WHERE grade = ‘4’ ); 학생의 키가 176, 177, 175 중 최대인 177보다 크면 참 9장. 서브쿼리

15 ALL 연산자를 이용한 다중 행 서브쿼리 9장. 서브쿼리

16 EXISTS 연산자를 이용한 다중 행 서브쿼리
개요 서브쿼리에서 검색된 결과가 하나라도 존재하면 메인쿼리 조건절이 참이 되는 연산자 서브쿼리에서 검색된 결과가 존재하지 않으면 메인쿼리의 조건절은 거짓 ‘선택된 레코드가 없습니다’ 라는 메시지 출력 NOT EXISTS EXISTS 와 상반되는 연산자 서브쿼리에서 검색된 결과가 하나도 존재하지 않으면 메인쿼리의 조건절이 참이 되는 연산자 9장. 서브쿼리

17 EXISTS 연산자를 이용한 다중 행 서브쿼리
SELECT profno, name, sal, comm, … FROM professor WHERE ② 아니오 (①의 결과가 없는 경우) EXISTS (SELECT positon FROM professor WHERE comm IS NOT NULL) ; ①의 결과가 존재하는가? ① 예 (①의 결과가 한 행이라도 있는 경우) ②의 결과값은? ②의 결과값은? 선택된 레코드가 없습니다 PROFNO NAME … POSITION COMM 9901 김도훈 교수 20 9902 이재우 조교수 9903 성연희 15 ……… …… 9907 전은지 전임강사 9908 남은혁 부교수 17 9장. 서브쿼리

18 EXISTS 연산자를 이용한 다중 행 서브쿼리
9장. 서브쿼리

19 NOT EXISTS 연산자를 이용한 다중 행 서브쿼리
9장. 서브쿼리

20 다중 컬럼 서브쿼리 개요 서브쿼리에서 여러 개의 칼럼 값을 검색하여 메인쿼리의 조건절과 비교하는 서브쿼리
메인쿼리의 조건절에서도 서브쿼리의 칼럼 수만큼 지정해야 함 종류 PAIRWISE : 칼럼을 쌍으로 묶어서 동시에 비교하는 방식 UNPAIRWISE : 칼럼별로 나누어서 비교한 후, AND 연산을 하는 방식 PAIRWISE, UNPAIRWISE 서브쿼리 비교  PAIRWISE 비교방법  UNPAIRWISE 비교 방법 9장. 서브쿼리

21 PAIRWISE 다중 칼럼 서브쿼리 개요 메인쿼리와 서브쿼리의 비교 대상 칼럼을 쌍으로 묶어서 행별로 비교하는 방법
메인쿼리와 서브쿼리에서 비교하는 칼럼의 수는 반드시 동일해야 함 9장. 서브쿼리

22 PAIRWISE 다중 칼럼 서브쿼리 9장. 서브쿼리

23 UNPAIRWISE 다중 칼럼 서브쿼리 개요
메인쿼리와 서브쿼리의 비교 대상 칼럼을 분리하여 개별적으로 비교한 후 AND 연산에 의해 최종 결과를 출력 각 칼럼이 동시에 만족하지 않더라도 개별적으로 만족하는 경우에는 비교 조건이 참이 되어 결과를 출력 가능 9장. 서브쿼리

24 UNPAIRWISE 다중 칼럼 서브쿼리 9장. 서브쿼리

25 상호연관 서브쿼리 개요 메인쿼리절과 서브쿼리간에 검색 결과를 교환하는 서브쿼리
메인쿼리와 서브쿼리간의 결과를 교환하기 위하여 서브쿼리의 WHERE 조건절에서 메인쿼리의 테이블과 연결 주의 행을 비교할 때마다 결과를 메인으로 반환하는 관계로 처리 성능이 저하될 수 있음 9장. 서브쿼리

26 상호연관 서브쿼리 책 267 Page 9장. 서브쿼리

27 실무에서 서브쿼리 사용시 주의사항 단일행 서브쿼리에서 오류가 발생하는 경우
복수 행 값을 반환하는 서브쿼리와 단일행 비교연산자 함께 사용하는 경우 반환되는 칼럼의 수와 메인쿼리에서 비교되는 칼럼 수가 일치하지 않는 경우 9장. 서브쿼리

28 실무에서 서브쿼리 사용시 주의사항 메인쿼리와 서브쿼리 칼럼의 수가 일치하지 않는 경우 9장. 서브쿼리

29 실무에서 서브쿼리 사용시 주의사항 ORDER BY 절 사용 서브쿼리 내에서 ORDER BY 절 사용하면 오류 발생
9장. 서브쿼리

30 실무에서 서브쿼리 사용시 주의사항 ORDER BY 절 사용 9장. 서브쿼리

31 실무에서 서브쿼리 사용시 주의사항 서브쿼리의 결과가 NULL인 경우 9장. 서브쿼리

32 실습 학과별 최소키 보다 큰 학생의 이름, 학과번호ㅡ 학번, 키를 출력하는 JDBC 프로그램을 작성하시오. 9장. 서브쿼리

33 WITH Clause 최신 SQL 기능 질의 결과의 재사용 구문 WITH 테이블 이름 AS ( SELECT … FROM …
VIEW : 영구적 임시 데이블 생성 WITH Clause : 한시적 임시 테이블 생성 구문 WITH 테이블 이름 AS ( SELECT … FROM … WHERE ) WITH 절로 정의된 테이블은 일반 테이블과 동일한 기능을 제공한다. 질의문이 간결해지고 이해하기 쉬움. 9장. 서브쿼리

34 WITH Clause 김도훈 교수 입사일 보다 늦게 입사한 교수의 이름 또는 늦게 출생한 학생의 이름을 출력하시오.
Join 형태 (select p1.name from professor p1, professor p2 where p2.name = ‘김도훈’ and p1.hiredate > p2.hiredate) UNION (select s.name from student s, professor p2 where p2.name = ‘김도훈’ and s.birthdate > p2.hiredate) 9장. 서브쿼리

35 where hiredate > ANY (select hiredate where name = ‘김도훈’) ) UNION
SubQuery 형태 ( select name from professor where hiredate > ANY (select hiredate where name = ‘김도훈’) ) UNION from student where birthdate > ANY (select hiredate 9장. 서브쿼리

36 WITH Clause WITH clause 형태 WITH kimhire AS ( SELECT hiredate
FROM professor WHERE name = ‘김도훈’) (SELECT name FROM professor, kimhire WHERE professor.hiredate > kimhire.hiredate ) UNION ( SELECT name FROM student, kimhire WHERE student.birthdate > kimhire.hiredate) 9장. 서브쿼리

37 WITH Clause 다음 질의를 WITH 절을 이용하여 재작성하여 JDBC를 이용하여수행하시오. 9장. 서브쿼리