오라클(Oracle) 데이터베이스(Database, DB) 조인(join)과 부질의(서브쿼리, sub query)

조인(JOIN)의 특징

   - JOIN : 두 개 이상의 테이블을 연결하여 하나의 테이블처럼 사용
   - INNER JOIN, OUTER JOIN, FULL OUTER JOIN  
   - INNER JOIN : 두개 테이블의 컬럼 값이 같을 때 (공통된 컬럼)
     

     EQUI JOIN, NATURAL JOIN, JOIN ~ ON , JOIN ~ USING

 

부질의(서브쿼리, SUB-QUARY)의  특징 

   - 서브쿼리는 연산자와 같은 비교 또는 조회 대상의 오른쪽에 놓이며 괄호( )로 묶어서 사용한다. 
   - 특수만 몇몇 경우를 제외하고 서브쿼리는 ORDER BY절을 사용할 수 없다.
   - 서브쿼리의 SELECT절에 명시한 컬럼(오른쪽 SAL)은 메인쿼리의 비교대상(왼쪽SAL)과 같은 자료형과 같은

     개수로 지정해야 한다.
    

 

테이블 정보

-- 테이블 삭제 : 나중에 만들어진 테이블부터 삭제
DROP TABLE EMP;
DROP TABLE DEPT;

-- DEPT 테이블 생성
CREATE TABLE DEPT(
    DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
	DNAME VARCHAR2(14) ,
	LOC VARCHAR2(13) 
);
  
-- EMP 테이블 생성  
CREATE TABLE EMP(
    EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
	ENAME VARCHAR2(10),
	JOB VARCHAR2(9),
	MGR NUMBER(4),
	HIREDATE DATE,
	SAL NUMBER(7,2),
	COMM NUMBER(7,2),
	DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
);
  
-- DEPT 데이터 삽입
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

-- EMP 데이터 삽입
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
  

COMMIT;

EMP 테이블

DEPT 테이블

조인(JOIN) 예제

 

Q1. 사원번호, 사원이름, 부서이름을 조회(natural join)

 

SELECT EMP.EMPNO, ENAME, DNAME FROM EMP NATURAL JOIN DEPT;

 

 

 

 

Q2. 사원번호, 사원이름, 부서이름을 조회(equi join)

 

SELECT EMP.EMPNO, ENAME, DNAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;

 

 

 

 

Q3. 사원번호, 사원이름, 부서이름을 조회(join ~ using)

 

SELECT EMP.EMPNO, ENAME, DNAME FROM EMP JOIN DEPT USING(DEPTNO);

 

 

 

 

Q4. 사원번호, 사원이름, 부서이름을 조회(join ~ on)

 

SELECT EMP.EMPNO, ENAME, DNAME FROM EMP JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;

 

 

 

Q.5 지역이 NEW YORK인 사원의 사원번호, 사원이름, 지역을 조회(equi join)

 

SELECT ENAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO AND LOC='NEW YORK';

 

 

 

 

 

Q6. 부서이름이 ACCOUTING이거나 지역이 NEW YORK인 사원의 사원번호, 사원이름, 부서이름, 지역을

     사원번호 순으로 조회(join ~ using)

SELECT EMPNO, ENAME, DNAME, LOC FROM EMP JOIN DEPT USING(DEPTNO) WHERE DNAME = 'ACCOUTING' OR LOC = 'NEW YORK' ORDER BY EMPNO;

 

 

 

 

Q7. 사원번호, 사원이름을 부하직원수가 많은 순으로 조회(join ~ on)

SELECT A.EMPNO, A.ENAME, COUNT(*) FROM EMP A JOIN EMP B ON A.EMPNO = B.MGR GROUP BY A.EMPNO, A.ENAME ORDER BY 3 DESC;

 

 

 

 

 

 

 

부질의(SUB-QUARY) 예제

 

Q1. EMP테이블에서 사원이름이 JONES의 급여를 조회

SELECT ENAME,SAL FROM EMP WHERE ENAME = 'JONES';

 

 

 

 

Q2. 급여가 2975보다 많은 사원의 모든 정보를 조회

SELECT * FROM EMP WHERE SAL > 2975;

 

 

 

 

 

Q3. 급여가 JONES보다 많은 사원의 모든 정보를 조회

SELECT * FROM EMP WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME = 'JONES');
--Q2의 결과와 같지만 부질의를 이용한 결과이다.

 

 

 

 

 

 

 Q4. 받는 급여가 평균 급여 이상인 사원의 정보를 조회

SELECT * FROM EMP WHERE SAL >= (SELECT AVG(SAL) FROM EMP);

 

 

 

 

 

 

 Q5. 각 부서별 최고급여와 동일한 급여를 받는 사원의 정보를 조회

SELECT * FROM EMP WHERE SAL IN (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);

OR

SELECT * FROM EMP WHERE (DEPTNO,SAL)  IN (SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO);

 

 

 

 

 

Q6.  CLARK 사원보다 늦게 입사한 사원의 정보를 조회

 

SELECT * FROM EMP WHERE HIREDATE > (SELECT HIREDATE FROM EMP WHERE ENAME = 'CLARK');

 

 

 

 

Q7. 20번 부서 사원의 직무와 같은 직무인 다른 부서의 사원정보를 조회

 

SELECT * FROM EMP WHERE DEPTNO <> 20 AND JOB IN (SELECT JOB FROM EMP WHERE DEPTNO = 20);