21.01.29

[SubQuery]

- 서브쿼리는 쿼리안의 쿼리 라는 뜻이다.
- 서브쿼리는 사전에 추출된 내용에서 재검색 하거나, 검색된 내용을 가상 컬럼을 만들어 추가 할 수 있다.
- 즉, 서브쿼리를 사용하는 이유는 가져온 데이터를 재 정제 하기 위함이라 볼 수 있다.
- 서브쿼리는 특정컬럼을 생성할 때에도 사용할 수 있다.

-- 문제1> 'han'의 근무부서?
--1. emp 테이블에서 han의 정보에서 depno를 확인
SELECT depno FROM emp WHERE ename='han';
--2. dept테이블에서 추출한 depno으로 depname을 추출
SELECT depname FROM dept WHERE depno='1'; 

-- 서브쿼리>
SELECT depname FROM dept WHERE depno = (SELECT depno FROM emp WHERE ename='han');

-- 문제2> 부서위치가 LA 또는 BOSTON인 부서에 속한 사람들의 이름과 직책
SELECT depno FROM dept WHERE loc = 'LA' OR loc ='BOSTON';
SELECT ename,job FROM emp WHERE depno ='2' OR depno ='4';

-- 서브쿼리>
SELECT ename,job FROM emp WHERE depno 
	IN(SELECT depno FROM dept WHERE loc = 'LA' OR loc ='BOSTON');
-- IN : 서브쿼리의 결과중 하나라도 일치하면 참, = 만 가능
-- ALL : 서브쿼리의 결과중 모든 값이 일치
-- EXITS : IN과 같은데 만족하는 값이 하나라도 존재하면 된다.

- 문제1) emp에서 han의 depno의 정보를 바로 넣는다.
- 서브쿼리는 괄호를 쳐줘야 한다.
- 문제2) 추출할 정보의 조건이 두개 이상이기 때문에 이 경우에는 depno의 정보를 넣는 과정에서 
  IN으로 넣어 줄 수 있다. 
- 단, IN은 하나의 컬럼에서만 가능.

-- 3문제>JOB이 매니저인 사원들(가장 빠른 입사일 기준)보다 입사일이 빠른 직원이 데이터 가져오기
SELECT * FROM emp WHERE job='manager' ORDER BY hiredate;
SELECT ename, job, hiredate FROM emp WHERE hiredate < '15/08/12';
-- 좀더 편한 방식
SELECT MIN(hiredate) FROM emp WHERE job='manager';

-- 서브쿼리>
SELECT ename, job, hiredate FROM emp WHERE hiredate 
	< (SELECT MIN(hiredate) FROM emp WHERE job='manager');

-- JOB이 매니저인 사원들(가장 빠른 입사일 기준)보다 입사일이 늦는 직원이 데이터 가져오기
SELECT ename, job, hiredate FROM emp WHERE hiredate 
	> ANY(SELECT hiredate FROM emp WHERE job='manager');

- 문제3) MIN() 함수를 이용하여 추출
- MIN() : 최소값을 추출
- ANY : 메인쿼리의 비교 조건이 서브쿼리의 결과중에서 하나라도 일치할경우..
- = ANY : IN과 같은 효과
- > ANY : 서브쿼리의 최소값보다 크면
- < ANY : 서브쿼리의 최대값보다 작으면

-- 문제4> sales부서에서 근무하는 사원들의 이름,직책,입사일 가져오기
SELECT depno FROM dept WHERE depname='sales';
SELECT ename,job,hiredate FROM emp WHERE depno='1';
-- 서브쿼리>
SELECT ename,job,hiredate FROM emp WHERE depno 
	= (SELECT depno FROM dept WHERE depname='sales');

-- 문제5> 부서별로 직원이 몇명인지 확인
-- 몇명이지?
SELECT COUNT(ENAME) FROM emp WHERE depno=1;

SELECT
    depno,
    depname,
    loc,
    (SELECT COUNT(ENAME) FROM emp WHERE depno=d.depno) AS depCnt
 FROM dept d;

- 문제5) SELECT COUNT([컬럼]) FROM [테이블명] WHERE [조건]; : 해당 데이터의 개수를 구할수 있다.
- 서브쿼리는 특정 컬럼을 생성할 때도 괄호를 이용해 사용할 수 있다.

[JOIN]

- 통상 2개 이상의 테이블을 연결하여 데이터를 검색하는 방법
- JOIN을 사용하기 위해서는 연결되는 테이블간에 공통되는 컬럼이 존재해야한다.
- 그래서 일반적으로 부모자식간의 테이블을 이용하여 JOIN을 진행한다.
- 부모자식간이 아니어도 사용은 할 수 있다.

-- 0. CROSS JOIN
-- 카다시안 곱을 두개의 테이블에 실행 한 내용
-- emp(4) * dept(10) = 40
SELECT e.ename,d.depname FROM emp e CROSS JOIN dept d;
SELECT e.ename,d.depname FROM emp e , dept d;

- CROSS JOIN : 카다시안 곱을 두개의 테이블에 실행 한 내용
- ~~ FROM [tableA] CROSS JOIN [tableB]
- ~~ FROM [tableA] [tableB] -> CROSS JOIN 명령어 생략 가능
- CROSS JOIN은 두개의 테이블에 모든 데이터를 조합하여 보여주므로 크게 의미있는 데이터를 뽑기 힘들다.

