select (join부터)

●조인

-두 개 이상의 테이블에 있는 컬럼의 값을 한번에 가져오기 위해 사용하는 것이 조인이다.

-select 컬럼명 from 테이블1, 테이블2;

-두 개 이상의 테이블에서 가져온 결과 중에 정확한 결과만 가져오기 위해 공통 부분을 이용한 조건문이 반드시 필요하다.

 

 

--사원테이블(emp)과 부서테이블(dept)을 join한다.

 

select * from emp,dept;

 

select *

from emp, dept

where emp.deptno = dept.deptno;

 

select *

from emp a1, dept a2

where a1.deptno = a2.deptno;

 

--사원의 사원번호, 이름, 근무부서 이름을 가져온다.

select empno, ename, dname

from emp a1, dept a2

where a1.deptno = a2.deptno;

 

select a1.empno, a1.ename, a2.dname

from emp a1, dept a2

where a1.deptno = a2.deptno;

 

--사원의 사원번호, 이름, 근무지역을 가져온다.

select a1.empno, a1.ename, a2.loc

from emp a1, dept a2

where a1.deptno = a2.deptno;

 

--DALLAS에 근무하고 있는 사원들의 사원번호, 이름, 직무를 가져온다.

select a1.empno, a1.ename, a1.job

from emp a1, dept a2

where a1.deptno = a2.deptno and a2.loc = 'DALLAS';

 

--SALES 부서에 근무하고 있는 사원들의 급여 평균을 가져온다.

select avg(a1.sal)

from emp a1, dept a2

where a1.deptno = a2.deptno and a2.dname = 'SALES';

 

--1982년에 입사한 사원들의 사원번호, 이름, 입사일, 근무부서이름을 가져온다.

select a1.empno, a1.ename, a1.hiredate, a2.dname

from emp a1, dept a2

where a1.deptno = a2.deptno and trunc(a1.hiredate,'YYYY') = '1982/01/01';

--각 사원들의 사원번호, 이름, 급여, 급여등급을 가져온다.

select a1.empno, a1.ename, a1.sal, a2.grade

from emp a1, salgrade a2

where a1.sal >= a2.losal and a1.sal <= a2.hisal;

 

select a1.empno, a1.ename, a1.sal, a2.grade

from emp a1, salgrade a2

where a1.sal between a2.losal and a2.hisal;

 

--SALES부서에 근무하고 있는 사원의 사원번호, 이름, 급여등급을 가져온다.

select  a1.empno, a1.ename, a2.grade, a3.dname

from emp a1, salgrade a2, dept a3

where a1.deptno = a3.deptno and a1.sal between a2.losal and a2.hisal

        and a3.dname = 'SALES';

--각 급여 등급별 급여의 총합과 평균, 사원의 수, 최대급여, 최소급여를 가져온다.

select grade,sum(sal),avg(sal),count(*),max(sal),min(sal)

from emp a1, salgrade a2

where a1.sal between a2.losal and a2.hisal

group by grade;

--급여등급이 4등급인 사원들의 사원번호, 이름, 급여, 근무부서 이름, 근무지역을 가져온다.

select grade, a1.empno, a1.ename, a1.sal, a2.deptno, a2.loc

from emp a1, dept a2, salgrade a3

where a1.deptno = a2.deptno and a1.sal between a3.losal and a3.hisal and a3.grade =4;

 

●self join

-같은 테이블을 두 번 이상 조인하는 것을 의미한다.

EX)

--SMITH사원의 사원번호, 이름, 직속상관 이름을 가져온다.

select * from emp;

 

--이거는 조인 안쓰고 해본거

select empno, ename, (select ename from emp where empno = (select mgr from emp where ename = 'SMITH'))

from emp

where ename= 'SMITH';

--a1 : SMITH 사원의 정보  a2 : 직속상관의 정보

select a1.empno, a1.ename, a2.ename

from emp a1, emp a2

where a1.mgr = a2.empno and a1.ename = 'SMITH';

 

--FORD 사원 밑에서 일하는 사원들의 사원번호, 이름, 직무를 가져온다.

--a1 : 부하직원의 정보

--a2 : FORD의 정보

select a1.empno, a1.ename, a1.job

from emp a1, emp a2

where a1.mgr = a2.empno and a2.ename = 'FORD';

 

--SMITH 사원의 직속상관과 동일한 직무를 가지고 있는 사원들의 사원번호, 이름, 직무를 가져온다.

--a1 : SMITH사원

--a2 : SMITH사원의 직속상관

--a3 : 사원들

select a3.empno, a3.ename, a3.job

from emp a1, emp a2, emp a3

where a1.mgr = a2.empno and a2.job = a3.job and a1.ename = 'SMITH';

 

 

 

●outer join

-조인 조건에 해당하지 않기 때문에 결과에 포함되지 않는 로우까지 가져오는 조인이다.

