3개 이상 테이블 Outer join 하기

형식 

//outer join 을 하고 이 결과를 가상의 테이블로 보고,(왼쪽 테이블이 된다.
select 컬럼(들)
from  테이블1 left outer join 테이블2
on 조건식

//그 결과를 다시 outer join 한다.
left outer join 테이블3
on 조건식;

 

예시

• 고객의 이름과 고객이 주문한 도서 이름을 출력한다.
• (단, 주문이 없는 고객이름도 출력한다.)
• 또, 고객 이름 순으로 정렬한다.

select name, nvl(bookname, '주문내역 없음') bookname
from customer c left outer join orders o
on c.custid = o.custid
left outer join book b
on o.bookid = b.bookid
order by name;

 

 

where 조건식 같이 사용하기

형식

select 컬럼(들)
from 테이블1 left outer join 테이블2
on 조건식
left outer join 테이블3
on 조건식
where 조건식

 

예시

• '대한미디어'나 '이상미디어', '굿스포츠'의 도서를 구매한 고객의 이름과 출판사명, 주문한 도서명을 출력하시오. 주문이 없는 고객의 이름도 출력하고 고객 이름 순으로 출력합니다.

select name, publisher, bookname
from customer c left outer join orders o
on c.custid = o.custid
left outer join book b
on o.bookid = b.bookid
where publisher in ('대한미디어', '이상미디어', '굿스포츠')
order by name;

 

ㄴ 이렇게 칠 수 있다고 생각하겠으나, 사실 주문 내역이 없는 고객 이름이 출력되지 않는다.

where는 inner 조인으로 동작하기 때문에 양쪽 테이블에 모두 만족하는 레코드만 검색된다.

 

해결 방법 1. is null 구문을 사용한다.

select name, publisher, bookname
from customer c left outer join orders o
on c.custid = o.custid
left outer join book b
on o.bookid = b.bookid
where publisher in ('대한미디어', '이상미디어', '굿스포츠') 
or publisher is null
order by name;

 

해결 방법 2. 서브 쿼리 사용

• 테이블이 오는 자리에 sql 을 넣는다.
• 서브쿼리(= 중첩질의): sql 문 안에 들어가는 또 다른 sql 문

select name, publisher, bookname
from customer c left outer join orders o
on c.custid = o.custid
left outer join 
(select * from book where publisher in ('대한미디어', '이상미디어', '굿스포츠')) b
on o.bookid = b.bookid
order by name;

• 문제점 : 이미 주문 내역이 있는 경우에도 나타나는 경우가 있다.
• NAME       PUBLISHER            BOOKNAME
  ---------- -------------------- --------------------
  김동규
  김민
  김연아     굿스포츠             피겨 교본
  김연아
  김철
  박세리
  박지성     대한미디어           축구의 이해
  박지성
  장미란
  장미란     이상미디어           야구를 부탁해
  장미란     굿스포츠             역도 단계별 기술
  추신수     이상미디어           야구의 추억
  추신수     이상미디어           야구를 부탁해
  
  13 행이 선택되었습니다.

 

서브쿼리

• sql 문장 안에 포함되는 또 다른 sql 
• 가장 비싼 도서의 이름
•  괄호 안에 같아야 하는 컬럼과 결과값이 나오도록 써준다. 괄호 안에서는 select 컬럼 부분에서부터 신경써서 써준다.

select max(price) from book;
select bookname from book where price = 35000;

//여기서 where price = 35000
//여기서 35000은 아래 쿼리에서 나온 값이므로

select max(price) from book; 
//을 대신 사용한다.

 

서브쿼리 위치

• select 절
• from 절
• where 절

예시

• 고객과 주문으로부터 고객이름, 도서번호를 출력(결과 A) 

select name, bookid
from cutomer c left outer join orders o
on c.custid = o.custid;

• 도서테이블에 '대한 미디어'나 '이상미디어', '굿스포츠'의 출판사명, 도서명, 도서 번호를 출력(결과 B) 

select publisher, bookname, bookid
from book
where publisher in ('대한미디어', '이상미디어', '굿스포츠');

• 서브 쿼리를 이용

