SQL - 기본 문법 (3) [작성중]

목차

• GROUP BY
• HAVING (vs WHERE)
• 테이블 결합
  • 집합 연산자
  • JOIN
• 서브쿼리
  • 스칼라 서브쿼리
  • 인라인 뷰
  • 상호연관 서브쿼리

GROUP BY

그룹 함수 (복수행함수)

: 여러 행을 이용하여 한 건을 출력하는 함수입니다. GROUP BY절을 배우기 전에 반드시 알야아 할 함수입니다.

GROUP BY

: 특정 속성을 기준으로 그룹화하는 절입니다. 모든 데이터에 대한 결과값이 아닌 특정 대상에 대한 결과만을 원할 때 사용합니다. 예를 들어 GROUP BY절 없이 SUM함수를 사용한다면 모든 값의 합을 구하지만 테이블 EMP에서 JOB기준으로 GROUP BY절을 추가 후 SUM함수를 사용한다면 JOB별 값의 합을 구할 수 있게 됩니다.

SELECT SUM(SAL)
  FROM EMP;

SELECT JOB, SUM(SAL)
  FROM EMP
 GROUP BY JOB;

여기서 문법적으로 반드시 지켜주셔야 할 것이 있는데 GROUP BY에 입력한 컬럼은 SELECT절에도 반드시 입력해야합니다.

추가로, GROUP BY에는 여러 속성을 기준으로 삼을 수 있습니다. 위 예시에서는 JOB에 대해서만 그룹화를 설정했지만 JOB과 DEPTNO 두 속성에 대해서 동시에 그룹화하여 그룹 함수를 사용할 수도 있습니다.

SELECT DEPTNO, JOB, SUM(SAL)
  FROM EMP
 GROUP BY DEPTNO, JOB;

HAVING

: 그룹함수를 사용할 때 조건으로 제한할 때 사용하는 절입니다. WHERE과 비슷한 기능을 가지고 있지만 WHERE과는 다르게 GROUP BY가 사용된 시점 이후의 결과를 사용할 수 있습니다.

다음 이미지에서 볼 수 있다시피 부서별 그룹함수를 적용한 이후에는 WHERE 사용에 제한이 있기 때문에 HAVING을 사용합니다.

SELECT DEPTNO, AVG(SAL)
  FROM EMP
 GROUP BY DEPTNO
 HAVING AVG(SAL)>=2500;

HAVING은 WHERE의 기능도 그대로 사용할 수 있습니다만, 그러면 기능이 많은 HAVING을 사용해도 되지 않을까 싶은 분들도 있을 것입니다. 하지만 WHERE로 가능한 쿼리는 되도록이면 WHERE로 하시는게 좋습니다. 왜냐하면 WHERE절을 사용하면 GROUP BY 절이 실행되기 전에 처리하는 행의 갯수가 줄어들기 때문에 속도 측면에서 더 이득을 볼 수 있습니다. 그림을 통해 설명드리도록 하겠습니다.

그러므로 HAVING절은 짧게 두 줄로 요약할 수 있겠습니다.

1. GROUP BY 이후의 결과에 대해서도 조건을 걸 수 있습니다.
2. WHERE로도 가능한 경우 WHERE을 사용합시다.

테이블 결합

여태까지는 한 테이블 내에서의 데이터를 조회했습니다. 이제부터는 각각 다른 테이블의 데이터들을 묶는 기능들에 대해서 알아보려고 합니다.

집합 연산자

집합 연산자는 조회 결과들에 대한 집합 연산을 하는 기능을 가지고 있습니다. 집합 연산에는 합집합, 교집합, 차집합이 있습니다.

