[데이터베이스] 서브쿼리

서브쿼리

---

안녕하세요 강정호입니다. 오늘은 서브쿼리에 대해 실습 위주로 알아보겠습니다.

주요 용어 설명은 잘 정리되어 있는 링크로 대신 하고 오늘은 주로 쿼리문 위주로 공부해 볼게요!

1. NULL을 포함하는 쿼리문

SELECT Fname, Lname FROM EMPLOYEE WHERE Super_ssn IS NULL;

- EMPLOYEE 테이블에서 Super_ssn이 NULL인 직원의 Fname, Lname 값을 구하여라.

결과는 다음과 같이 나왔습니다.

---

2. 서브쿼리문(Nested Query)

서브쿼리란? 서브쿼리 정의

- 서브쿼리가 다 같은 서브쿼리로 알고 있었으나 쿼리가 위치한 곳에 따라 다르게 불리어진다. SELECT 문 뒤(스칼라 쿼리문), FROM 절 뒤(인라인 뷰), WHERE 절 뒤(서브쿼리) 라는 각각의 명칭이 있다.

SQL IN 연산자란?

- SQL IN 연산자 : IN 연산자

- SQL IN 연산자 실습 : IN 연산자 실습

요약해서 말하자면 IN 연산자는 Where 뒤에 여러 개의 조건문이 OR로 나열된 것과 같다.

 

위와 같이 IN을 사용하여 Germany, France, UK의 값만 추출하였다.

IN 연산자를 사용한 서브 쿼리문

SELECT DISTINCT Pnumber
FROM PROJECT
WHERE Pnumber IN 
        (SELECT Pnumber
         FROM PROJECT, DEPARTMENT, EMPLOYEE
         WHERE Dnum = Dnumber AND
               Mgr_ssn = Ssn AND Lname = 'Smith')
      OR
      Pnumber IN
      (SELECT Pno FROM WORKS_ON, EMPLOYEE
       WHERE Essn = Ssn AND Lname = 'Smith');

이 쿼리문은 Pnumber(프로젝트 번호)를 구하는 쿼리문이다. Pnumber를 2개의 서브쿼리문에서 가져온다. 첫 번째 서브쿼리문은 PROJECT, DEPARTMENT, EMPLOYEE를 Inner Join 하여 가져온다. 두 번째 서브쿼리문은 WORKS_ON, EMPLOYEE 테이블을 Inner Join 하여 가져온다.

2개의 서브쿼리문에서 나온 Pnumber에 해당하는 데이터를 PROJECT 테이블에서 추출한다.

Tuple의 값을 이용한 IN 서브쿼리문

SELECT DISTINCT Essn
FROM WORKS_ON
WHERE (Pno, Hours) IN (SELECT Pno, Hours 
                       FROM WORKS_ON
                       WHERE Essn = '123456789');

WORKS_ON 테이블에서 중복되지 않는 Essn을 가져온다. 어떤 Essn을 가져오냐 하면 WORKS_ON 테이블에서 Pno, Hours가 Essn = '123456789' 에 해당하는 것만 가져온다.

서브쿼리에서 이미 Essn = '123456789'에 해당하는 것을 추출하게 하였기 때문에 결과도 다음과 같다.

별칭(Aliase)를 이용하여 잠재적인 에러와 모호성을 방지한 서브쿼리문

SELECT E.Fname, E.Lname
FROM EMPLOYEE AS E
WHERE E.Ssn IN (SELECT Essn
                FROM DEPENDENT AS D
                WHERE E.Fname=D.Dependent_name
                AND E.Sex=D.Sex);

EMPLOYEE와 DEPENDENT 테이블을 Inner Join 하는 서브쿼리문을 확인할 수 있다. 별칭을 사용한 이유는 EMPLOYEE 테이블에 Sex 컬럼과 DEPENDENT 테이블의 Sex 컬럼을 구별하기 위한 것이다.

3. 상호연관 서브쿼리문(Correlated Nested Queries)

EXISTS 연산자

- EXISTS 연산자는 Subquery 데이터가 존재하는가를 체크해 존재 여부(TRUE,FALSE)를 결과로 반환한다.

- EXISTS절에는 반드시 메인 쿼리와 연결이 되는 조인 조건을 가지고 있어야 한다.

- subquery에서 결과 행을 찾으면, inner query 수행을 중단하고 TRUE를 반환한다.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

-- 아래 예처럼 emp 테이블을 통해 사원들이 속한 부서번호의 정보만 조회하는 경우 -- 추출하고자 하는 대상은 dept 테이블이지만 emp 테이블과 조인하여 부서번호를 -- 체크해야 한다. -- 두 테이블의 관계가 1 : M 이므로 불필요하게 EMP 테이블을 모두 액세스하고 -- DISTINCT로 중복 제거를 한다. SELECT DISTINCT d.deptno, d.dname   FROM dept d, emp e  WHERE d.deptno = e.deptno;       -- EXISTS를 사용하는 Subquery로 변경 -- 추출하고자 하는 대상만을 FROM절에 놓고 emp테이블은 체크만 하기위해 -- EXISTS절에 위치시켰으며 이로 인해 수행속도가 대폭 감소하게 된다. SELECT d.deptno, d.dname   FROM dept d  WHERE EXISTS       (SELECT 1          FROM emp e         WHERE e.deptno = d.deptno);

UNIQUE 연산자

중복된 값을 가진 튜플이 존재하지 않을 때 TRUE를 반환하는 연산자이다.

