[데이터베이스] 서브쿼리 (중첩질의), 셀프조인, left(right) outer join 정리노트

* 서브쿼리 (중첩질의) 란?

- sql 문안에 포함되는 또다른 sql문을 말합니다.
- 조인보다 서브쿼리를 사용하는 것이 데이터 효율성 면에서 더욱 효율적입니다.
- 서브쿼리를 사용할 때에는 출력될 결과의 칼럼들이 셀렉트 절에서 동일한 테이블에 속해있는 칼럼들로 구성된 경우에 사용합니다.

 

- 서브쿼리 사용방법 

select name from customer where custid in(select distinct custid from orders);
: 주문한 적이 있는 고객의 이름을 출력

select name from customer where custid not in(select distinct custid from orders);

: 주문한 내역이 없는 고객의 이름을 출력



쿼리 연습)

1. 도서번호가 1인 도서의 이름
select bookname from book where bookid=1;

2. 가격이 20000원 이상인 도서의 이름
select bookname from book where price>=20000;

3. 박지성의 총구매액
조인 : select sum(saleprice) from orders o, customer c where o.custid=c.custid and name='박지성'; 
서브쿼리 : select sum(saleprice) from orders where custid in (select custid from customer where name='박지성');

4. 박지성이 구매한 도서의 수, 단 중복을 제거하고 도서의 수를 출력합니다.
조인 : select count(distinct bookid) from customer c, orders o where c.custid=o.custid and name ='박지성';
서브쿼리 : select count(distinct bookid) from orders where custid in (select custid from customer where name ='박지성');

5. 박지성이 구매한 도서의 출판사 수
조인 : select count(distinct publisher) from orders o, customer c, book b where o.custid=c.custid and o.bookid=b.bookid and name='박지성'; 

6. 박지성이 구매한 도서의 이름, 가격 정가와 판매가격 차이
조인 : select distinct name, bookname, price-saleprice from orders o, customer c, book b where o.custid=c.custid and o.bookid=b.bookid and name='박지성';

7. 박지성이 구매하지 않은 도서의 이름
서브쿼리: select distinct bookname from book where bookname not in (select distinct bookname from orders o, customer c, book b where o.custid=c.custid and o.bookid=b.bookid and name='박지성');

8. 2023/04/01 2023/04/15 사이에 주문받은 도서의 주문번호
select orderid from orders where orderdate between '2023/04/01' and '2023/04/15 ';

9.  2023/04/01 2023/04/15 사이에 주문받은 도서를 제외한 도서의 주문번호
select orderid from orders where orderid not in (select orderid from orders where orderdate between '2023/04/01' and '2023/04/15 ');

10. 성이 김씨인 고객의 이름과 주소 
select name, address from customer where name like '김%';

11. 성이 김씨이고 아로 끝나는 고객 고객의 이름과 주소 
select name, address from customer where name like '김%아';

12. 주문하지 않은 고객의 이름
select name from customer where custid not in (select custid from orders); 

13. 주문 금액의 총액과 주문 평균금액
select sum(saleprice), avg(saleprice) from orders;

14. 고객의 이름과 고객별 구매액
select name, sum(saleprice) from orders o, customer c where o.custid=c.custid group by name; 

//name 에 그룹시키기 

15. 도서의 가격과 판매가격의 차이가 가장 많은 주문 정보 출력
1) 실제 도서가격과 판매가격의 차이가 가장 큰 금액

select max(price-saleprice) from book b, orders o where b.bookid=o.bookid;

2) 실제 도서가격과 판매가격의 차이가 가장 큰 금액의 주문번호

select orderid from book b, orders o where b.bookid=o.bookid and (price-saleprice) 
in (select max(price-saleprice) from book b, orders o where b.bookied=o.bookied);

3) 실제 도서가격과 판매가격의 차이가 가장 큰 금액의 주문번호의 주문정보 출력

select * from orders where orderid in (select orderid from book b, orders o where b.bookid=o.bookid and (price-saleprice) in (select max(price-saleprice) from book b, orders o where b.bookid=o.bookid));

16. 도서의 판매액 평균보다 자신의 구매액 평균이 더 높은 고객이름
1) 도서 판매액 평균
select avg(saleprice) from orders;

2) 도서 판매액 평균보다 자신의 구매액 평균이 더 높은 사람
select name from customer c, orders o where c.custid=o.custid 
group by name having avg(saleprice) > (select avg(saleprice) from orders);

 

---------------------------------------------------------------------------

 

- 회사 ver.


연습) 기획팀에 근무하는 모든 직원들의 사원번호, 이름, 입사일을 출력합니다. 입사일 순으로 출력합니다.
select eno, ename, hiredate from emp where dno =(select dno from dept where dname ='기획팀');

연습) 서교동에 근무하는 모든 대리와 사원의 부서번호, 부서명, 사원번호, 사원이름, 전화, 주소를 출력.. 단, 부서번호 순으로 출력
select e.dno, dname, eno, ename, phone, addr from emp e, dept d where e.dno=d.dno and dloc='서교동' and job in('대리','사원') order by e.dno;

