JAVA 웹개발 과정 국비 19일차 정리
19일차
※ 배우는 과정이므로 정확하지 않을 수 있습니다.
잘못된 점은 알려주시면 수정하도록 하겠습니다!
1. Set Operator
집합 연산자.
- 두 개 이상의 테이블에서 조인을 사용하지 않고 연관된 데이터를 조회하는 방법 중 하나.
- 컬럼의 개수, 데이터 타입이 같아야 한다.
TABLE temp1 에 A, B 가 있고, TABLE temp2 에 B, C가 있다고 하자.
1) UNION
중복을 제거하는 합집합
같은 부류의 데이터인데, 테이블이 나뉘어져있을 때 사용한다.
ex) 고객정보, 블랙리스트 고객정보 테이블이 각각 있을 때, 전체 고객 테이블을 생성
ex) 새 상품 테이블, 리퍼 상품 테이블이 각각 있을 때, 전체 상품 테이블을 생성
UNION의 결과값은 A, B, C 가 나온다.
SELECT * FROM temp1
UNION
SELECT * FROM temp2;
2) UNION ALL
중복을 허용하는 합집합
UNION의 특성을 모두 가지지만, 중복값을 허용한다는 점만 다르다.
UNION ALL의 결과값은 A, B, B, C 가 나온다.
SELECT * FROM temp1
UNION
SELECT * FROM temp2;
3) INTERSECT
교집합
INTERSECT의 결과값은 B가 나온다.
SELECT * FROM temp1
INTERSECT
SELECT * FROM temp2;
4) MINUS
차집합
INTERSECT의 결과값은 A가 나온다.
SELECT * FROM temp1
MINUS
SELECT * FROM temp2;
2. SUBQUERY
하나의 SQL문 안에 포함되어 있는 또 다른 SQL문.
SUBQUERY와 JOIN은 일부 상호 대체가 가능하다. (완전한 대체는 불가능)
1) SUBQUERY 의 이해
전 직원의 평균 급여보다 더 많은 급여를 받고 있는 직원의 사번, 이름, 직급코드, 급여를 출력한다고 하자.
① 조건 = WHERE절?
일단 조건이 들어가니 WHERE절을 써봐야겠다.
SELECT emp_id, emp_name, job_code, salary
FROM employee
WHERE salary> AVG(salary);
그러나 오류가 난다.
알고있듯이 WHERE절에는 그룹함수 사용이 불가능하기 때문이다.
② 평균 급여를 구해보자
그렇다면 전 직원의 평균 급여를 구해서 AVG(salary) 대신 사용하면 맞는 값을 출력할 수 있을 것 같다.
SELECT AVG(salary) FROM employee;
-- 평균 급여 : 3047667
SELECT emp_id, emp_name, job_code, salary
FROM employee
WHERE salary >= 3047662;
실행은 되지만 두 개의 쿼리를 써야하므로 비효율적인 방법이다.
③ 서브쿼리 사용
따라서 아래와 같이 서브쿼리를 사용하면 깔끔하게 원하는 값을 얻어낼 수 있다.
SELECT emp_id, emp_name, job_code, salary FROM employee
WHERE salary >= (SELECT AVG(salary) FROM employee);
2) 서브쿼리의 종류
- 단일 행 서브쿼리 : 딱 하나의 행만 얻어내는 서브쿼리
- 단일 열 서브쿼리 : 딱 하나의 열만 얻어내는 서브쿼리
- 다중 행 서브쿼리 : 여러 행의 결과를 갖는 서브쿼리
- 다중 열 서브쿼리 : 여러 열의 결과를 갖는 서브쿼리
① 단일 행 서브쿼리
-- 윤은해 직원의 급여와 같은 급여를 받는 사원들의 사번, 이름, 급여를 출력해보세요.
SELECT emp_id, emp_name, salary FROM employee
WHERE salary=(SELECT salary FROM employee WHERE emp_name='윤은해');
-- employee 테이블에서 급여를 가장 적게 받는 직원의 이름과 급여를 출력해보세요.
SELECT emp_name, salary FROM employee
WHERE (SELECT MIN(salary) FROM employee)=salary;
-- employee 테이블에서 급여를 가장 적게 받는 직원과 많이 받는 직원의 이름과 급여를 출력해보세요.
SELECT emp_name, salary FROM employee
WHERE salary IN ((SELECT MIN(salary) FROM employee),(SELECT MAX(salary) FROM employee));
② 다중 행 서브쿼리
송종기 또는 전지연 직원과 같은 부서에서 일하는 직원들의 이름, 부서, 급여를 출력해보자.
SELECT emp_name, dept_code, salary FROM employee
WHERE dept_code = (SELECT dept_code FROM employee WHERE emp_name='송종기')
OR dept_code = (SELECT dept_code FROM employee WHERE emp_name='전지연');
그러나 이 쿼리문은 단일 행 쿼리를 두 개 쓰는 것이다.
다중 행 서브쿼리를 이용하여 하나의 서브쿼리에서 두개의 행이 나오게 할 것이다.
SELECT emp_name, dept_code, salary FROM employee
WHERE dept_code = (SELECT dept_code FROM employee WHERE emp_name IN('송종기','전지연'));
IN을 이용하여 송종기 또는 전지연의 이름일 때, dept_code를 보여주는 서브쿼리를 만들었다.
얼핏보면 말이 되는 것 같지만, 서브쿼리만 출력해보면 D9, D1 이렇게 두개의 행이 출력된다.
하나의 데이터(dept_code)가 두 데이터(D9, D1)과 같다(=)? 말이 되지 않는 문장이다.
따라서 서브쿼리에서 여러 데이터를 만들어서 내보냈을 경우, = 를 쓸 수 없다.
그럼 어떤 것을 써야하는가?
IN, NOT IN, ANY, ALL
다중 행 서브쿼리라고 다른 문법이 있는 것이 아니다.
1:N 일때 쓸 수 있는 기능을 쓰면 된다.
ⓐ IN / NOT IN
IN은 ~거나 ~거나 ~일때, 즉 or or or 인 조건에 쓸 수 있다.
따라서 여러 행의 데이터를 가진 다중 행 서브쿼리에 IN을 쓰면 똑같은 효과를 가져온다.
-- 차태연, 전지연 직원의 급여등급과 동일한 급여등급을 가진 직원의 직급명, 이름을 출력해보세요.
SELECT job_name, emp_name FROM employee
JOIN job USING (job_code)
WHERE sal_level IN
(SELECT sal_level FROM employee WHERE emp_name IN ('차태연','전지연'));
ⓑ ANY
IN이 =의 역할과 비슷하다면, >나 <는 다중 행 서브쿼리에서 어떻게 작용시켜야할까?
J3 직급을 가진 직원의 급여보다 급여를 많이 받는 직원을 출력하고 싶다.
단순히 생각해서 쿼리문을 써보면 아래와 같이 쓸 수 있다.
SELECT emp_name, salary FROM employee
WHERE salary > (select salary From employee WHERE job_code='J3');
그러나 = 때와 마찬가지로, >나 <는 1개의 값과 2개 이상의 값을 비교할 수 없다.
J3 직급을 가진 사람의 급여가 여러 값이겠지만, 그 값들 중에서 아무거나보다 크다면 조건을 만족하는 것이 된다.
예를 들어 J3 직급을 가진 사람들의 급여가 200만, 300만, 400만원이라고 했을 때, 출력하려는 직원의 급여가 250만이라면, 200만 급여를 받는 J3 직원보다 많이 받는 것이므로 조건을 만족하는 것이다.
320만이라면 200만, 300만 급여를 받는 J3 직원보다 많이 받는 것이므로 역시 조건을 만족하고, 450만이라고해도 모든 J3 직원의 급여보다 많기 때문에 출력된다.
다시말하면 200만의 급여를 받는 J3 직원보다만 급여를 많이 받는다면 조건에 부합하는 것이다.
따라서 아래와 같이 >와 ANY를 같이 쓸 수 있다.
SELECT emp_name, salary FROM employee
WHERE salary > ANY (select salary From employee WHERE job_code='J3');
-- ex) WHERE salary > 아무거나 (200만, 300만, 400만) = (200만 보다 크면 충족)
같은 내용을 MIN 함수를 이용하여 수행할 수 있다.
SELECT emp_name, salary FROM employee
WHERE salary > (select min(salary) From employee WHERE job_code='J3');
ⓒ ALL
그렇다면, J3 직원들의 각각의 급여 모두보다 큰 급여를 받는 직원을 출력하고 싶다면?
가장 급여를 많이 받는 사람보다만 크면 조건을 충족시킬 수 있다.
즉, 400만보다만 크면 J3의 모든 직원의 급여보다 많이 받는 것이 된다.
이럴 때는 ALL을 같이 써준다.
SELECT emp_name, salary FROM employee
WHERE salary > ALL (select salary From employee WHERE job_code='J3');
같은 내용을 MAX 함수로 수행할 수 있다.
SELECT emp_name, salary FROM employee
WHERE salary > (select MAX(salary) From employee WHERE job_code='J3');
③ 다중 열 서브쿼리
이태림 직원과 부서코드, 직급코드가 같은 사람을 출력하고 싶다.
SELECT emp_name FROM employee
WHERE dept_code =
(SELECT dept_code FROM employee WHERE emp_name='이태림')
AND job_code =
(SELECT job_code FROM employee WHERE emp_name='이태림');
그러나 마찬가지로 단일 행 쿼리 두개를 쓰는 것이기 때문에 효율적이지 못하다.
다중 열 서브쿼리를 이용하여 쿼리문을 만들어야 하는데, dept_code와 job_code 두개 값 비교를 어떻게 해야할까?
서브쿼리가 여러 열이면 비교 대상도 괄호로 묶어 여러개로 만들어주면 된다.
SELECT dept_code, job_code, emp_name FROM employee
WHERE (dept_code,job_code) =
(SELECT dept_code, job_code FROM employee WHERE emp_name='이태림');
④ 다중 행 다중 열 서브쿼리
다중 행 쿼리문에 IN을 하면 쉽게 나타낼 수 있다.
-- 직급 별 최소 급여를 받는 직원의 직급코드, 이름, 급여를 출력하세요.
SELECT job_code, emp_name, salary FROM employee
WHERE (job_code, salary)
IN (SELECT job_code, MIN(salary) FROM employee GROUP BY job_code);
댓글남기기