sql-study-day13

BEBECO·2022년 12월 5일
Do it! 오라클로 배우는 데이터베이스 입문db
0

Do it! 오라클로 배우는 데이터베이스 입문

목록 보기
15/21

09 - 1 서브쿼리

  • 서브쿼리는 SQL문을 실행하는 데 필요한 데이터를 추가로 조회하기 위해 SQL문 내부에서 사용하는 SELECT문을 의미한다.
  • 서브퀴리의 결과 값을 사용하여 기능을 수행하는 영역은메인쿼리라고 부른다.
// 메인쿼리
SELECT 조회할 열
FROM 조회할 테이블
WHERE 조건식 ( SELECT 조회할 열 // 서브쿼리
							   FROM 조회할 테이블
								WHERE 조건식 )
  • 서브쿼리 작성의 핵심은 주어진 문제를 어떻게 SELECT문으로 나누어 처리할지를 결정하는 데에 있다.
SELECT *
FROM EMP
WHERE SAL > (SELECT SAL
							 FROM EMP
							WHERE ENAME = 'JONES');

서브쿼리의 특징

  1. 서브쿼리는 연산자와 같은 비교 또는 조회 대상의 오른쪽에 놓이면 괄호 ()로 묶어서 사용한다.
  2. 특수한 몇몇 경우를 제외한 대부분의 서브쿼리에서는 ORDER BY절을 사용할 수 없다.
  3. 서브쿼리의 SELECT절에 명시한 열은 메인쿼리의 비교 대상과 같은 자료형과 같은 개수로 지정해야 한다. 즉 메인쿼리의 비교 대상 데이터가 하나라면 서브쿼리의 SELECT절 역시 같은 자료형인 열을 하나 지정해야 한다.
  4. 서브쿼리에 있는 SELECT문의 결과 행 수는 함께 사용하는 메인쿼리의 연산자 종류와 호환 가능해야 한다. 예를 들어 메인쿼리에 사용한 연산자가 단 하나의 데이터로만 연산이 가능한 연산자라면 서브쿼리의 결과 행 수는 반드시 하나여야 한다. 이 내용은 ‘단일행 서브쿼리 및 다중행 서브쿼리’에서 자세히 살펴보겠다.

09 - 2 실행 결과가 하나인 단일행 서브쿼리

  • 단일행 서브쿼리는 실행 결과가 단 하나의 행으로 나오는 서브쿼리를 뜻한다. 서브쿼리에서 출력되는 결과가 하나이므로 메인쿼리와 서브쿼리 결과는 다음과 같이 단일행 연산자를 사용하여 비교한다.
단일행 연산자
> ≥ = ≤ < <> ^= ! =
초과 이상 같음 이하 미만 같지 않음
  • 이름으로 단일행 서브쿼리를 사용하는 것은 나중에 문제가 될 수 있다. 사람 이름뿐만 아니라 같은 데이터가 여러 개 존재하는 열의 경우에는 중복이 가능하기 때문에 주의해야 한다.
  • 이름을 가진 사원이 여러 명 있었다면 대소 비교 연산자를 사용한 서브쿼리는 오류가 발생하고 실행되지 못한다. 이와 같이 서브쿼리의 결과로 여러 행을 반환할 때에는 다중행 서브쿼리를 사용해야 한다.

단일행 서브쿼리와 날짜형 데이터

  • 단일행 서브쿼리는 서브쿼리 결과 값이 날짜(DATE) 자료형일 때도 사용할 수 있다.
SELECT *
FROM EMP
WHERE HIREDATE < (SELECT HIREDATE
										FROM EMP
									 WHERE ENAME = 'SCOTT');

단일행 서브쿼리와 함수

  • 서브쿼리에서 특정 함수를 사용한 결과 값이 하나일 때 역시 단일행 서브쿼리로서 사용 가능하다.
SELECT E.EMPNO, E.ENAME, E.JOB, E.SAL, D.DEPTNO, D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND E.DEPTNO = 20
AND E.SAL > (SELECT AVG(SAL)
							FROM EMP);

09 - 3 실행 결과가 여러 개인 다중행 서브쿼리

  • 다중행 서브쿼리는 실행 결과 행이 여러 개로 나오는 서브쿼리를 가리킨다.
다중행 연산자설명
IN메인쿼리의 데이터가 서브쿼리의 결과 중 하나라도 일치한 데이터가 있다면 true
ANY, SOME메인쿼리의 조건식을 만족하는 서브쿼리의 결과가 하나 이상이면 true
ALL메인쿼리의 조건식을 서브쿼리의 결과 모두가 만족하면 true
EXISTS서브쿼리의 결과가 존재하면(즉, 행이 1개 이상일 경우) true

IN

  • 부서별 최고 급여 데이터를 먼저 구하고, 이 데이터와 일치하는 메인쿼리 데이터를 IN 연산자를 통해 선별해 낼 수 있다.
