MySQL) SUBQUERY 예제

SUBQUERY - 부질의
    : 서브 쿼리(subqueries)는 쿼리 안에 포함되어 있는 또 다른 쿼리를 말한다.
    : ()괄호로 묶으며 괄호 안에 쿼리가 먼저 실행된 후 그 결과를 메인 쿼리의 조건으로 주로 사용한다. 
    : 서브쿼리의 결과 행이 한 개일 때  비교연산자 사용한다.
    : 서브쿼리의 결과 행이 여러 개 일 때는 ANY, ALL, IN 연산자를 사용한다. 
    : 주로 SELECT에서 많이 사용하지만 CREATE, INSERT, UPDATE ,DELTE, 
           HAVING, WHERE , FROM ,ORDER 에서도 사용가능 하다.

 

EMP테이블 실습을 위해서 이전에 EMP관련 테이블 생성 쿼리

-- 부서 테이블 생성
CREATE TABLE DEPT(
    DEPTNO int  PRIMARY KEY,
	DNAME VARCHAR(20) ,
	LOC VARCHAR(20) 
) ;

-- 사원테이블
CREATE TABLE EMP(
    EMPNO int  PRIMARY KEY,
	ENAME VARCHAR(10) not null,
	JOB VARCHAR(9),
	MGR  int,
	HIREDATE DATEtime,
	SAL int,
	COMM int,
	DEPTNO int  REFERENCES DEPT(DEPTNO)
);

-- 부서 레코드 추가
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');
    
-- 사원 레코드 추가
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902, STR_TO_DATE('17-12-1980','%d-%m-%Y'),800,NULL,20);
INSERT INTO EMP VALUES(7499,'ALLEN','SALESMAN',7698,STR_TO_DATE('20-2-1981','%d-%m-%Y'),1600,300,30);
INSERT INTO EMP VALUES(7521,'WARD','SALESMAN',7698,STR_TO_DATE('22-2-1981','%d-%m-%Y'),1250,500,30);
INSERT INTO EMP VALUES(7566,'JONES','MANAGER',7839,STR_TO_DATE('2-4-1981','%d-%m-%Y'),2975,NULL,20);
INSERT INTO EMP VALUES(7654,'MARTIN','SALESMAN',7698,STR_TO_DATE('28-9-1981','%d-%m-%Y'),1250,1400,30);
INSERT INTO EMP VALUES(7698,'BLAKE','MANAGER',7839,STR_TO_DATE('1-5-1981','%d-%m-%Y'),2850,NULL,30);
INSERT INTO EMP VALUES(7782,'CLARK','MANAGER',7839,STR_TO_DATE('9-6-1981','%d-%m-%Y'),2450,NULL,10);
INSERT INTO EMP VALUES(7788,'SCOTT','ANALYST',7566,STR_TO_DATE('13-7-1987','%d-%m-%Y'),3000,NULL,20);
INSERT INTO EMP VALUES(7839,'KING','PRESIDENT',NULL,STR_TO_DATE('17-11-1981','%d-%m-%Y'),5000,NULL,10);
INSERT INTO EMP VALUES(7844,'TURNER','SALESMAN',7698,STR_TO_DATE('8-9-1981','%d-%m-%Y'),1500,0,30);
INSERT INTO EMP VALUES(7876,'ADAMS','CLERK',7788,STR_TO_DATE('13-7-1987','%d-%m-%Y' ),1100,NULL,20);
INSERT INTO EMP VALUES(7900,'JAMES','CLERK',7698,STR_TO_DATE('3-12-1981','%d-%m-%Y'),950,NULL,30);
INSERT INTO EMP VALUES(7902,'FORD','ANALYST',7566,STR_TO_DATE('3-12-1981','%d-%m-%Y'),3000,NULL,20);
INSERT INTO EMP VALUES(7934,'MILLER','CLERK',7782,STR_TO_DATE('23-1-1982','%d-%m-%Y'),1300,NULL,10);

-- 급여 등급 테이블
CREATE TABLE SALGRADE( 
    GRADE int primary key,
	LOSAL int,
	HISAL int 
    );
 
 -- 급여 등급 정보 추가
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
COMMIT;

-- 레코드 검색
select * from emp;
select * from dept;
select * from salgrade;

서브쿼리 예제 실습 

- EMP테이블에서 평균 급여보다 더 많이 받는 사원 검색
  -- 1) 평균 급여를 구한다.
     select avg(sal) from emp;
  
  -- 2) 1)에서 구한 평균급여를 조건으로 사용한다. 
   select * from emp where sal > (select avg(sal) from emp);
                  
-- JOB에 'A'문자열이 들어간 사원의 부서와 같은 곳에서 근무하는 사원의 부서이름 검색하고 싶다. 
 -- 1) JOB에 'A'문자열이 들어간 사원의 부서번호를 구한다.
     select distinct deptno from emp where job like '%a%';
 
  -- 2)  1)에서 구한 부서번호에 해당하는 부서이름을 dept에서 검색한다.
    select dname from dept 
    where deptno in (select distinct deptno from emp where job like '%a%');
  
 -- 부서번호가 30인 사원들에 급여중에서 가장 많이 받는 사원보다 더 많이 받는 사원정보를 검색하고 싶다. 
   -- 1) 부서번호가 30인 사원들의 급여중에서 최대값을 구한다.
      select max(sal) from emp where deptno=30;
     
   -- 2) 1)을 조건으로 사용한다.
       select * from emp where sal > (select max(sal) from emp where deptno=30);
      select * from emp where sal > all (select sal from emp where deptno=30);
      
      
/*
 검색결과와 하나이상이 일치하면 참
 ex) 컬럼명 < any(100, 200, 300) => 최대값보다 작다
      컬럼명 > any(100, 200, 300) => 최소값 보다 크다


- 검색결과의 모든 값이 일치하면 참.
 ex) 컬럼명 < all(100, 200, 300) => 최소값보다 작다
     컬럼명 > all(100, 200, 300) => 최대값보다 크다.
*/



-- SUBQUERY를 이용한 INSERT
-- 테이블 복사
CREATE TABLE SUB_EMP
AS SELECT * FROM EMP WHERE 1=0;

SELECT * FROM SUB_EMP;

-- 특정한 칼럼만 다른테이블로부터 가져와서 INSERT
insert into sub_emp(empno, ename, job, sal)
(select empno, ename, job, sal from emp where sal >=3000);

SET SQL_SAFE_UPDATES = 0;  

select * from sub_emp where empno=7902;
select * from emp where empno=7900;
-- SUBQUERY를 UPDATE
-- EX) EMP테이블에서 EMPNO 7900인 사원의 JOB, MGR, DEPTNO로 SUB_EMP테이블의 7566의 사원의 정보로 수정해보자.
update sub_emp
set job=(select job from emp where empno=7900) , 
    mgr=(select mgr from emp where empno=7900) , 
    deptno=(select deptno from emp where empno=7900)
where empno=7902;

-- SUBQUERY 대신 JOIN문장으로 변경하면
update sub_emp sub join emp e on e.empno=7499
set sub.job=e.job , sub.mgr=e.mgr , sub.deptno=e.deptno
where sub.empno=7839;

-- SUBQUERY를 DELETE
  -- EX) EMP테이블의 평균 급여를 조건으로 사용해서 평균급여보다 많이 받는 사원들을 삭제한다. 
  delete from sub_emp
  where sal > (select avg(sal) from emp);