EX)

--각 사원의 이름, 사원번호, 직장상사 이름을 가져온다. 단 직속상관이 없는 사원도 가져온다.

--a1 : 각 사원의 정보

--a2 : 직장상사의 정보

select a1.ename, a1.empno, a2.ename

from emp a1, emp a2

where a1.mgr = a2.empno(+);

 

--모든 부서의 소속사원의 근무부서명, 사원번호, 사원이름, 급여를 가져온다.

select distinct(deptno) from emp;

select * from dept;

 

select a2.dname, a1.empno, a1.ename, a1.sal

from emp a1, dept a2

where a1.deptno(+) = a2.deptno;

 

●서브쿼리

-쿼리문 안에 들어가는 쿼리문을 서브쿼리라고 한다.

-쿼리문 작성시 사용되는 값을 다른 쿼리문을 통해 구해야 할 경우 사용한다.

--SMITH사원이 근무하고 있는 부서의 이름을 가져온다.

select a2.dname

from emp a1, dept a2

where a1.deptno = a2.deptno and a1.ename = 'SMITH';

 

select dname

from dept

where deptno = (select deptno

                from emp

                where ename='SMITH');

 

--SMITH와 같은 부서에 근무하고 있는 사원들의 사원번호, 이름, 급여액, 부서이름을 가져온다.

select a3.empno, a3.ename, a3.sal, a2.dname

from emp a1, dept a2, emp a3

where a1.deptno =  a2.deptno and a1.ename = 'SMITH' and a1.deptno = a3.deptno;

 

select ename, empno, sal, a2.dname

from emp a1, dept a2

where a1.deptno = a2.deptno and a1.deptno = (select deptno

                                            from emp

                                            where ename = 'SMITH');

 

select deptno

from emp

where ename = 'SMITH';

 

 

--MARTIN과 같은 직무를 가지고 있는 사원들의 사원번호, 이름, 직무를 가져온다.

select a2.empno, a2.ename, a2.job

from emp a1, emp a2

where a1.job = a2.job and a1.ename = 'MARTIN';

 

select empno, ename, job

from emp

where job = (select job

            from emp

            where ename = 'MARTIN'

            );

 

--ALLEN과 같은 직속상관을 가진 사원들의 사원번호, 이름, 직속상관이름을 가져온다.

select a2.empno, a2.ename, a1.mgr, a2.mgr, a3.ename

from emp a1, emp a2, emp a3

where a1.mgr = a2.mgr and a1.ename = 'ALLEN' and a1.mgr = a3.empno;

 

select a1.empno, a1.ename, a2.ename

from emp a1, emp a2

where a1.mgr = a2.empno and a1.mgr = (

                                        select mgr

                                        from emp

                                        where ename = 'ALLEN');

                                       

--WARD와 같은 부서에 근무하고 있는 사원들의 사원번호, 이름, 부서번호를 가져온다,

--a1 : ward의 정보, a2 : ward와 같은 부서에 근무하고 있는 사원들의 정보

select a2.empno, a2.ename, a2.deptno

from emp a1, emp a2

where a1.deptno = a2.deptno and a1.ename = 'WARD';

 

select empno, ename, deptno

from emp

where deptno = (select deptno

                from emp

                where ename = 'WARD');

               

--SALESMAN의 평균 급여보다 많이 받는 사원들의 사원번호, 이름, 급여를 가져온다.

select empno, ename, sal

from emp

where sal > (select avg(sal)

            from emp

            where job = 'SALESMAN');

 

--DALLAS 지역에 근무하는 사원들의 평균 급여를 가져온다.

select avg(sal)

from emp a1, dept a2

where a1.deptno = a2.deptno and a2.loc = 'DALLAS';

 

select avg(sal)

from emp

where deptno = (select deptno

                    from dept

                    where loc = 'DALLAS');

--SALES 부서에 근무하는 사원들의 사원번호, 이름, 근무지역을 가져온다.

select a1.empno, a1.ename, a2.loc

from emp a1, dept a2

where a1.deptno = a2.deptno and a2.dname = 'SALES';

 

select a1.empno, a1.ename, a2.loc

from emp a1, dept a2

where a1.deptno = a2.deptno and a1.deptno = (select deptno

                                            from dept

                                            where dname = 'SALES');

                                           

                                           

--CHICAGO 지역에 근무하는 사원들 중 BLAKE이 직속 상관인 사원들의 사원번호, 이름, 직무를 가져온다.

--a1 : 시카고 지역의 정보 a2 :시카고 근무사원들 a3 : BLAKE이 직속상관인 사원들

select a2.empno, a2.ename, a2.job

from dept a1, emp a2, emp a3

where a1.deptno = a2.deptno and a2.mgr = a3.empno and a3.ename = 'BLAKE' and a1.loc='CHICAGO';

 