SELECT *
FROM EMP
WHERE SAL IN (SELECT MAX(SAL)
                FROM EMP
              GROUP BY DEPTNO);

  • 다중행 연산자 중 IN 연산자는 가장 자주 사용하는 연산자이므로 사용법을 반드시 기억해 두자.

ANY, SOME 연산자

  • ANY, SOME 연산자는 서브쿼리가 반환한 여러 결과 값 중 메인쿼리와 조건식을 사용한 결과가 하나라도 true라면 메인쿼리 조건식을 true로 반환해 주는 연산자이다.
  • 메인쿼리와 값을 비교할 때 ANY 및 SOME 연산자를 등가 비교 연산자(=)와 함께 사용하면 IN 연산자와 정확히 같은 기능을 수행한다.
SELECT *
FROM EMP
WHERE SAL = ANY (SELECT MAX(SAL)
									FROM EMP
									GROUP BY DEPTNO);

  • 등가 비교 연산자가 아닌 대소 비교 연산자를 ANY 연산자와 함께 사용하는 경우는 조금 생각을 해야 한다.
SELECT *
FROM EMP
WHERE SAL < ANY (SELECT SAL
									FROM EMP
									WHERE DEPTNO = 30)
ORDER BY SAL, EMPNO;

// < ANY 연산자를 사용한 경우
SELECT *
FROM EMP
WHERE SAL < ANY (SELECT SAL
								FROM EMP
								WHERE DEPTNO = 30)
ORDER BY SAL, EMPNO;
// 서브쿼리에 MAX 함수를 사용한 경우
SELECT *
FROM EMP
WHERE SAL < (SELECT MAX(SAL)
							FROM EMP
							WHERE DEPTNO = 30)
ORDER BY SAL, EMPNO;

ALL 연산자

ANY 및 SOME과 달리 ALL 연산자는 서브쿼리의 모든 결과가 조건식에 맞아떨어져야만 메인쿼리의 조건식이 true가 되는 연산자이다.

// 부서 번호가 30번인 사원들의 최소 급여보다 더 적은 급여를 받는 사원 출력하기
SELECT *
FROM EMP
WHERE SAL < ALL (SELECT SAL
									FROM EMP
								WHERE DEPTNO = 30);

EXISTS 연산자

EXISTS 연산자는 조금 특이한 연산자인데 서브쿼리에 값이 하나 이상 존재하면 조건식이 모두 true, 존재하지 않으면 false가 되는 연산자이다.

// 서브쿼리 결과 값이 존재하는 경우
SELECT *
FROM EMP
WHERE EXISTS (SELECT DNAME
                FROM DEPT
                WHERE DEPTNO = 10);

// 서브쿼리 결과 값이 존재하지 않는 경우
SELECT *
FROM EMP
WHERE EXISTS (SELECT DNAME
                FROM DEPT
                WHERE DEPTNO = 50);

  • EXISTS 연산자는 다른 다중행 연산자에 비해 그리 자주 사용하는 편은 아니지만, 특정 서브쿼리 결과 값의 존재 유무를 통해 메인쿼리의 데이터 노출 여부를 결정해야 할 때 간혹 사용한다.

09 - 4 비교할 열이 여러 개인 다중열 서브쿼리

  • 다중열 서브쿼리는 서브쿼리의 SELECT절에 비교할 데이터를 여러 개 지정하는 방식이다.
  • 메인쿼리에 비교할 열을 묶어 명시하고, 서브쿼리에서는 괄호로 묶은 데이터와 같은 자료형 데이터를 SELECT절에 명시하여 사용할 수 있다.
// 다중열 서브쿼리 사용하기
SELECT *
FROM EMP
WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, MAX(SAL)
                                FROM EMP
                                GROUP BY DEPTNO);

09 - 5 FROM절에 사용하는 서브쿼리와 WITH절

  • FROM절에 사용하는 서브쿼리는 인라인 뷰라고도 부른다.
  • 인라인 뷰는 특정 테이블 전체가 아닌 SELECT문을 통해 일부 데이터를 먼저 추출해 온 후 별칭을 주어 사용할 수 있다.
// 인라인 뷰 사용하기
SELECT E10.EMPNO, E10.ENAME, E10.DEPTNO, D.DNAME, D.LOC
FROM (SELECT * FROM EMP WHERE DEPTNO = 10) E10,
    (SELECT * FROM DEPT) D
    WHERE E10.DEPTNO = D.DEPTNO;

하지만 FROM절에 너무 많은 서브쿼리를 지정하면 가독성이나 성능이 떨어질 수 있기 때문에 경우에 따라 WITH절을 사용하기도 한다.

WITH절은 메인쿼리가 될 SELECT문 안에서 사용할 서브쿼리와 별칭을 먼저 지정한 후 메인쿼리에서 사용한다.