1. 합집합

   UNION : 중복 제거를 위해 내부 정렬 후 두 조회 결과 합치기를 수행합니다.

   UNION ALL : 중복 제거 없이 조회 결과를 합칩니다.

   그래서 중복이 없을때는 UNION의 악성 쿼리가 됩니다. 중복이 없는데도 굳이 조회 결과간 중복되는 데이터가 있는지 확인 절차가 있기 때문입니다. 즉, UNION / UNION ALL의 결과가 같은 경우는 UNION ALL 사용하는 것이 속도면에서 좋습니다.

   그리고 하나 더 주의할 게 있습니다. 컬럼의 형태가 다른 경우에는 집합 연산이 되지 않습니다.

1. 교집합

   INTERSECT : 중복되는 조회 결과만 출력합니다.

2. 차집합

   MINUS : 기준이 되는 데이터에서 중복되는 결과를 제외하고 출력합니다.

JOIN

드디어 대망의 JOIN에 도착했습니다. 집합 연산자와는 달리 서로 다른 테이블의 정보를 같은 조건의 데이터에 하나로 합치는 기능입니다. 모든 데이터들이 한 테이블에 있는 경우는 드물고 보통은 여러 테이블에 나눠져서 보관되기 때문에 JOIN기능을 잘 활용하면 원하는 결과를 빨리 찾아볼 수 있습니다.

바로 JOIN을 이용한 간단한 쿼리를 작성해보겠습니다. EMP 테이블에는 부서번호(DEPTNO)는 있지만 부서명(DNAME)은 없습니다. DEPT 테이블에 부서명 데이터가 있기 때문에 EMP의 사원정보에 부서이름을 붙이기 위해서 두 테이블을 JOIN해보도록 하겠습니다.

SELECT E.EMPNO, E.ENAME, D.DNAME
  FROM EMP E, DEPT D
 WHERE D.DEPTNO = E.DEPTNO;

JOIN 문법에 대해 설명하도록 하겠습니다. 기존에 작성하던 쿼리들과 큰 차이점이 하나 있습니다. 바로 테이블 이름 옆에 문자가 하나 추가가 된 것인데요. 이는 Alias로 부르며 SELECT에서 쓰던 그 Alias와 비슷합니다. 사용 목적은 가독성도 있지만 쿼리 작성을 편하게 해주는데 있습니다.

그리고 SELECT절에 테이블 옆에 있는 Alias가 왼쪽에 컬럼에 붙어있는 것을 볼 수 있습니다. E.EMPNO는 E 라는 Alias를 가진 테이블의 EMPNO라는 뜻입니다. 즉, EMP 테이블의 EMPNO를 의미합니다. 이런 식으로 붙이는 이유는 뭘까요? 바로 JOIN하려는 서로 다른 테이블끼리 같은 이름을 가진 컬럼을 구분하기 위해서인데요 Alias를 컬럼명에 붙이지 않으면 SQL 문법에서는 해당 컬럼이 어떤 테이블의 데이터인지 알 수 없습니다. 철수와 영희가 모두 사과를 가지고 있는데 그냥 사과만 얘기하면 철수가 가지고 있는 사과인지 영희가 가지고 있는 사과인지 모르는 것을 생각하시면 됩니다.

WHERE절에는 테이블이 붙을 조건을 적어두면 됩니다. 위 쿼리에서는 DEPTNO가 같은 데이터끼리 묶어라를 의미합니다. 여기서도 Alias는 잊으시면 안됩니다.

다음으로는 JOIN의 종류에 대해 설명하도록 하겠습니다. JOIN은 크게 두 종류로 나뉘는데요. EQUAL 연산자를 사용하는지 아닌지에 따라 갈립니다.

1. EQUI JOIN : 테이블을 연결지을 때 묶는 컬럼에 대한 조건이 = (EQUAL)인 경우를 말합니다.

2. NON EQUI JOIN : EQUI JOIN이 아닌 것. 즉, BETWEEN, >혹은 < 같이 =가 아닌 조건으로 JOIN하는 경우를 말합니다.

그리고 출력 범위에 대해서도 종류가 나뉩니다.