select empno, ename, job

from emp

where deptno = (select deptno

                from dept

                where loc = 'CHICAGO'

                )

    and mgr = (select empno

                from emp

                where ename = 'BLAKE');

 

●결과가 하나 이상인 서브쿼리

-서브쿼리를 통해 가져온 결과가 하나이상인 경우 결과를 모두 만족하거나 결과중 하나만 만족하거나 해야 하는 경우가 있다.

-이때 다음과 같은 연산자를 사용하면 된다.

IN : 서브쿼리의 결과 중 하나라도 일치하면 조건은 참이 된다.

ANY, SOME : 서브쿼리의 결과와 하나이상 일치하면 조건은 참이 된다.

ALL : 서브쿼리의 결과와 모두 일치해야 조건은 참이 된다.

...

 

--3000 이상의 급여를 받는 사원들과 같은 부서에 근무하고 있는 사원의 사원번호, 이름, 급여를 가져온다.

select empno, ename, sal

from emp

where deptno in(select deptno

                from emp

                where sal >= 3000);

 

--직무가 CLERK인 사원과 동일한 부서에 근무하고 있는 사원들의 사원번호, 이름, 입사일을 가져온다.

select empno, ename, hiredate

from emp

where deptno in (

            select deptno

            from emp

            where job = 'CLERK'

                );

 

--KING을 직속상관으로 가지고 있는 사원들이 근무하고 있는 근무 부서명, 지역을 가지고 온다.

select  a1.ename,a3.dname, a3.loc

from emp a1, emp a2, dept a3

where a1.mgr = a2.empno and a2.ename = 'KING' and a1.deptno = a3.deptno;

 

select a1.ename, a2.dname, a2.loc

from emp a1, dept a2

where a1.deptno = a2.deptno and a1.mgr = (select empno

                                            from emp

                                            where ename='KING');

                                           

select dname, loc

from dept

where deptno in (select deptno

                from emp

                where mgr = (select empno

                            from emp

                            where ename = 'KING'));

 

--CLERK들의 직속상관의 사원번호, 이름, 급여를 가져온다.

select a2.empno, a2.ename, a2.sal

from emp a1, emp a2

where a1.mgr = a2.empno and a1.job = 'CLERK';

 

select empno, ename, sal

from emp

where empno in (select mgr

                from emp

                where job = 'CLERK');

 

--각 부서별 급여 평균보다 더 많이 받는 사원의 사원번호, 이름, 급여를 가져온다.

select empno, ename, sal

from emp

where sal  > all (select avg(sal)

                from emp

                group by deptno

                );

               

select empno, ename, sal

from emp

where sal  > (select max(avg(sal))

                from emp

                group by deptno

                );

 

--각 부서별 급여 최저치보다 더 많이 받는 사원들의 사원번호, 이름, 급여를 가져온다.

 

select empno, ename, sal

from emp

where sal > all(select min(sal)

                from emp

                group by deptno);

 

select empno, ename, sal

from emp

where sal >     (select max(min(sal))

                from emp

                group by deptno);

 

select min(sal)

from emp

group by deptno;

 

--SALESMAN보다 급여를 적게받는 사원들의 사원번호, 이름, 급여를 가져온다.

select empno, ename, sal

from emp

where sal < all(select sal

            from emp

            where job = 'SALESMAN');

 

select empno, ename, sal

from emp

where sal < (select min(sal)

            from emp

            where job = 'SALESMAN');

 

select sal

from emp

where job = 'SALESMAN';

 

https://rongscodinghistory.tistory.com/90

 

●SET

-두 select문을 통해 얻어온 결과에 대해 집합 연산을 할 수 있는 명령문이다.

-두 select문을 통해 가져온 컬럼의 형태가 완전히 일치해야 한다.

-UNION : 합집합 (중복된 데이터 배제)

-UNION ALL : 합닙합. 중복된 데이터를 모두 가져온다.

-INTERSECT : 교집합

-MINUS : 차집합

 

--UNION 

select empno, ename, job, deptno 
from emp 
where deptno = 10 
union 
select empno, ename, job, deptno 
from emp 
where job = 'CLERK'; 

--UNION ALL 
select empno, ename, job, deptno 
from emp 
where deptno = 10 
union all 
select empno, ename, job, deptno 
from emp 
where job = 'CLERK'; 

--INTERSECT 
select empno, ename, job, deptno 
from emp 
where deptno = 10 
intersect 
select empno, ename, job, deptno 
from emp 
where job = 'CLERK'; 

--minus (위에 있는 select문에서 아래 있는 select문을 뺀 것) 
select empno, ename, job, deptno 
from emp 
where deptno = 10 
minus 
select empno, ename, job, deptno 
from emp 
where job = 'CLERK';

 

인프런 - 윤재성의 Oracle SQL Database 11g PL/SQL Developer