// 기본 형식
WITH
[별칭1] AS (SELECT문 1),
[별칭2] AS (SELECT문 2),
...
[별칭n] AS (SELECT문 n)
SELECT
FROM 별칭1, 별칭2, 별칭3
...
// WITH절 사용하기
WITH
E10 AS (SELECT * FROM EMP WHERE DEPTNO = 10),
D   AS (SELECT * FROM DEPT)
SELECT E10.EMPNO, E10.ENAME, E10.DEPTNO, D.DNAME, D.LOC
FROM E10, D
WHERE E10.DEPTNO = D.DEPTNO;

09 - 6 SELECT절에 사용하는 서브쿼리

  • 서브쿼리는 SELECT절에도 사용할 수 있다. 흔히 스칼라 서브쿼리라고 부르는 이 서브쿼리는 SELECT절에 하나의 열 영역으로서 결과를 출력할 수 있다.
// SELECT절에 서브쿼리 사용하기
SELECT EMPNO, ENAME, JOB, SAL,
        (SELECT GRADE
            FROM SALGRADE
            WHERE E.SAL BETWEEN LOSAL AND HISAL) AS SALGRADE,
            DEPTNO,
            (SELECT DNAME
            FROM DEPT
            WHERE E.DEPTNO = DEPT.DEPTNO) AS DNAME
            FROM EMP E;

SELECT절에 명시하는 서브쿼리는 반드시 하나의 결과만 반환하도록 작성해 주어야 한다는 것도 꼭 기억하자.

잊기 전에 한 번 더!

  • Q1. 전체 사원 중 ALLEN과 같은 직책(JOB)인 사원들의 사원 정보, 부서 정보를 다음과 같이 출력하는 SQL문을 작성하세요.
SELECT E.JOB, E.EMPNO, E.ENAME, E.SAL, E.DEPTNO, D.DNAME
  FROM EMP E, DEPT D
 WHERE E.DEPTNO = D.DEPTNO
   AND JOB = (SELECT JOB
                FROM EMP
               WHERE ENAME = 'ALLEN');
  • Q2. 전체 사원의 평균 급여(SAL)보다 높은 급여를 받는 사원들의 사원 정보, 부서 정보, 급여 등급 정보를 출력하는 SQL문을 작성하세요(단 출력할 때 급여가 많은 순으로 정렬하되 급여가 같을 경우에는 사원 번호를 기준으로 오름차순으로 정렬하세요).
SELECT E.EMPNO, E.ENAME, D.DNAME, E.HIREDATE, D.LOC, E.SAL, S.GRADE
  FROM EMP E, DEPT D, SALGRADE S
 WHERE E.DEPTNO = D.DEPTNO
   AND E.SAL BETWEEN S.LOSAL AND S.HISAL
   AND SAL > (SELECT AVG(SAL)
                FROM EMP)
ORDER BY E.SAL DESC, E.EMPNO;
  • Q3. 10번 부서에 근무하는 사원 중 30번 부서에는 존재하지 않는 직책을 가진 사원들의 사원 정보, 부서 정보를 다음과 같이 출력하는 SQL문을 작성하세요.
SELECT E.EMPNO, E.ENAME, E.JOB, E.DEPTNO, D.DNAME, D.LOC
  FROM EMP E, DEPT D
 WHERE E.DEPTNO = D.DEPTNO
   AND E.DEPTNO = 10
   AND JOB NOT IN (SELECT DISTINCT JOB
                     FROM EMP
                    WHERE DEPTNO = 30);
  • Q4. 직책인 SALESMAN인 사람들의 최고 급여보다 높은 급여를 받는 사원들의 사원 정보, 급여 등급 정보를 다음과 같이 출력하는 SQL문을 작성하세요(단 서브쿼리를 활욜할 때 다중행 함수를 사용하는 방법과 사용하지 않는 방법을 통해 사원 번호를 기준으로 오름차순으로 정렬하세요).
-- 다중행 함수 사용하지 않는 방법
SELECT E.EMPNO, E.ENAME, E.SAL, S.GRADE
  FROM EMP E, SALGRADE S
 WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
   AND SAL > (SELECT MAX(SAL)
                FROM EMP
               WHERE JOB = 'SALESMAN')
ORDER BY E.EMPNO; 

--다중행 함수 사용하는 방법
SELECT E.EMPNO, E.ENAME, E.SAL, S.GRADE
  FROM EMP E, SALGRADE S
 WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
   AND SAL > ALL (SELECT DISTINCT SAL
                    FROM EMP
                   WHERE JOB = 'SALESMAN')
ORDER BY E.EMPNO;
profile
BEBECO
블로그 이사 중 ⭐️ || https://bebeco.tistory.com/
이전 포스트

20220726 스터디 발표

0개의 댓글