1. INNER JOIN : 일반적으로 사용하며 테이블 중 하나라도 NULL이 존재하는 데이터는 출력하지 않음

2. OUTER JOIN : 어느 한쪽의 데이터가 NULL 이더라도 출력하는데, WHERE 절 컬럼에 (+)를 붙임으로서 사용이 가능합니다.

   크게는 NULL값이 있는 방향을 기준으로 LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN으로 구분합니다.

   [OUTER JOIN 주의 사항]

   1) 두 테이블의 조인 조건이 여러개인 경우 기준이 되는 반대쪽 테이블의 컬럼 옆에 모두 (+)를 적어야합니다.

   • 예시) 테이블 s1이 기준이고 s2를 OUTER JOIN하는 경우

     WHERE s1.grade = s2.grade(+)

     AND s1.height < s2.height(+)

   2) 기준이 되는 테이블 상대쪽 테이블이 여러 개인 경우, 모든 상대 테이블 컬럼에 (+)를 붙여야합니다.

   • 예시) s - p(+) - d(+) : 테이블 s가 기준이고 p, d를 OUTER JOIN하는 경우

     WHERE s.profno = p.profno(+)

     AND p.deptno = d.deptno(+)

   3) 순환구조를 갖는 테이블일 경우 OUTER JOIN이 불가능하기 때문에 서브쿼리 등을 이용하여 테이블 구조 변경이 필요합니다.

마지막으로 자기 자신에 대해서 JOIN을 실행하는 경우도 있습니다.​

1. SELF JOIN : 하나의 테이블이 자기 자신에 JOIN되는 경우를 말합니다.

SELECT E1.ENAME "EMP", E2.ENAME AS "MGR"
  FROM EMP E1, EMP E2
 WHERE E1.MGR = E2.EMPNO;

위 쿼리는 SELF JOIN을 이용한 사원과 그 사원의 상사를 출력하는 쿼리입니다. 쿼리 결과를 보면 사원 이름에 KING이 빠진 것을 확인할 수 있는데요. KING은 상사가 없기 때문입니다. KING도 출력하기 위해서는 위에서 다룬 OUTER JOIN을 이용하면 됩니다. 다음과 같이요.

SELECT E1.ENAME "EMP", E2.ENAME AS "MGR"
  FROM EMP E1, EMP E2
 WHERE E1.MGR = E2.EMPNO(+);

서브쿼리(Subquery)

: 쿼리문 안에 있는 쿼리문을 의미합니다.

​ 서브쿼리 종류 및 목적

1. 스칼라 서브쿼리(Scalar Subquery) :

   SELECT 절에 서브쿼리를 사용하는 것을 말하며 하나의 결과만 리턴이 가능하며, 두 개 이상의 결과는 리턴할 수 없습니다. 일치하는 데이터가 없을 때는 NULL값을 리턴할 수 있습니다.

SELECT DEPTNO, (SELECT MIN(SAL)
                  FROM EMP
                 WHERE DEPTNO = D.DEPTNO) AS MIN_SAL
  FROM DEPT D;

1. 인라인 뷰(Inline View) :

   FROM 절에 서브쿼리를 사용하는 것을 말하며, 서브쿼리의 결과를 하나의 테이블처럼 사용할 수 있습니다.

SELECT *
  FROM EMP E, (SELECT DEPTNO, MAX(SAL) AS MAX_SAL
                 FROM EMP
                GROUP BY DEPTNO) I
 WHERE E.DEPTNO = I.DEPTNO
   AND E.SAL = I.MAX_SAL;

1. 상호연관 서브쿼리(Corelated Subquery) :

   SELECT, FROM 절 외에 다른 절에 붙으며 그룹 간의 컬럼 동시 비교 가능. 그룹에 대한 비교가 서브쿼리절에서 수행, GROUP BY 절 생략이 가능합니다.

다음 내용

• DDL
• DML
• TCL
• Data Dictionary
• Constraint
• Sequence
• 권한 부여