조인문법에는
1. ansi 표준문법 : join, on 키워드 사용
2. oracle 전용문법 : , (콤마 ), where절 사용
두가지 방법이 있다.
ansi표준문법의 경우 36, 35일차에 알아보았다.
오라클 표준문법을 ANSI표준문법과 비교해보겠다.
EQUI JOIN
1. INNER JOIN 이너조인
ANSI 표준문법은 회색, 오라클 전용 문법은 검정색 창 안에 적었다.
오라클 전용문법은 테이블 순서가 중요하지 않다.
select
e.emp_name,
d.dept_title
from
employee e inner join department d
on e.dept_code = d.dept_id;select
e.emp_name,
d.dept_title
from
employee e,department d
where
e.dept_code=d.dept_id;on에 쓸 것을 where에 적어준다. join대신 , (콤마) 로 대신한다.
2. OUTER JOIN 아우터조인
오라클 전용문법은 테이블 순서가 중요하지 않고, left join의 경우 오른쪽 컬럼에 (+)를, right join의 경우 왼쪽 컬럼에 (+)를 적는다.
( 기준 테이블의 반대 컬럼에 추가한다.)
select
emp_name,
dept_title
from
employee e left outer join department d
on e.dept_code = d.dept_id;select
emp_name,
dept_title
from
employee e,department d
where
e.dept_code = d.dept_id(+);위 join의 경우 left조인이기때문에 오른쪽인 d. dept_id에 (+)가 추가된 것을 확인할 수 있다.
select
d.dept_title,
e.emp_name
from
employee e right outer join department d
on e.dept_code = d.dept_id;select
d.dept_title,
e.emp_name
from
employee e,department d
where
e.dept_code(+) = d.dept_id;마찬가지로 위 조인은 right outer join이기 때문에 왼쪽의 e.dept_code에 (+)를 적어주었다.
* full outer join은 오라클 전용 문법이 없다.
3. CROSS JOIN 상호조인
오라클문법에서는 where조인 조건을 생략하면 된다.
select
*
from
employee e cross join department d;select
*
from
employee e , department d;
4. SELF JOIN 셀프조인
같은 테이블을 조인하는 셀프조인, 위의 방식들과 다를게 없다.
select
e.emp_name,
m.emp_name manager_name
from
employee e left join employee m
on e.manager_id = m.emp_id;
select
e.emp_name,
m.emp_name manager_name
from
employee e, employee m
where
e.manager_id=m.emp_id(+);
5. MULTIPLE JOIN 멀티플조인
위에서 사용한 방법들을 모두 혼합해서 쓰면 된다.
1. 순서 상관 X, 2. left join시 오른쪽 컬럼에 (+) , right join시 왼쪽 컬럼에 (+)
select
*
from
employee e
join job j
on e.job_code = j.job_code
left join department d
on e.dept_code = d.dept_id
left join location l
on d.location_id = l.local_code
where
j.job_name in ('대리', '과장')
and
l.local_name like 'ASIA%';select
*
from
employee e,job j , department d, location l --순서 중요치않음
where
e.job_code=j.job_code
and
e.dept_code=d.dept_id(+)
and
d.location_id=l.local_code(+)
and
j.job_name in ('대리','과장')
and
l.local_name like 'ASIA%';join내용도 where에 쓰고, 조건도 where에 쓴다는 점이 중요하게 봐야할 점이다.
NON-EQUI JOIN
동등연산자만 쓰지 않을 뿐이므로, where절에서 동등연산자 대신 쓰려는 연산자만 사용해주면 된다.
select*
from
employee e join sal_grade s
on e.salary between s.min_sal and s.max_sal;select *
from
employee e, sal_grade s
where
e.salary between s.min_sal and s.max_sal;
서브쿼리
- 서브쿼리란, 메인쿼리에 속한 쿼리를 의미한다. (종속 관계)
- 존재하지 않는 조건(직접적으로 처리 불가한 조건)에 근거해서 검색할 때 굉장히 유용하다.
- 메인쿼리 실행중에 서브쿼리를 만나면, 우선 서브쿼리 실행후, 그 결과를 메인 쿼리에 반환한다.
조건
1. 반드시 소괄호 안에 위치
2. order by 문법 사용 불가
3. 연산자 오른쪽에 작성할 것
|
유형 분류
|
||
|
결과값에 따른 유형
|
메인 쿼리 관계에 따른 구분
|
위치에 따른 구분
|
|
1. 단일행 단일컬럼 서브쿼리 (1행 1열)
|
1. 일반 서브쿼리
서브쿼리의 결과값을 메인쿼리에 통보 |
1. 스칼라 서브 쿼리
|
|
2. 다중행 단일컬럼 서브쿼리 (n행 1열)
|
||
|
3. 단일행 다중컬럼 서브쿼리(1행 n열)
|
2. 상호연관 서브쿼리
메인쿼리값을 가지고 서브쿼리 처리 후, 메인쿼리에 다시 통보 |
2. inline - view
|
|
4. 다중행 다중컬럼 서브쿼리(n행 m열)
|
||
단일행 단일컬럼 서브쿼리
말그대로 서브쿼리가 1행 1열인 경우.
ex ) 노옹철 사원의 관리자 이름 조회
1. 조인으로
select
m.emp_name manager_name
from
employee e left join employee m
on e.manager_id = m.emp_id
where
e.emp_name = '노옹철';2. 서브쿼리로
select
emp_name
from
employee m
where
emp_id = (
select
manager_id
from
employee e
where
emp_name = '노옹철'
);
결과는 모두 위와 같다.
서브쿼리의 경우, '노옹철 사원의 관리자' 를 하나의 서브쿼리로 만들어서 where문에 조건으로 넣었다.
하지만 아무리 봐도 조인이 더 간단해보인다. 🤔
ex 2) 윤은해와 같은 금액의 급여를 받는 사원 (윤은해 제외) 조회 - 사번, 사원명, 급여
select
emp_id,
emp_name,
salary
from
employee
where
salary = (
select
salary
from
employee
where
emp_name = '윤은해'
)
and
emp_name ^= '윤은해';
위 문제의 경우 where문 안에 '윤은해와 같은 금액의 급여'여야 한다는 조건을 서브쿼리로 넣었다.
윤은해의 급여를 모르는 이상, 서브쿼리를 이용해 같이 짤 수 밖에 없다.
다중행 단일컬럼 서브쿼리
서브쿼리 결과가 n행 1열인 경우이다.
값이 여러개이므로 =, !=, >, < 연산자 사용이 불가능하고 대신 in, any | some, all, exits 연산자를 사용할 수 있다.
서브쿼리의 결과는 여러행 이지만, in 연산자 안에서 가로로 나열된다.
ex ) 송종기, 하이유 사원과 같은 부서원 조회 - 사원명, 부서코드
서브쿼리 - 송중기, 하이유 사원의 dept_code
select
dept_code
from
employee
where
emp_name in ('송종기','하이유');
메인쿼리
select
emp_name,
dept_code
from
employee
where
dept_code in (
select
dept_code
from
employee
where
emp_name in ('송종기', '하이유')
);dept_code 가 '서브쿼리=송중기, 하이유 사원의 dept_code' 인 사원의 emp_name과 dept_code
다음과 같이 나오는 것을 확인할 수 있다.
연산자 any , some, all
any와 some은 같은 사용법을 가지고, 동일하다고 보면 된다.
1. any(some)
select
*
from
employee
where
salary > some(3000000, 4000000, 5000000);any (some)은 나열된 값 중 최소값보다 크다면 true를 반환한다. (한개라도 해당하면 true!)
|
= some(a,b,c,d)
|
a,b,c,d 중 하나라도 만족하는 값이 있으면 true (in이랑 쓰임새가 같음)
|
|
>some(a,b,c,d)
|
a,b,c,d,중 최소값보다 크면 true
|
|
>=some(a,b,c,d)
|
a,b,c,d,중 최소값보다 크거나 같으면 true
|
|
< some(a,b,c,d)
|
a,b,c,d,중 최대값보다 작으면 true
|
|
<= some(a,b,c,d)
|
a,b,c,d 중 최대값보다 작거나 같으면 true
|
|
<> some(a)
|
모든 값들 중 다른 값만 true (값이 하나일때만 사용이 가능함. 즉 사용하지 않음)
|
2. all
select
*
from
employee
where
salary > all(3000000, 4000000, 5000000);all은 나열된 값 중 최대값보다 크면 true를 반환한다. (모두 해당해야 true)
|
= all(a,b,c,d)
|
a,b,c,d 모두 만족하는 값이 있어야 true (쓰이지 않음)
|
|
>all(a,b,c,d)
|
a,b,c,d 중 최대값보다 크면 true
|
|
>=all(a,b,c,d)
|
a,b,c,d,중 최대값보다 크거나 같으면 true
|
|
< all(a,b,c,d)
|
a,b,c,d,중 최소값보다 작으면 true
|
|
<= all(a,b,c,d)
|
a,b,c,d 중 최소값보다 작거나 같으면 true
|
|
<> all(a)
|
모든 값들과 값이 다르면 결과를 리턴
|
ex ) 'D5'의 모든 부서원의 급여보다 많은 급여를 받는 사원 조회
서브쿼리 - D5 부서원들의 salary
select
salary
from
employee
where
dept_code='D5';메인쿼리 - '서브쿼리보다 salary가 높은 직원' 을 where문 조건으로 함
select
emp_name, salary
from
employee
where
salary > all(
select
salary
from
employee
where
dept_code = 'D5'
);
단일/다중행 다중컬럼 서브쿼리
- 서브쿼리 결과가 1행 n열인 경우 = 동등비교연산자 사용
- 서브쿼리 결과가 n행 m열인 경우 in 연산자 사용
- 1행 n열인 경우.
ex ) 퇴사한 직원이 1명 있을때, 이 사원과 같은 부서, 같은 직급의 사원을 조회 - 사원명, 직급코드, 부서코드, 퇴사여부
select * from employee where quit_yn = 'Y';
select
emp_name, dept_code, job_code
from
employee
where
(dept_code, job_code) = (
select
dept_code, job_code
from
employee
where
quit_yn = 'Y'
);
고두밋 오늘 퇴사
update
employee
set
quit_yn = 'Y',
quit_date = sysdate
where
emp_name = '고두밋';
commit;2행 2열 리턴
select
emp_name, dept_code, job_code
from
employee
where
(dept_code, job_code) in (
select
dept_code, job_code
from
employee
where
quit_yn = 'Y'
);
상호연관 서브쿼리
-상관 서브쿼리 라고도 부름.
-메인쿼리의 값을 전달받아 서브쿼리를 수행 후 결과값을 메인쿼리에 반환하는 쿼리.
-일반 서브쿼리는 블럭을 잡아서 단독으로 실행이 가능한 반면, 상관서브쿼리는 그렇지 않다.
-메인쿼리의 매행마다 다른 값을 서브쿼리에 전달한다.
ex ) 직급별 평균급여보다 많은 급여를 받는 사원 조회
1. 조인 버전
select
e.emp_name,
e.job_code,
e.salary
from
employee e join (
select
job_code,
trunc(avg(salary)) avg_sal
from
employee
group by
job_code
) j
on e.job_code = j.job_code
where
salary > avg_sal;2. 상관서브쿼리버전
select
emp_name,
job_code,
salary
from
employee e
where
salary > (
select
avg(salary)
from
employee
where
job_code = e.job_code
);
둘다 값은 똑같다.
실제로 단일 쿼리로 job_code별로 salary평균을 내보면,
J1 8000000
J2 4726666
J3 3600000
J4 2330000
J5 2820000
J6 2624373
J7 2017500
라는 값을 가진다.
그리고 결과와 비교해보면, 각 Job_code에서 평균을 넘는 사원만 출력되었음을 알 수 있다.
exits 연산자
exits(서브쿼리) 서브쿼리의 결과집합이 1행 이상인 경우 true, 0행이면 false를 처리
(true이면 해당 행을 결과집합에 포함한다는 뜻이고, false이면 해당 행을 결과집합에서 제외한다는 뜻이다.)
ex ) 부서테이블에서 실제 부서원이 존재하는 부서만 조회
select
*
from
department d
where
exists(
select
*
from
employee
where
dept_code = d.dept_id
);
not exits 연산자
exits와 반대로 서브쿼리가 행이 존재하면 false, 존재하지 않으면 true를 반환한다.
ex ) 최대급여 사원을 조회
select
*
from
employee e
where
not exists (
select
1
from
employee
where
salary > e.salary
);
스칼라 서브쿼리
scala값 (단일값)
select절에 사용된 1행 1열 상관서브쿼리
ex ) 사원명, 부서명, 관리자명 조회
select
emp_name,
(select dept_title from department where dept_id = e.dept_code) dept_title,
(select emp_name from employee where emp_id = e.manager_id) manager_name
from
employee e;
서브쿼리가 SQL 알고리즘에도 나와서 당황스럽고 그랬었는데 바로 배워서 이해가 쏙쏙됐다!
아무래도 알고리즘문제가 있다면 자주 사용할 것 같고,
그게 아니더라도 더 다양한 데이터 분석 , 나타내기를 하기 위해서는 서브쿼리 연습이 필수라고 생각된다.