select name, publisher, bookname
from () A left outer join () B
on A.bookid = B.bookid;

select name, publisher, bookname
from (
    select name, bookid
    from customer c left outer join orders o
    on c.custid = o.custid
) A left outer join (
    select publisher, bookname, bookid
    from book
    where publisher in ('대한미디어', '이상미디어', '굿스포츠')
) B
on A.bookid = B.bookid;

• 도서와 주문 테이블로부터 출판사가 '이상미디어', '대한미디어', '굿 스포츠'인 고객번호, 도서명, 출판사명

select custid, bookname, publisher
from book b, orders o
where b.bookid = o.bookid
and publisher in ('이상미디어','대한미디어', '굿스포츠');

• 도서와 주문 테이블로부터 출판사가 '이상미디어', '대한미디어', '굿 스포츠'인 고객번호, 도서명, 출판사명을 출력.
  주문이 없는 고객 이름도 출력하고 고객 이름 순으로 출력한다

select name, publisher, bookname
from customer c left outer join
(
    select custid, bookname, publisher
    from book b, orders o
    where b.bookid = o.bookid
    and publisher in ('이상미디어','대한미디어', '굿스포츠')
) o
on c.custid = o.custid
order by name;

 

서브쿼리 vs 조인

• 데이터 건수가 많을 때는 join 보다는 서브 쿼리가 조인보다 검색 성능이 좋다.

 

예제

• 구매한 내역이 있는 고객의 이름 출력
• 조인

select distinct name from customer c, orders o
where c.custid = o.custid;

• 서브쿼리

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

 

• '대한미디어'의 도서를 구매한 고객의 이름을 출력
• 조인

select distinct name from 
customer c, orders o, book b
where c.custid = o.custid and
b.bookid = o.bookid and
publisher = '대한미디어';

• 서브쿼리

/*
선작업
insert into orders values(11, 2, 3, 22000, sysdate);
insert into orders values(12, 1, 4, 35000, sysdate);
insert into orders values(13, 4, 4, 36000, sysdate);
insert into orders values(14, 5, 4, 36000, sysdate);
*/

select name from customer c
where custid in
(
	select custid
    from book b, orders o
    where b.bookid = o.bookid and
    publisher = '대한미디어'
);

select bookid from book where publisher = '대한미디어'; => A

select distinct custid from orders
where bookid in (select bookid from book where publisher = '대한미디어'); => B

select name from customer
where custid in(
	select distinct custid from orders
	where bookid in (select bookid from book where publisher = '대한미디어')
);

 

• 부하직원이 있는 직원의 이름을 출력(조인+서브쿼리)

select distinct m.ename
from emp e, emp m
where e.mgr = m.eno;

select ename
from emp
where eno in (
	select mgr from emp
);

 

• 장미란 고객의 주문 내역을 출력

select o.* from orders o, customer c
where o.custid = c.custid and
name = '장미란';

select * from orders
where custid = (
	select custid from customer
    where name='장미란'
);

 

• '장미란' 고객의 총 주문 건수, 총 주문 금액, 평균 주문 금액을 출력(서브쿼리 이용)

select count(*) count, sum(saleprice) sum, avg(saleprice) avg
from orders
where custid = 
(
	select custid from customer where name = '장미란'
);

 

• 고객별 총 주문 건수, 총 주문 금액, 평균 주문 금액을 출력. 단, 주문이 없는 사람도 출력하고, 총 주문 건수가 높은 순으로 출력. 동일할ㅇ 때는 총 주문 금액이 높은 순으로 출력

select name, count(saleprice) count, nvl(sum(saleprice),0) sum, nvl(avg(saleprice),0) avg
from orders o right outer join customer c
on c.custid = o.custid
group by name
order by count desc, sum desc;

• '굿스포츠', '대한미디어', '이상미디어' 출판사들의 도서를 구매한 고객들의 고객별 총 주문 건수, 총 주문 금액, 평균 주문금액을 출력한다. 단, 총 주문 건수가 높은 순으로 출력하고 동일할 때에는 총 주문 금액이 높은 순으로 출력한다.

