having

group by 절에 나타난 결과에 대하여 조건식을 주고자 할 때 사용

코드

// having 사용
// group by 절에 나타난 결과에 대하여 조건식을 주고자 할 때 사용
select b.bookname "도서명", count(custId) "판매건수"
from orders o
join book b on b.bookid = o.bookid 
where b.publisher in('이상미디어','대한미디어')
group by b.bookname having count(custId) >= 2
order by count(custId);

---

Self 조인

실제로는 하나의 테이블인데 컬럼의 값이 그 테이블의 다른 컬럼을 참조할 때 사용하는 조건이다.

worker 테이블의 mgr은 worker 테이블의 workerId를 참조하고 있다. 만약 모든 직원의 이름과 관리자 이름을 출력해야 한다면 self 조인할 수 있다. 실제로 물리적으로 테이블은 workerId라는 것 한개만 있다. 이것을 한번은 직원이라고 보고 한번은 관리자로 보아 두 개를 조인하여 직원이름과 관리자 이름을 조회할 수 있다.

코드

select e.name "직원", m.name "관리자" 
from worker e
join worker m on e.mgr = m.workerId;

select w.workerId "사원번호", w.name"사원명", m.name"관리자명", w.id"부서번호", d.name"부서명"
from worker w
join worker m on w.mgr = m.workerId
join department d on d.id = w.id
where d.name like '%개발%'
order by d.id;

select w.workerId "사원번호", w.name "사원명", m.name "관리자명", w.hireDate "입사일", m.hireDate "관리자 입사일"
from worker w
join worker m on w.mgr = m.workerid
join department d on d.id = w.id
where d.name like '%개발%' and w.hireDate < m.hireDate;

select w.workerId, w.name, m.name, w.hireDate, m.hireDate, w.salary, m.salary
from worker w
join worker m on w.mgr = m.workerid
join department d on w.id = d.id
where d.location in ('판교','종각') and w.job in ('사원','대리')
and w.hiredate < m.hiredate or w.salary > m.salary
order by w.hireDate, w.salary desc;

---

Outer Join

여태까지 했던 조인은 Inner Join이였다. 조건이 만족한 테이블만 조인해오는 것이다. Outer Join은 조건이 만족하지 않아도 예를 들어, 연관관계가 없어도 조회해온다.

💡 두개의 테이블에 왼쪽이나 오른쪽에 레코드는 조건을 만족하지 않더라도 모두 검색

• left outer join : 조건을 만족하지 않더라도 왼쪽의 테이블은 무조건 검색
• Right outer join : 조건을 만족하지 않더라도 오른쪽의 테이블은 무조건 검색

select 컬럼이름(들)
from 테이블1 
right|left outer join 테이블2 on 조건식
[where 테이블];

테이블1의 모든 데이터를 조회하려면 left, 테이블2의 모든 테이블을 조회하려면 right

코드

select c.name, count(o.custId) 
from orders o 
right outer join customer c on c.custid = o.custid
group by c.name;

여기서는 모든 고객별로 주문건수를 가져오려고 하기 때문에 테이블2 즉, customer을 전부 가져와야 하기 때문에 right를 사용해야 한다. 이렇게 하면 조건( on c.custid = o.custid)에 맞지 않아도 모든 고객들을 가져온다.

select m.name, count(w.workerId)
from worker w
right outer join worker m on w.mgr = m.workerid
group by m.name;

select d.name, count(w.workerId)
from worker w
right outer join department d on d.id = w.id
group by d.name;

// count 외에 집계함수는 없으면 null이 나오는데
// oracle에서는 null을 0으로 주는 방법이 있다.
// 그것이 nvl(컬럼, 값)이다.
select b.publisher"출판사", nvl(sum(o.custId),0) cnt, nvl(sum(o.salePrice),0)sum
from book b
left outer join orders o on o.bookid = b.bookid
group by b.publisher;