연습) 손지민의 부서정보를 출력
select * from dept where dno in(select dno from emp where ename='손지민');

연습) 부서별로 평균 급여를 출력
select avg(salary) from emp e, dept d where e.dno=d.dno group by dno order by dname;

연습) 부서별 직원수와 평균급여를 출력
select dname 부서명, count(*) 사원수, avg(salary) "평균 급여"  from emp e, dept d where e.dno = d.dno group by dname;

// 컬럼에 공백이 필요한 애칭을 줄 때에만 "" 이용하고 나머지는  ' ' 로 사용합니다.

연습) 직책별 직원의 수를 출력
select job, count(*) from emp group by job;

연습) 직책이 사원인 직원들에 대해 부서별 사원 수와 평균급여를 출력, 단 사원의 수가 2명이상인 부서만 출력 사원의 수가 높은 순으로 출력
select dname, count(*), avg(salary) from emp e, dept d where e.dno=d.dno and job='사원' group by dname having count(*)>=2 order by count(*) desc;

 

 

* outer join

두개의 테이블 중에 조건을 만족하지 않는 행도 포함시키고자 할 때 사용합니다.

- left outer join
-> from 절을 기준으로 왼쪽에 있는 테이블은 모두 출력

- right outer join
-> from 절을 기준으로 오른쪽에 있는 테이블은 모두 출력

 

 

* outer join 형식

select 컬럼(들)
from 테이블1 left[right] outer join 테이블2
on 조인식

연습) 도서명, 도서가격, 판매가격, 판매일을 출력합니다.
단 판매가 안된 모든 도서의 정보도 출력합니다.
select bookname, price, saleprice, orderdate from book b left outer join orders o on b.bookied=o.bookied;

연습) 고객번호,고객명, 주문번호, 구매가격, 구매일을 출력 ( 주문이 있지 않은 고객정보도 출력)
select c.custid, name, o.orderid, saleprice, orderdate from customer c left outer join orders o on c.custid=o.custid;

* 3개의 테이블을 outer join 하기

select 컬럼(들)
from 테이블1 left[right] outer join 테이블2
on 조인식
left[right] outer join 테이블3
on 조인식
=> 지금까지 실행할 결과를 left로 보고

연습) 고객명, 도서명, 출판사, 구매가격, 구매일을 출력
select name, bookname, publisher, saleprice, orderdate from customer c left outer join orders o on c.custid=o.custid left outer join book b on b.bookid=o.bookid;



* self join 

실제로는 테이블이 한개인데 하나의 테이블에 있는 칼럼이 같은 테이블에 있는 다른 속성을 참조할 때에 실제로 물리적으로는 하나의 테이블인데 애칭을 주어 조인하는 것을 말합니다.

emp ==> 사원
emp ==> 관리자

연습) 모든 직원들의 이름과 관리자이름을 출력하시오.
select e.사원이름, m.관리자이름 from emp e, emp m where e.mgr=m.eno;

-> e.mgr 관리자 번호가 m.eno 사원번호와 같다는 뜻.


연습)  구은현의 부하직원들의 사원명, 부서명, 급여, 직책, 입사일을 출력합니다. 단 입사일 순으로 출력합니다.
select e.ename 사원명, dname 부서명, e.salary 급여, e.job 직책, e.hiredate 입사일 from emp e, emp m, dept d 
where e.mgr = m.eno and e.dno = d.dno and m.ename='구은현';

연습) '서교동'에 근무하는 모든 직원들에 대해 부서번호, 사원명, 부서명, 관리자명을 출력합니다.
select e.dno, e.ename, dname, m.ename from emp e, emp m, dept d where e.mgr= m.eno and e.dno = d.dno and dloc='서교동'; 

연습) '개발'팀에 근무하는 2020/01/01 이후에 입사한 직원들의 사원번호, 부서명, 부서위치, 관리자명을 출력합니다.
select e.eno, dname dloc, m.ename from emp e, emp m, dept d where e.mgr = m.eno and e.dno = d.dno and dname like '개발%' and e.hiredate >= '2020/01/01';

연습) 관리자보다 입사일이 빠른 직원들의 부서번호, 부서명, 사원명, 입사일, 관리자명, 관리자의 입사일을 출력합니다.
select e.dno, dname, e.ename, e.hiredate 입사일, m.ename, m.hiredate 관리자입사일 from emp e, emp m, dept d where e.mgr = m.eno and e.dno = d.dno and e.hiredate < m.hiredate;

 

연습) 근무지와 거주지가 같은 직원들의 사원번호, 사원명, 부서명, 부서위치, 관리자명을 출력합니다. (관리자가 없는 사람도 출력합니다)
select e.eno, e.ename, dname, dloc, m.ename from emp e left outer join emp m on e.mgr = m.eno left outer join dept d on e.dno = d.dno;