select name, count(saleprice) count, nvl(sum(saleprice),0) sum, nvl(avg(saleprice),0) avg
from orders o, customer c
where o.custid = c.custid and
bookid in (
	select bookid from book
    where publisher in ('굿스포츠', '대한미디어', '이상미디어')
)
group by name
order by count desc, sum desc;

select name, count(*) count, sum(saleprice) sum, avg(saleprice) avg
from customer c, orders o, book b
where c.custid = o.custid and
o.bookid = b.bookid and
publisher in ('대한미디어', '이상미디어', '굿스포츠')
group by name
order by count desc, sum desc;

select custid, count(saleprice), sum(saleprice), avg(saleprice)
from orders
group by custid; --> 1

//where 절에 서브쿼리 넣기
select custid, count(saleprice), sum(saleprice), avg(saleprice)
from orders where bookid in
(select bookid from book where publisher in ('굿스포츠', '대한미디어', '이상미디어'))
group by custid;

//select 절에 서브쿼리 넣기
select (select name from customer c where c.custid = o.custid) name,  
count(saleprice) count, sum(saleprice) sum, avg(saleprice) avg 
from orders o where bookid in
(select bookid from book where publisher in ('굿스포츠', '대한미디어', '이상미디어'))
group by custid;

 

• '굿스포츠', '대한미디어', '이상미디어' 출판사들의 도서를 구매한 고객들의 고객별 총 주문 건수, 총 주문 금액, 평균 주문금액을 출력한다. 단, 총 주문 건수가 높은 순으로 출력하고 동일할 때에는 총 주문 금액이 높은 순으로 출력한다.
  단, 총 주문 건수가 3건이상인 고객만 출력한다.

select (select name from customer c where c.custid = o.custid) name,  
count(saleprice) count, sum(saleprice) sum, avg(saleprice) avg 
from orders o where bookid in
(select bookid from book where publisher in ('굿스포츠', '대한미디어', '이상미디어'))
group by custid
having count(saleprice) >= 3;

 

• 마당 서점의 도서의 총 개수

select count(bookid) from book;

• 마당 서점에 도서를 출고하는 출판사의 총 개수

select count(distinct publisher) from book;

• 모든 고객의 이름, 주소

select name, address from customer;

• 2024/06/01~06/17 사이에 주문 받은 도서의 주문번호

select orderid from orders
where orderdate between '2024/06/01' and '2024/06/17';

• 2024/06/01 ~06/17 사이에 주문받은 도서를 제외한 주문번호(not between도 가능) 

select orderid from orders
where orderdate < '2024/06/01' 
or orderdate > '2024/06/17';

• 성이 '김'씨인 고객의 이름과 주소

select name, address from customer
where name like '김%';

• 성이 '김'씨이고 이름이 '아'로 끝나는 고객의 이름과 주소

select name, address from customer
where name like '김%아';

• 주문하지 않은 고객의 이름

select name from customer c 
left outer join orders o
on c.custid = o.custid
where orderdate is null;

• 주문 금액의 총액과 주문의 평균 금액

select sum(saleprice) sum, avg(saleprice) avg
from orders;

• 고객의 이름과 고객별 구매액

select name, sum(saleprice) saleprice 
from customer c, orders o
where c.custid = o.custid
group by name;

 

• 고객의 이름과 고객이 구매한 도서목록(고객별로 구매한 도서목록)

select name, bookname from customer c, orders o, book b
where c.custid = o.custid and
o.bookid = b.bookid;

• 도서의 가격과 판매 가격의 차이가 가장 많은 주문

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

• 도서의 판매액 평균보다 자신의 구매액 평균이 더 높은 고객의 이름

select name from (select name, avg(saleprice) avg
	from customer c, orders o
	where c.custid = o.custid
	group by name )
	where avg > (select avg(saleprice) from orders);

 

다중 행

서브 쿼리의 결과가 2건 이상인 것을 뜻하며, = 이 아닌 in을 사용해야한다.

=> '=' 을 쓸 경우 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다라는 오류가 뜬다.

=> 서브 쿼리의 건수가 여러 건일 떄는 '다중 행 연산자'를 사용해야 한다.