JAVA 웹개발 과정 국비 18일차 정리

18일차

※ 배우는 과정이므로 정확하지 않을 수 있습니다.
잘못된 점은 알려주시면 수정하도록 하겠습니다!

---

1. 그룹화 (GROUP BY + HAVING)

1) GROUP BY

특정 컬럼을 기준으로 그룹화시켜서 데이터를 만들어내는 기법

직원별 급여 목록은 아래와 같이 구할 수 있다.

SELECT emp_name, salary FROM employee;

전직원의 급여 합계는 아래와 같이 구할 수 있다.

SELECT SUM(salary) FROM employee;

그렇다면, 부서별 급여 합계는 어떻게 구할 수 있을까?
이때, GROUP BY를 이용한다.

• GROUP BY 뜻 : “~별로 헤쳐 모여~!!!”
• GROUP BY를 쓰면 SELECT 뒤 데이터 하나하나 값 출력 불가능하고, 그룹화 된 값만 출력 가능하다.
• 부가적인 효과 : 중복 값 제거
• 2개 이상 사용 가능하다.

SELECT 
    dept_code, 
    TO_CHAR(SUM(salary),'L999,999,999') AS "부서별 급여합계",
    COUNT(*)||'명' AS "부서별 인원수"
FROM employee 
GROUP BY dept_code  -- 부서코드별로 헤쳐모여~!!
ORDER BY 1;

부서 안에 직원들이 다 들어있는 그림을 연상하면 이해하기 쉽다.
부서코드와 SUM(salary)를 그 부서 안에 사람들에게 물어본다.

-- 부서코드별 보너스를 받는 사람의 인원수

-- 0을 출력 안하는 쿼리
SELECT dept_code, COUNT(*)||'명' AS "보너스 받는 인원 수" 
FROM employee WHERE bonus IS NOT NULL GROUP BY dept_code ORDER BY 1;

-- 0도 출력하는 쿼리
SELECT COUNT(bonus) FROM employee GROUP BY dept_code;

-- 부서별로 그룹화, 직급별로 그룹화(이중 그룹화)
SELECT dept_code, job_code , SUM(salary) AS "부서 내 직급별 합계"
FROM employee GROUP BY dept_code, job_code
ORDER BY 1;

2) HAVING

그룹화 데이터에 대한 조건을 부여하는 구문

부서 내 급여 평균이 250만원보다 작은 부서만 선별해서 보고싶다고 하자.

① WHERE절 = 조건?

조건이니까 WHERE절을 써서 쿼리문을 만들면 되겠지?

SELECT 
    dept_code,
    FLOOR(AVG(salary))
FROM employee 
WHERE AVG(salary) < 2500000  -- WHERE절?
GROUP BY dept_code 
ORDER BY 1;

언뜻보면 맞는 쿼리문 처럼 보이지만, 규칙 상 WHERE절에 그룹함수는 사용할 수 없으므로 에러가 난다.

② WHERE절 조건을 다르게?

그렇다면 WHERE절의 조건을 다르게 하면 어떨까?

SELECT 
    dept_code,
    FLOOR(AVG(salary))
FROM employee 
WHERE salary < 2500000  -- 조건을 다르게?
GROUP BY dept_code 
ORDER BY 1;

그러나 이렇게하면, 급여가 250만원보다 작은 직원을 뽑아 그룹화를 진행하게되어, 급여가 250만이 넘은 직원들은 아예 빠지게 된다.
구하려고 하는 값은 전체 직원의 부서에서 조건에 맞는 부서이기 때문에 맞지 않는다.

③ HAVING 문 사용

따라서, 그룹화 후에 조건을 달고 싶다면 HAVING절을 사용해야 한다.

SELECT 
    dept_code,
    FLOOR(AVG(salary))
FROM employee 
GROUP BY dept_code 
HAVING AVG(salary) < 2500000  -- HAVING절!
ORDER BY 1;

3) 쿼리 실행 순서

WHERE절 다음 GROUP BY가 진행된다는 것은 어떻게 알 수 있을까?
바로 쿼리 진행 순서가 정해져있기 때문이다.
SELECT 쿼리 실행 순서는 아래와 같다.

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

SELECT salary 급여 FROM employee WHERE 급여 < 2000000; --불가능함
SELECT salary 급여 FROM employee ORDER BY 급여; --가능

실행 순서를 잘 외워둔다면, 위와 같은 쿼리문에서 실수할 확률이 줄어든다.

2. JOIN

여러 테이블의 레코드를 조합해서 의미있는 ResultSet을 만들어내는 문법
다중 JOIN도 존재한다.

1) CROSS JOIN (카티션곱)

job 테이블과 department 테이블이 있다.
두 테이블을 그냥 합쳐서 보고싶다면 CROSS JOIN을 해주면 된다.
교집합이라고 생각할 수 있다.

두 테이블에 대한 모든 정보를 합치는 방법

① ORACLE 쿼리 문법

카티션곱이라고 불린다.

SELECT * FROM job, department;

② ANSI 표준 쿼리 문법

CROSS JOIN이라고 불린다.