Attribute의 이름을 변경하는 쿼리문

SELECT E.Lname AS Employee_name, S.Lname AS Supervisor_name
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.Super_ssn = S.Ssn;

Attribute의 이름을 'AS'를 이용하여 변경하였다. Self Join을 하여 2개의 Lname을 추출하였다.

단일 Join 테이블을 만든 쿼리문

SELECT Fname, Lname, Address
FROM (EMPLOYEE JOIN DEPARTMENT ON Dno = Dnumber)
WHERE Dname = 'Research';
 

이 쿼리문은 EMPLOYEE, DEPARTMENT 테이블을 Dno=Dnumber로 조인을 하여 단일 Join 테이블로 만든 쿼리문이다. 아래 쿼리문과 동일한 결과를 가진다.

SELECT Fname, Lname, Address
FROM EMPLOYEE, DEPARTMENT
WHERE Dno = Dnumber AND Dname = 'Research';

집계함수(Aggregate function)의 이해

• - GROUP BY절을 이용하여 그룹 당 하나의 결과로 그룹화 할 수 있다.
• - HAVING절을 사용하여 집계함수를 이용한 조건 비교를 할 수 있다.
• - MIN, MAX 함수는 모든 자료형에 사용 할 수 있다.
• - 일반적으로 가장 많이 사용하는 집계함수에는AVG(평균), COUNT(개수), MAX(최대값), MIN(최소값), SUM(합계) 등이 있다.

COUNT

COUNT 함수는 검색된 행의 수를 반환 한다.

?

1 2 3 4 5 6

-- 검색된 행의 총 수 4개를 반환. 즉 4개의 부서가 존재한다. SELECT COUNT(deptno) FROM dept;   COUNT(DEPTNO) -------------             4

MAX

MAX 함수는 컬럼값 중에서 최대값을 반환 한다.

?

1 2 3 4 5 6

-- sal 컬럼값 중에서 제일 큰값을 반환. 즉 가장 큰 급여를 반환. SELECT MAX(sal) salary FROM emp;   SALARY -------   5000

MIN

MIN 함수는 컬럼값 중에서 최소값을 반환 한다.

?

1 2 3 4 5 6

-- sal 컬럼값 중에서 가장 작은 값 반환. 즉 가장 적은 급여를 반환 SELECT MIN(sal) salary FROM emp;    SALARY -------     800

AVG

AVG 함수는 평균 값을 반환 한다.

?

1 2 3 4 5 6 7 8

-- 부서번호 30의 사원 평균 급여를 소수점 1자리 이하에서 반올림 SELECT ROUND(AVG(sal),1) salary   FROM emp  WHERE deptno = 30;    SALARY  ------  1566.7

SUM

SUM 함수는 검색된 컬럼의 합을 반환 한다.

?

1 2 3 4 5 6 7 8

-- 부서번호 30의 사원 급여 합계를 조회. SELECT SUM(sal) salary   FROM emp  WHERE deptno = 30;    SALARY -------    9400

STDDEV

STDDEV 함수는 표준편차를 반환 한다.

?

1 2 3 4 5 6 7 8

-- 부서번호 30의 사원 급여 표준편차를 반환.    SELECT ROUND(STDDEV(sal),3) salary   FROM  emp  WHERE deptno = 30;     SALARY --------  668.331

집계함수 예

아래는 부서별 사원수, 최대급여, 최소급여, 급여합계, 평균급여를 급여합게 순으로 조회하는 예이다.

?

1 2 3 4 5 6 7 8 9 10 11 12 13

SELECT deptno 부서번호, COUNT(*) 사원수,        MAX(sal) 최대급여, MIN(sal) 최소급여,        SUM(sal) 급여합계, ROUND(AVG(sal)) 평균급여   FROM emp  GROUP BY deptno  ORDER BY SUM(sal) DESC;     부서번호   사원수  최대급여  최소급여   급여합계   평균급여 ------- -------- --------- -------- --------- ---------      20       5      3000       800    10875     2175      30       6      2850       950     9400     1567      10       3      5000      1300     8750     2917

GROUP BY와 HAVING의 이해

GROUP BY와 HAVING 절 링크 

- GROUP BY 함수는 집계 함수와 자주 사용된다. 특정 부서의 직원들의 급여의 총합과 같은 특정 그룹의 데이터를 추출할 때 사용한다. 집계함수와 사용하지 않는다면 DISTINCT와 비슷한 기능을 가진다.

- HAVING은 GROUP BY 절과 함께 사용이 된다. WHERE 절에서는 집계함수를 사용할 수 없다. 이 때 HAVING을 사용한다.

SELECT Dnumber, COUNT(*)
FROM DEPARTMENT, EMPLOYEE
WHERE Dnumber = Dno AND Salary>40000 AND
      (SELECT Dno
      FROM EMPLOYEE
      GROUP BY Dno
      HAVING COUNT(*)>3);

위의 쿼리문은 GROUP BY와 HAVING을 사용한 쿼리이다.

서브쿼리를 보면 EMPLOYEE 테이블에서 직원수가 3명 초과인 Dno(부서번호)를 가져온다. 이 때 WHERE 절에 HAVING을 사용하는 것을 확인할 수 있다.

오늘은 서브쿼리에 대해 공부해보았습니다. 서브쿼리는 제약조건, 사용 방식, 목적을 정확히 알아야 사용할 수 있어요. 다음 시간에는 VIEW에 대한 포스팅을 올릴게요!!