Oracle PL/SQL문 강좌(4)

[4] Displaying Data from Multiple Tables (JOIN) 

1. EquiJoin : column1과 column2는 Primary Key와 Foreign Key관계인 경우 

SELECT emp.empno, emp.ename, emp.deptno, dept.deptno, dept.loc 

FROM emp, dept 

WHERE emp.deptno = dept.deptno 

2. Non-EquiJoin : Join하는 Table 사이에 서로 대응하는 Key가 없는 경우 

where절의 Join조건에 '='을 제외한 비교연산자를 사용한다. 

SELECT e.ename, e.sal, s.grade 

FROM emp e, salgrade s 

WHERE e.sal BETWEEN s.losal AND s.hisal 

3. Outer Join : 서로 = 되지 않는 row 까지도 모두 보여준다. 

정보가 없는쪽 컬럼 뒤에 (+)를 붙인다.( =, and 만 사용가능) 

SELECT e.ename, d.deptno, d.dname 

FROM emp e, dept d 

WHERE e.deptno (+) = d.deptno 

ORDER BY e.deptno 

4. Self Join : 같은 Table을 그것이 마치 2개의 Table인 것처럼 Join해서 사용한다. 

SELECT worker.ename, manager.ename 

FROM emp worker, emp manager 

WHERE worker.mgr = manager.empno 

5. SET OPERATORS 

UNION : 중복된 row는 제외하고 보여준다. UNION ALL : 중복된 row까지 모두 보여준다. INTERSECT : A,B의 중복된 row만 보여준다.MINUS : A,B의 중복된 row를 제외한 A row를 보여준다. 

[5] Aggregating Data Using Group Functions(그룹함수를 사용한 데이터집계) 

1. Group Function : 행집합에 적용하여 그룹당 하나의 결과를 생성한다. 

AVG([DISTINCT|ALL] n) : 평균값 

COUNT({*|[DISTINCT|ALL] expr}) : row수 

MAX([DISTINCT|ALL] expr) : 최대값 

MIN([DISTINCT|ALL] expr) : 최소값 

SUM([DISTINCT|ALL] n) : 합 

STDDEV([DISTINCT|ALL] x) : 표준편차 

VARIANCE([DISTINCT|ALL] x) : 분산 

* count(*)를 제외한 모든 Group Function은 Null을 배제하고 수행한다. 

Null을 포함하고 싶다면 NVL함수를 사용한다. 

* DISTINCT나 ALL을 쓰지 않으면 Default가 ALL이다. 

* AVG,SUM, STDDEV, VARIANCE는 반드시 숫자형이다. 

2. 어떤 컬럼에 해당하는 데이터별 그룹 함수를 사용할 때 

SELECT [deptno,] COUNT(ename) 

FROM emp --> 이문장은 성립되지 않는다. GROUP BY가 없다. 

<추가> 

GROUP BY deptno 

*일반칼럼과 그룹함수를 같이 쓰면 group by절에 일반 컬럼 명시(열 별칭 사용 못함) 

*GROUP BY 열을 SELECT 절에 포함시키지 않아도 된다 

3. 그룹 함수는 WHERE절에 올수가 없다. 

SELECT deptno, AVG(sal) 

FROM emp 

WHERE AVG(sal) > 2000 

GROUP BY deptno 

--> 이 문장은 성립되지 않는다. WHERE절에 그룹 함수가 올 수 없다. 

SELECT deptno, AVG(sal) 

FROM emp 

GROUP BY deptno 

HAVING AVG(sal) > 2000 

4. HAVING : 그룹 함수를 조건절에 적용시키기 위해서 사용한다. 

SELECT job, SUM(sal) 

FROM emp 

WHERE job NOT LIKE 'SALES%' 

GROUP BY job 

HAVING SUM(sal) > 5000 

ORDER BY SUM(sal) 

*절 평가 순서 : ① WHERE -> ② GROUP BY절 -> ③ HAVING절 

*그룹 함수는 두 번까지 중첩될 수 있다. MAX( AVG(SAL)) 

[6] Subqueries( WHERE 절, HAVING 절, FROM 절 ) 

1. Subquery 규칙 

- 반드시 ()로 묶어야 한다. 

- 반드시 비교연산자 오른쪽에 위치해야 한다. 

- ORDER BY 절에는 사용할 수 없다. 

- 서브쿼리 결과가 한개일때는 Single-Row 비교연산자를 사용해야 한다. 

( =, >, >=, <, <=, <> ) 

- 서브쿼리 결과가 여러개일때는 Multi-Row 연산자를 사용해햐 한다. 

2. Subquery 사용예1 

* 반드시 비교연산자 오른쪽에 써야한다. 

SELECT ename FROM emp 

WHERE sal > (SELECT sal FROM emp 

WHERE empno = 7566) 

3. Subquery 사용예2 

SELECT ename, job 

FROM emp 

WHERE job = (SELECT job 

FROM emp 

WHERE empno = 7369) 

AND 

sal > (SELECT sal 

FROM emp 

WHERE empno = 7876) 

4. Subquery 사용예3 

SELECT job, AVG(sal) 

FROM emp 

GROUP BY job 

HAVING AVG(sal) > (SELECT MIN(AVG(sal)) 

FROM emp 

GROUP BY job) 

5. Subquery 사용예4 (Subquery 결과가 여러개가 나올때 비교연산자 사용법) 

SELECT empno, ename 

FROM emp 

WHERE sal IN (SELECT MIN(sal) ( =ANY 와 같음 ) 

FROM emp 

GROUP BY deptno) 

6. ANY(동의어:SOME)연산자를 사용한 Subquery : 조건중에 한개만 만족하면 OK 

SELECT empno, ename, job 

FROM emp 

WHERE sal < ANY (SELECT sal --> OR 

FROM emp 

WHERE job = 'CLERK') 

AND job <> 'CLERK' 

7. ALL연산자를 사용한 Subquery : 모든 조건을 만족해야 OK 

SELECT empno, ename, job 

FROM emp 

WHERE sal > ALL (SELECT avg(sal) --> AND 

FROM emp 

GROUP BY deptno