그룹함수
연습문제
-- 연습문제 6) 직급별(JOB) 사원의 최저 급여를(SALARY) 출력하세요,
-- 단, 관리자를(MANAGER) 알 수 없는 사원 및 최저 급여가 2000 미만인 그룹은 제외시키고,
-- 급여에 대한 내림차순으로 정렬해서 출력하세요
-- 힌트) 0) ~ 컬럼별 : GROUP BY 컬럼 사용
-- 1) 관리자가 있는 사원 : WHERE MANAGER IS NOT NULL
-- 2) 최저 급여가(MIN(SALARY)) >= 2000 이상인 그룹 :
-- HAVING MIN(SALARY)>= 2000
-- 3) ORDER BY MIN(SALARY) DESC;SELECT JOB, MIN(SALARY) FROM EMPLOYEE
WHERE MANAGER IS NOT NULL
GROUP BY JOB
HAVING MIN(SALARY) >= 2000
ORDER BY MIN(salary)DESC;"관리자를(MANAGER) 알 수 없는 사원"은 그룹에 해당하는 조건이 아니고 개인에 해당하는 조건이므로 WHERE를 사용해야하고, "최저 급여가 2000 미만인 그룹은 제외"에서는 그룹에 대한 조건이므로 HAVING을 사용해야한다.
그리고 HAVING 조건은 GROUP BY뒤에 와야하고 OREDER도 마지막에 와야한다.
JOIN 두개 이상의 SQL문 1개로 만들기
JOIN을 사용하는 경우
사원 번호에 따른 사원의 부서명을 보고싶다. 근데 EMPLOYEE테이블에는 부서명 컬럼이 없다. 하지만 우리가 가지고 있는데 DEPARTMENT테이블에는 부서명 컬럼이 있기에 그걸 이용해서 조회를 해보자.
SELECT DNAME FROM EMPLOYEE
WHERE ENO = 7788;
DNAME을 찾을수 없다고 나온다.
SELECT *
FROM DEPARTMENT
WHERE DNO = 20;
하지만 DEPARTMENT에는 있다.
그런데 이렇게 두번 실행을 하면통신을 두번 해야해서 성능이 아주 안 좋다. 그래서 이 두개를 한 번에 할 수 있게 해주는 방법 JOIN이다.
EQUAL JOIN 2개의 테이블 같은 데이터끼리 연결
서로 다른 테이블에 이름이 같은 컬럼이 있다면, 이를 연결해주는 JOIN쿼리를 EQUAL조인이라고 한다.
-- 예제1) 사원번호가(ENO) 7788 인 사원의 부서명은(DNAME) 뭘까요?
SELECT EMPLOYEE.*, DEPARTMENT.* FROM EMPLOYEE, DEPARTMENT
WHERE EMPLOYEE.DNO = DEPARTMENT.DNO -- 공통 컬럼이 있어야함. 대부분의 JOIN을 이렇게 함- SELECT *을 하면 모든 컬럼을 가져오지만 서로 다른 테이블에 같은 이름의 컬럼이 충돌을 일으키므로 각각 *을 해주어야한다.
- FROM 에도 두 테이블을 모두 적어준다.
- WHERE를 이용해서 같은 컬럼을 표현해준다. 여기서 JOIN을 해주는 것이다.
건수가 작은 테이블이 큰 테이블을 따라갈려고 하다보니 작은 테이블의 값이 중복이 될 수있다.
DEPARTMENT가 건수가 더 적지만 더 많은 건수를 가진 EMPLOYEE테이블을 따라간다.
SELECT EMPLOYEE.*, DEPARTMENT.* FROM EMPLOYEE, DEPARTMENT
WHERE EMPLOYEE.DNO = DEPARTMENT.DNO
AND EMPLOYEE.ENO = 7788;
사원번호가 7788인 사원의 부서는 RESEARCH부서이다.
테이블 이름 변경
컬럼의 이름은 AS 예약어로 변경했다. 테이블은 AS없이 그냥 바로 별명을 붙여주면된다.
SELECT EM.*, DE.* FROM EMPLOYEE EM, DEPARTMENT DE
WHERE EM.DNO = DE.DNO
AND EM.ENO = 7788;그 밖에 JOIN들
카티션 프러덕트 : 공통컬럼으로 조건없이 초인하기
성능이 대폭 저하되어서 사용 하지 않는것이 좋다.
SELECT EM.*, DE.*
FROM EMPLOYEE EM, DEPARTMENT DE이렇게 하면 되는데 조건을 안 걸어서 많은 양의 데이터가 발생하게 되어서 성능이 저하된다.
범위 JOIN 특정 수치 사이의 값만 조인
특정 범위 사이의 값들을 모두 출력한다. BETWEEN을 이용해서 조인한다.
--급여 등급 테이블(SALGRADE)의 낮은 등급(LOSAL) ~ 높은 등급(HISAL)
--사이의 급여에 대해서 사원테이블과 조인하여 사원정보를 화면에 출력
SELECT ENAME, SALARY, GRADE
FROM EMPLOYEE EM
,SALGRADE SA
WHERE EM.SALARY BETWEEN SA.LOSAL AND SA.HISAL;
OUTER JOIN : EQUAL 조인에서 NULL값 보기
특수 조인에서 나온다면 이 조인이 대부분이다. EQUAL조인은 NULL값을 자동으로 제외하는데, NULL값까지 보고싶을 경우 OUTER조인을 사용하면 된다.
참고로 같은 테이블에서도 JOIN이 가능하다. 이것을 셀프조인이라고한다.
SELECT EM.ENAME, MA.ENAME FROM EMPLOYEE EM, EMPLOYEE MA
WHERE EM.MANAGER = MA.MANAGER;EQUAL조인으로 셀프 조인을 했다.
NULL값을 보여주지 않는다.
SELECT EM.ENAME, MA.ENAME FROM EMPLOYEE EM, EMPLOYEE MA
WHERE EM.MANAGER = MA.MANAGER(+);(+)을 조건문에서 NULL을 보고싶은 항에 넣어주면 OUTER조인이 되는 것이다.
NULL값이 나타나는 것을 볼 수 있다.
조인시 조회 성능 팁
- 컬럼당 인덱스를 줄 수 있는데 이때 인덱스를 이용해서 조인을 해주면 성능을 올릴수 있다.
- FROM뒤에 테이블을 넣을때, 큰 테이블을 앞에 두고 작은 테이블은 뒤에 두는 것이 성능이 좋다.
sql-99 조인
세계Sql기구에서 만든 작성법
EQUAL조인
SELECT EM*, DE.*
FROM EMPLOYEE EM JOIN DEPARTMENT DE ON (EM.DNO = DE.DNO);JOIN과 ON을 사용해주어야하고 ()안에 조건문을 넣어주어야한다. EQUAL조인과 같지만 작성방법만 다르다.
OUTER조인
SELECT EM.ENAME
,MA.ENAME
FROM EMPLOYEE EM LEFT OUTER JOIN EMPLOYEE MA ON (EM.MANAGER = MA.MANAGER)JOIN과 ON을 사용해주어야하고 ()안에 조건문을 넣어주어야하는 건 같지만 , (+)을 사용하지 않고 OUTER JOIN을 이용해서 (+)을 대신한다. 그리고 (+)가 들어가야하는 항이 오른 쪽이면 LEFT OUTER JOIN, 왼쪽이면 RIGHT OUTER JOIN으로 반대로 적어주면 된다.
SUBQUERY
SQL안에 SQL이 있는 명령문이다.
특정 컬럼의 조건에 해당하는 다른 컬럼의 값과 비교가 필요할때 쿼리문을 한번만 작성해서 찾게 해준다.
SE
예를 들어 SCOTT의 월급보다 높게 받는 사람이 누가 있는지를 알려면 SCOTT의 월급을 검색하여 그 값을 얻고 다시 조건문으로 SCOTT월급보다 높은 월급을 받는 사람을 찾아야해서 쿼리문을 두번 써야한다.
단일행 SUBQUERY
서브쿼리의 결과가 하나만 나오는 것이다. =,<,> 을 사용해서 비교하면된다.
먼저 서브쿼리를 사용하지 않고 구해보자.
서브쿼리 사용 안함
SELECT SALARY FROM EMPLOYEE
WHERE ENAME ='SCOTT';
SELECT ENAME FROM EMPLOYEE
WHERE SALARY > 3000;먼저 찾은 값은 3000이고 3000을 이용해서 다시 사람을 찾아야한다.
서브쿼리 사용
SELECT ENAME
FROM EMPLOYEE
WHERE SALARY > (SELECT SALARY
FROM EMPLOYEE
WHERE ENAME ='SCOTT');3000이 들어가야하는 자리에 괄호를 치고 첫번째 쿼리를 넣어주면된다. 
결과가 잘 나온다.
팁
첫번째 쿼리의 WHERE과, 두번째 쿼리의 SELECT의 컬럼이 같아야한다.
특수한 서브쿼리
-- 1) SELECT 예약어 다음에도 서브쿼리 사용가능 : (서브쿼리)
-- 단점 : 성능 저하
SELECT 2*3 , (SELECT 4*3 FROM DUAL)
FROM DUAL;-- 2) FROM 예약어 다음에도 서브쿼리 사용 가능
SELECT EM.ENAME, EM.ENO
FROM (SELECT ENAME, ENO FROM EMPLOYEE) EM;