인라인 뷰(가상 테이블)

뷰 : 사용자에게 맞춤형으로 제공되는 가상 테이블

지금까지는 FROM절에 테이블명을 직접 명시하고 사용했다
그러나 테이블에 컬럼들이 너무 많으면 작업의 효율성이 떨어진다
따라서 일부 컬럼들이 필요할때 효율적으로 사용할 수 있는 서브쿼리다

SELECT E10.EMPNO, E10.ENAME, E10.DEPTNO, D.DNAME, D.LOC
FROM (SELECT * FROM EMP WHERE DEPTNO = 10) E10,
(SELECT * FROM DEPT) D
/*서브쿼리의 데이터를 가상의 테이블인 E10, D로 명명하고 사용하겠다 라는 의미*/
WHERE E10.DEPTNO = D.DEPTNO;

서브쿼리를 FROM절에 사용해서 가상의 테이블처럼 사용할 수 있다

WITH 절

WITH
E10 AS (SELECT * FROM EMP WHERE DEPTNO = 10),
D AS (SELECT * FROM DEPT)
SELECT E10.EMPNO, E10.ENAME, E10.DEPTNO, D.DNAME, D.LOC
FROM E10, D
WHERE E10.DEPTNO = D.DEPTNO

인라인뷰를 만드는 또다른 방법이다 잘 사용하진 않는다
값은 위와 같다

스칼라 서브쿼리

SELECT절에 넣는 서브쿼리
반드시 하나의 결과(하나의 컬럼)만 반환하도록 작성해야한다

SELECT EMPNO, ENAME, JOB, SAL,
(SELECT GRADE FROM SALGRADE WHERE E.SAL BETWEEN LOSAL AND HISAL) AS SALGRADE,
DEPTNO,
(SELECT DNAME FROM DEPT WHERE E.DEPTNO = DEPT.DEPTNO) AS DNAME
FROM EMP E;

첫번째 서브쿼리는 SALGRADE테이블의 GRADE를 가져오는데 JOIN까지 활용해서 조건이 달려있다

E.SAL BETWEEN LOSAL AND HISAL -> E.SAL의 범위를 SALGRADE테이블의 LOSAL 과 HISAL의
사이의 범위로 지정을 하고 그 범위에 있는 GRADE만 하나의 컬럼으로 보고 SELECT문에서
사용하겠다는 의미(컬럼을 하나만 반환해야한다 두개이상이 되면 작동이 불가능하다)

• SELECT절에 가져올것이 많고 또 그중 하나의 컬럼에 여러 조건이 달려있을때
  메인쿼리에서 특정 컬럼에만 조건을 붙이는게 까다롭기 때문에 스칼라 서브쿼리를 활용하면
  원하는 형태의 컬럼을 메인쿼리문의 SELECT절에 넣는게 가능해진다

예제

• 쿼리문 작성할때 먼저 작성해야 할것
  1. 가져와야할 테이블을 먼저 적는다
  2. 가져온 테이블들을 조인한다
  3. 나머지 조건들을 작성한다

/*예1) 전체 사원 중 ALLEN 과 같은 직책(JOB)인 사원들의 사원정보, 부서 정보를 출력*/

SELECT *
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO AND 
E.JOB = (SELECT JOB FROM EMP WHERE ENAME LIKE('ALLEN'));


/*예2) 전체 사원의 평균급여 보다 높은 급여를 받는 사원들의 사원정보, 부서 정보, 급여등급 출력 
      ( 출력시 급여가 많은 순으로 정렬하되, 급여가 같을 시 사원 번호기준 오름차순) */
      
SELECT E.EMPNO, E.ENAME, E.SAL, D.DNAME, D.LOC, S.GRADE
FROM EMP E, DEPT D, SALGRADE S
WHERE E.DEPTNO = D.DEPTNO AND E.SAL BETWEEN S.LOSAL AND S.HISAL
/*키값을 = 으로 1:1 매칭했다면 E.SAL의 키값은 S.LOSAL과 S.HISAL의 범위를
1:범위로 매칭해서 조인을 했다*/
AND SAL > (SELECT AVG(SAL) FROM EMP)
ORDER BY E.SAL DESC, E.EMPNO;

/*예3) 10번 부서에 근무하는 사원 중 30번 부서에는 존재하지 않는 직책을 가진 
사원들의 사원정보, 부서정보 출력*/

SELECT E.EMPNO, E.ENAME, E.JOB, E.DEPTNO, D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO AND E.DEPTNO = 10 AND
JOB NOT IN (SELECT DISTINCT JOB FROM EMP WHERE DEPTNO = 30);


/*예4) 직책이 SALESMAN 인 사람들의 최고 급여보다 높은 급여를 받는 사원들의 사원정보, 급여등급 출력
      (사원번호를 기준으로 오름차순 정렬)*/
/*다중행 함수 사용방법*/

SELECT E.EMPNO, E.ENAME, E.JOB, E.DEPTNO, E.SAL, S.GRADE
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL AND 
E.SAL > (SELECT MAX(SAL) FROM EMP WHERE JOB LIKE('SALESMAN'))
ORDER BY E.EMPNO;