SELECT * FROM job CROSS JOIN department;

2) INNER JOIN

특정 조건을 이용해서 의미있는 데이터를 만드는 것

조건문에 무조건 =을 써주지는 않지만, =가 올때는 ‘두개 값이 같으면 연결해라’라고 해석하면 조금 더 쉽게 이해할 수 있다.

① ORACLE 쿼리 문법

employee 테이블에도 job_code 컬럼이 있고, job 테이블에도 job_code가 있을 때, 간단하게 아래와 같이 쓰면 오류가 난다.
이때는 테이블에 별명을 만들어서 컬럼명 앞에 붙여줘야 한다.

SELECT emp_name, job_name FROM employee, job WHERE job_code=job_code; 
-- column ambiguously defined : 컬럼이 애매하게 정의되었다 (오류!)

SELECT emp_name, job_name FROM employee e, job j 
WHERE e.job_code=j.job_code;

② ANSI 표준 쿼리 문법

같은 컬럼명을 공유할 때는 특별하게 ON 대신 USING을 쓸 수 있다.
선택사항이므로 꼭 USING을 쓸 필요는 없다.
일반적으로 JOIN만 쓸때는 INNER JOIN을 의미한다.

-- ON 사용
SELECT emp_name, job_name FROM employee e (INNER) JOIN job j
ON (e.job_code=j.job_code);
-- USING 사용
SELECT emp_name, job_name FROM employee (INNER) JOIN job 
USING (job_code);

3) LEFT OUTER JOIN

INNER JOIN을 하니 NULL값을 가진 데이터는 아예 출력이 되지 않는다는 단점이 있다.
이 때 OUTER JOIN이 하나의 방법이 될 수 있다.

왼쪽 테이블의 값을 전부 나오게 하는 방법

① ORACLE 쿼리 문법

오른쪽 조건에 (+)를 붙여준다.
(+) 뜻은 ‘다른 테이블 값에 맞는 데이터가 없을 때 NULL 값으로 그냥 채워줘라!’라는 뜻. ‘

SELECT emp_name, dept_title FROM employee e,department d 
WHERE e.dept_code = d.dept_id(+);

② ANSI 표준 쿼리 문법

OUTER는 써도 되고 안써도 된다.

SELECT emp_name, dept_title FROM employee e LEFT (OUTER) JOIN department d 
ON (e.dept_code=d.dept_id);

4) RIGHT OUTER JOIN

LEFT OUTER JOIN의 반대 개념이다.

오른쪽 테이블의 값을 전부 나오게 하는 방법

① ORACLE 쿼리 문법

왼쪽 조건에 (+)를 붙여준다.
(+) 뜻은 ‘다른 테이블 값에 맞는 데이터가 없을 때 NULL 값으로 그냥 채워줘라!’라는 뜻. ‘

SELECT emp_name, dept_title FROM employee e,department d 
WHERE e.dept_code(+) = d.dept_id;

② ANSI 표준 쿼리 문법

OUTER는 써도 되고 안써도 된다.

SELECT emp_name, dept_title FROM employee e RIGHT (OUTER) JOIN department d 
ON (e.dept_code=d.dept_id);

5) FULL OUTER JOIN

양쪽 테이블의 값을 전부 나오게 하는 방법

① ORACLE 쿼리 문법

ORACLE 쿼리 문법에는 FULL OUTER JOIN이 없다.

② ANSI 표준 쿼리 문법

OUTER는 써도 되고 안써도 된다.

SELECT emp_name, dept_title FROM employee e FULL OUTER JOIN department d 
ON (e.dept_code=d.dept_id);

6) SELF JOIN

같은 테이블을 JOIN 하여 사용하는 방법
특정 문법이 존재하는 것이 아니라 자기 자신을 참조하면 그게 SELF JOIN이다.

‘송종기’의 manager_id(직속상사)는 200이다.
emp_id에서 200을 찾으면… 음 ‘선동일’이군!
manager_id 처럼 숫자가 아닌 사람 이름이 바로 나오면 좋을텐데..

SELECT emp_id, emp_name, manager_id FROM employee 
WHERE manager_id=emp_id;

WHERE절에 조건을 달아보았지만 나오지 않았다.
같은 행에서 emp_id와 manager_id가 같은 사람을 찾는 것이기 때문에 당연히 그런 사람은 없었다.
그럼 어떻게 해야할까?
같은 employee 테이블을 e1, e2로 나누어서 비교하는 것이다.

e1테이블과 e2테이블이라는 같은 테이블을 비교하면서 e1의 manager_id와 e2의 emp_id가 같아질 때 e2.emp_name을 출력한다

① ORACLE 쿼리 문법

SELECT e1.emp_id, e1.emp_name "직원명", e2.emp_name "상사이름" 
FROM employee e1, employee e2
WHERE e1.manager_id=e2.emp_id;

② ANSI 표준 쿼리 문법

SELECT e1.emp_id, e1.emp_name "직원명", e2.emp_name "상사이름"
FROM employee e1 JOIN employee e2 
ON (e1.manager_id=e2.emp_id);