Join, SubQuery

김고양이·2023년 10월 16일

JOIN subquery

BackEnd

목록 보기

6/8

R+DBMS DataBaseManagerment System

JOIN

둘 이상의 테이블에서 데이터가 필요한 경우 테이블 조인이 필요함.
일반적으로 조인 조건을 포함하는 WHERE절을 작성해야함.(조인조건, 일반조건)
조인조건은 일반적으로 각 테이블의 PK, FK로 구성됨.

종류

NATURAL JOIN*

기준이 되는 컬럼을 자동으로 지정하여 join
일반적으로 사용하지 않음(기준 모호)
INNER JOIN

기준 컬럼의 데이터를 좌,우 테이블 둘 다 갖고 있는 경우만 조회

select *
from table_l l [inner] join table_r r
#on l.common = r.common;
using(common);

LEFT JOIN
좌측 테이블을 기준으로 모두 조회, 우측에는 값이 없는 경우 null로 조회

select *
from table_l l left [outer] join table_r r
#on l.common = r.common;
using(common);

RIGHT JOIN
우측 테이블을 기준으로 모두 조회, 좌측에는 값이 없는 경우 null로 조회

select *
from table_l l right [outer] join table_r r
#on l.common = r.common;
using(common);

FULL OUTER JOIN
어느 한쪽 테이블이라도 데이터를 갖고 있으면 조회
mysql에서는 left, right jpon의 결과를 union해서 얻을 수 있음

select l.common, l.col_l, r.col_r
from table_l l left join table_r r
using(common)
union
select r.common, l.col_l, r.col_r
from table_l l right join table_r r
using(common)
order by common;

INNER JOIN 방법

일반조인

mysql 한정!
where 조건절에 join에 대한 조건과 테이블에 대한 필터링 조건이 함께 쓰임 => 가독성 낮음

select *
from table_l l, table_r r
where l. common = r.common;

ANSI 조인

다른 sql에서도 범용적으로 사용 가능
where 조건절, join조건이 분리 => 가독성이 상대적으로 높음

select *
from table_l l [inner] join table_r r
#on l.common = r.common;
using(common);

조인 시 주의

조인 처리는 어느 테이블을 먼저 읽을지 결정하는 것이 중요
INNER JOIN: 어느 테이블을 먼저 읽어도 결과가 달라지지 않아 MYSQL 옵티마이저가 조인의 순서를 조절해서 다양한 방법으로 최적화 수행
OUTER JOIN : 반드시 OUTER가 되는 테이블을 먼저 읽어야 함. 옵티마이저가 순서를 조정 XX

서브 쿼리 (SubQuery)

다른 쿼리의 내부에 포함된 select 쿼리

종류

인라인 뷰

from 절에 사용하는 서브쿼리
서브쿼리의 조회 결과를 테이블처럼 사용할 때
alias 지정 필수

select *
from (
        select l.common, l.col_l, r.col_r
        from table_l l left join table_r r
        using(common)
        union
        select r.common, l.col_l, r.col_r
        from table_l l right join table_r r
        using(common)
        order by common
    ) as A
where common%2 =0;

스칼라 서브쿼리
- select 절에 사용하는 서브쿼리
- 메인쿼리의 조회 결과에 컬럼 데이터를 추가하고 싶은 경우 사용
  - 서브쿼리의 조회 결과는 무조건 1개 행이어야 함!
```
use world;

select continent, name, gnp, 
(select avg(gnp) from country) '전세계 평균',
(select avg(gnp) from country  where continent =c.continent) '전세계 평균'
from country c;
```

중첩 서브쿼리

where조건절에 사용하는 서브쿼리
서브쿼리의 조회 결과를 메인쿼리의 조건절로 사용

select department_id, department_name
from departments
#단일행 중첩서브쿼리
where location_id = (
    select location_id
    from locations
    where city = 'seattle'
);


- 단일행 중첩 서브쿼리
서브쿼리 조회 결과가 1개 row
where [컬럼명] [비교연산자] (서브쿼리)

# 근무 도시가 seattle인 사원의 사번, 이름

1 도시명이 seattle인 location_id 구하기

select location_id
from locations
where city = 'seattle';

# 2 부서번호(department_id) 구하기
  select department_id
 from departments
 where location_id = 1700;

 select department_id
 from departments
 where location_id = (
    select location_id
    from locations
    where city = 'seattle'
 );

 # 3 해당 부서 사원의 사번, 이름
select employee_id, first_name
from employees
where department_id in
(
     select department_id
     from departments
     where location_id = 
     (
        select location_id
        from locations
        where city = 'seattle'
     )
);


<br>

    - 다중행 중첩 서브쿼리
      - 서브쿼리 조회 결과가 n개 row
    where [컬럼명] [비교연산자] (서브쿼리)
    *비교연산자는 in, < any, > all 와 같이 사용
      - any : 서브쿼리로 조회된 결과보다 하나라도 ~값이 있다면 true
      - all : 서브쿼리로 조회된 결과보다 모두 ~ 경우 true
     ```
     select employee_id, first_name, salary, department_id
     from employees
     # any : 비교하는 값 중 하나라도 만족하면(최소 급여자) true
     # all : 최대 급여자보다 큰 걸 만족하면 true
     where salary > any (
     #where salary < all (
     #where salary in ( 
        select salary
        from employees
        where department_id = 30
     );

다중열 중첩 서브쿼리

여러개 컬럼을 동시에 비교하려는 경우 사용
where (col1,col2) [비교연산자] (select col1, co2...)

    # 커미션을 받는 사원중 매니저 사번이 148인 사원의 급여와 부서번호가 일치하는 사원의 사번, 이름
    #1 커미션을 받고(commission_pct is not null) 매니저 사번(manager_id)이 148인 사원의 급여와 부서번호 구하기
    select salary, department_id
    from employees 
    where commission_pct is not null 
    and manager_id = 148;

    #2 해당급여와 부서번호가 일치하는 사원의  사원 사번, 이름 
   select employee_id, first_name
   from employees
   where (salary, department_id) in (
        select salary, department_id
        from employees 
        where commission_pct is not null 
        and manager_id = 148
   );

조회 이외의 서브쿼리 활용

테이블 만들 때(create)

create table [테이블명]
[as]
	select 쿼리

조회쿼리의 결과를 다른 테이블의 데이터로 추가(insert)
```
insert into [테이블명] 
[ (col1, col2) ] 
select 쿼리
```

데이터 삭제/수정 시, 조건식으로 활용

update [테이블명] set col1=val1 where[컬럼][비교연산자](서브쿼리);
delete from [테이블명] where [컬럼] [비교연산자] (서브쿼리);

데이터 수정 시 수정할 값을 서브쿼리로 주ㅡㄴ 경우

   update[테이블명] set col1 = (
 	select* 
   from(
   	select 컬럼 from[태이블명]
   	) as a
  	)
   where[조건절]

김고양이

이전 포스트

DB_Index

다음 포스트

Join, SubQuery

BackEnd

JOIN

종류

NATURAL JOIN*

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL OUTER JOIN