🌞Outer join
- 조인할 때에 두 개의 테이블 중에 조건을 만족하지 않더라도 포함시키고자 할 때에 사용한다.
🌈left outer join
- from 절을 기준으로 왼쪽에 있는 테이블은 조건을 만족하지 않더라도 모두 포함
🍁left outer join의 형식
select 컬럼1, 컬럼2
from 테이블1 left outer join 테이블2
on 조건식;- from절을 기준으로 왼쪽에 있는 테이블 1의 내용은 조건을 만족하지 않더라도 모두 출력
예시) 고객의 이름과 고객이 주문한 도서의 판매가격을 출력. 주문내역이 없는 고객의 이름도 출력
select name, saleprice
from customer left outer join outers
on customer.custid = outers.custid;🌈right outer join
- from 절을 기준으로 오른쪽에 있는 테이블은 조건을 만족하지 않더라도 모두 포함
🍁right outer join의 형식
select 컬럼1, 컬럼2
from 테이블1 right outer join 테이블2
on 조건식;- from절을 기준으로 오른쪽에 있는 테이블2의 내용은 조건을 만족하지 않더라도 모두 출력
예시) 고객의 이름과 고객이 주문한 도서의 판매가격을 출력. 주문내역이 없는 고객의 이름도 출력
select name, saleprice
from outers right outer join customer
on customer.custid = outers.custid;🌈full outer join
- 양쪽 테이블을 모두 포함
🌞NVL 함수
- 특정 칼럼의 값이 null인 경우 다른 값으로 대체하는 함수이다.
nvl(컬럼이름, 대체할 값)예시) 출판사 별로 총 판매한 도서의 수, 총 판매 금액을 출력. 단, 판매되지 않는 출판사도 포함
select publicsher, count(saleprice), nvl(sum(saleprice), 0)
from book left outer join orders
on book.bookid = orders.bookid
group by publicsher;예시) 부서 별로 근무자의 수, 평균급여를 출력하세요. 근무자가 없는 부서명도 출력하세요.
select dname, count(eno), nvl(avg(salary), 0)
from dept d left outer join emp e
on d.dno = e.dno
group by dname;🌞self join
- 테이블 하나에서 어떤 컬럼이 자신의 또 다른 컬럼의 값을 참조하는 경우
- 실제로 물리적으로는 테이블이 하나인데 마치 두 개인 것 처럼 애칭을 주어 조인하는 것을 말한다.
예시) 모든 직원의 이름과 관리자 이름을 출력
select 사원.ename ename, 관리자.ename mname
from emp 사원, emp 관리자
where 사원.mgr = 관리자.eno;예시) 고객명과 출판사명을 입력받아 해당 고객에 해당 출판사에 출간하는 도서의 구매내역을 출력합니다. 고객번호, 고객명, 출판사명, 도서명, 도서가격, 구매가격, 구매일을 출력. 단, 최근의 구매일 순으로 출력하고 동일할 때에는 도서명 순으로 출력
select c.custid, name, publicsher, b.bookname, price, saleprice, orderdate
from customer c, book b, orders o
where c.custid = o.custid and b.bookid = o.bookid and name = '박지성' and publicsher = '굿스포츠'
order by orderdate desc, b.bookname;🌞서브쿼리(부속질의, 하위질의, 중첩질의)
- sql 문장 안에 포함되는 또 다른 sql문을 말한다.
🌈서브쿼리가 올 수 있는 곳
- select절, from 절, where 절
예시) 도서를 구매한 적이 있는 고객의 이름을 출력
<서브쿼리>
select name
from customer
where custid in (select custid from orders);
<조인>
select distinct name
from customer c, orders o
where c.custid = o.custid;- 같은 문제를 조인으로도, 서브쿼리로도 표현할 수 있다.
- 자료의 수가 적을 때는 별 차이가 없지만 레코드의 수가 많을 때에는 성능에 차이가 있다.
- 서브쿼리가 조인보다는 더 성능적인 면에서는 효율성이 높다.
예시) 평균급여보다 더 높은 급여를 받는 직원들의 이름, 급여를 출력하세요.
select ename, salary
from emp
where salary > (select avg(salary) from emp);예시) '대한미디어'에서 출간하는 도서를 구매한 적이 있는 고객의 이름을 출력
select name
from customer
where custid in ( select custid
from orders
where bookid in ( select bookid
from book
where publicsher = '대한미디어'));🌞상관 서브쿼리
- 메인쿼리와 서브쿼리에 사용되는 테이블의 컬럼을 조건식에 서로 관련을 맺고있을 때에 사용한다.
- 서브쿼리의 조건식에 메인쿼리의 테이블과 조건식이 필요한 경우를 말한다.
- 단독 서브쿼리는 메인쿼리와 상관없이 단독으로 실행이 가능한 서브쿼리이지만, '상관서브쿼리'는 메인쿼리의 값을 받아 처리하는 서브쿼리이다.
- 가장 먼저, 메인쿼리가 실행되고 그 후에 서브쿼리가 실행된다.
🌈상관 서브쿼리 형식
select 컬럼1, 컬럼2,...
from 테이블1
(select 컬럼3 from 테이블2 where 테이블1.컬럼 = 테이블2.컬럼5);예시) 출판사별로 출판사의 평균 도서 가격보다 더 비싼 도서 이름을 출력
select b1.bookname
from book b1
where b1.price > (select avg(b2.price)
from book b2
where b1.publicsher = b2.publicsher );🌞집합연산
🌈UNION(합집합)
- 중복을 제거한 결과의 합을 검색
🌈UNION ALL(합집합)
- 중복을 포함한 결과의 합을 검색
🌈INTERSECT(교집합)
- 양쪽 모두에서 포함된 행을 검색
🌈MINUS(차집합)
- 첫 번째 검색 결과에서 두 번째 검색 결과를 제외한 나머지를 검색
예시) 주문하지 않는 고객의 이름을 출력
select name from customer
minus
select name from customer where custid in (select distinct custid from orders);- 모든고객이름 minus 주문한 고객이름
- 모든 고객의 이름에서 주문한 고객의 이름을 빼줌
🌞exists
- 서브쿼리의 결과의 레코드가 존재한다면 메인쿼리를 실행한다.
- 그렇기 때문에 반드시 상관 서브쿼리가 되어야 한다.
- 서브쿼리의 결과가 "한 건이라도 존재하면" true, 없으면 false를 반환
🌈형식
select 컬럼1, 컬럼2
from 테이블1
(select 컬럼3 from 테이블2 where 테이블1.컬럼1 = 테이블2.컬럼4);예시) 연습) 주문이 있는 고객이름과 주소를 출력
select name, address
from customer c
where exists (select * from orders o
where c.custid = o.custid);NAME ADDRESS
---------- --------------------
박지성 영국 맨체스타
김연아 대한민국 서울
장미란 대한민국 강원도
추신수 미국 클리블랜드
박세리 대한민국 대전- 서브쿼리의 orders 테이블에 데이터가 한 건이라도 존재해야 데이터가 조회된다.
- 따라서, 서브쿼리에 있는 값이 존재하면 그 값을 메인쿼리에 반영하여 name과 address의 목록이 출력된다.
- 주문이 있는 고객이름과 주소만을 출력하게 된다.
🌞연습문제
👑연습문제1👑
- 마당서점의 고객이 요구하는 다음 질문에 대해 sql 문을 작성하세요.
(1) 박지성이 구매한 도서의 출판사 수
(2) 박지성이 구매한 도서의 이름, 가격, 정가와 판매가격의 차이
(3) 박지성이 구매하지 않은 도서의 이름
(1) select count(distinct publicsher)
from book
where bookid in (select bookid
from orders
where custid = (select custid
from customer
where name = '박지성'));
(2) select bookname, price, (price - saleprice) as 차이
from book b, customer c, orders o
where name = '박지성' and
b.bookid = o.bookid and c.custid = o.custid;
(3) select bookname
from book
where bookid not in (select bookid
from orders
where custid = (select custid
from customer
where name = '박지성'));
꾸준히 성장하는 개발자✨