-- 1. Equi JOIN
-- 1) Equi JOIN - 등가 조인
SELECT e.ename,d.depname FROM emp e , dept d WHERE e.depno = d.depno;

-- 2) Equi JOIN  - 내부조인(INNER JOIN)
SELECT e.depno, e.ename, d.depname FROM emp e INNER JOIN dept d ON e.depno = d.depno;

SELECT e.ename, d.depname FROM emp e JOIN dept d USING (depno); --여기서는 depno못부름

-- 3) Equi JOIN - NATURAL JOIN
SELECT depno, e.ename, d.depname FROM emp e NATURAL JOIN dept d; -- 동일한 컬럼 depno

-- 2. 자기조인(self join)
SELECT a.ename, b.job FROM emp a, emp b;

1. Equi JOIN : Equlity Condition(=)
1) Equi JOIN - 등가 조인 : 조건에 맞고 쌍방간에 존재할 경우에 보여준다
- SELECT [불러올 컬럼명] FROM [테이블1],[테이블2] WHERE [조건];
2) Equi JOIN  - 내부조인(INNER JOIN) : INNER는 생략 가능 JOIN조건에 WHERE대신 ON 사용
- SELECT [불러올 컬럼명] FROM [테이블1] INNER JOIN [테이블2] ON [조건];
- 등가조인이랑 보여주는건 같고 명령어만 다를 뿐이다.
- ON대신 USING을 사용 할 수 있다.
- SELECT [불러올 컬럼명] FROM [테이블1] JOIN [테이블2] USING ([조건컬럼]);
- USING은 조인에 사용할 컬럼이나 테이블 뷰 ,서브쿼리등을 사용가능
- USING에 사용된 컬럼은 SELECT문으로 불러 올 수 없다.
3) Equi JOIN - NATURAL JOIN
- 동일한 컬럼을 모두 자동으로 조인해 놓는다.
- 그래서 조건절 ON이 필요가 없다.
- 다만 동일한 컬럼은 단축명을 주지 않는다.

2. 자기조인(self join)
- Equi 조인과 같으나 동일한 테이블을 대상으로 삼는다.
- 자기 자신의 컬럼들을 대상으로 카다시안곱을 수행

-- 3. 외부조인 (OUTER JOIN)
-- personnel 값은 dept는 존재하지만 emp에는 존재하지 않는다.
-- emp쪽에 null이 추가되어야 한다.
-- 그래서 e.depno 부분에 (+)가 추가 되어야한다.
SELECT e.depno, e.ename,d.depname FROM emp e , dept d 
    WHERE e.depno(+) = d.depno; 
-- emp 에는 depno 6이 있지만 dept에는 depno 6이 존재하지 않는다.
-- dept쪽에 null이 추가 되어야 한다.
-- 그래서 d.depno부분에 (+)추가 되어야한다.
SELECT e.depno, e.ename,d.depname FROM emp e , dept d 
    WHERE e.depno = d.depno(+);

-- LEFT OUTER JOIN
SELECT e.depno, e.ename,d.depname 
    FROM emp e LEFT OUTER JOIN dept d -- emp 기준
    ON e.depno = d.depno;
    
-- RIGHT OUTER JOIN
SELECT d.depno, e.ename,d.depname 
    FROM emp e RIGHT OUTER JOIN dept d -- dept 기준
    ON e.depno = d.depno;

-- FULL OUTER JOIN
SELECT d.depno, e.ename,d.depname 
    FROM emp e FULL OUTER JOIN dept d
    ON e.depno = d.depno;

3. 외부조인 (OUTER JOIN) : A또는 B를 만족시키면 보여준다.
- A에는 있지만 B에는 존재하지 않는 데이터가 있는 경우 B에 NULL을 추가해야한다.
- (+)로 NULL을 추가하고 (+)는 한 컬럼에 적용되는 것이 아니고 테이블에 적용되는 것이다.
- (+)는 양쪽에는 넣어 줄 수 없다.
- FROM [tableA] [LEFT|RIGHT|FULL] OUTER JOIN [tableB] ON 조건절 :
	기준이 되는 곳을 지목하면 기준이 되는 테이블의 데이터를 더 보여준다.
- FULL을 사용하면 양쪽 테이블의 데이터가 모두 보여지게 된다.
- 그래서 (+)를 양쪽에 넣어줄 수 없으므로 FULL을 이용하면 된다.

[SET]

- 다수의 테이블에 대해서 집합 연산을 할 수 있다.
- 합집합(중복여부),교집합,차집합
- UNION의 경우 중복제거 때문에 연산 수행이 다소 느리다.

-- 1. UNION(합집합 - 중복 허용 X)
SELECT depno FROM emp UNION SELECT depno FROM dept;
-- 2. UNION ALL(합집합 - 중복 허용)=> 두개 테이블의 데이터를 합쳐 놓는다.
SELECT depno FROM emp UNION ALL SELECT depno FROM dept;
-- 3. INTERSECT(교집합)
SELECT depno FROM emp INTERSECT SELECT depno FROM dept;
-- 4. MINUS(차집합)
-- emp에서 dept를 뺄경우
SELECT depno FROM emp MINUS SELECT depno FROM dept;
-- dept에서 emp를 뺄경우
SELECT depno FROM dept MINUS SELECT depno FROM emp

- [쿼리1] [UNION|UNION ALL|INTERSECT|MINUS] [쿼리2]