join

• 데이터를 중복 저장하면 저장할 공간이 더 필요한 문제와, 중복 저장한 열 데이터를 수정할 경우 해당 열을 가진 테이블의 데이터를 모두 찾아 수정해야하는 문제 발생 등 조인을 활요하면 해결

데이터모델링

• 주어진 상황에서 논리 데이터 모델을 구성하는 작업
• 데이터모델링이 끝나면 논리데이터를 물리데이터로 바꾸어 데이터베이스에 반영

내부조인

select[열 이름]
from [테이블 1]
inner join [테이블 2] on [테이블 1.열] = [테이블 2.열]
where [검색 조건]

ON 과 WHERE차이

• 데이터를 필터링한다는 점에서는 역할이 비슷하지만 다르다
• ON은 조인할 때 조인 조건을 위해 사용
• WHERE은 조인을 완료한 상태에서 조건에 맞는 값을 가져오고자 사용

별칭을 사용하지 않으면?

• select를 할 때 공통인 열 이름을 사용하면 오류가 발생하므로, 별칭을 만들고 공통인 열을 지칭해야 한다.
  Ex)

select 
a.symbol #공통열
from company as a
inner join stock as b on a.symbol = b.symbol
where a.symbol = "aa"

• 조인 시 열의 이름은 달라도 되나 데이터형이 같아야한다.

외부조인

• 일치하는 항목이 아닌 행을 조합해 검색해야 할 경우 사용
• 한쪽 테이블을 다른 쪽 테이블에 조합할 때 사용

select [열이름]
from [테이블 1]
<left, right, full> outer join [테이블2] on [테이블 1.열] = [테이블 2.열]
where [검색조건]

Left outer join

select 
a.symbol as a_symbol
b.symbol as b_symbol
from company as a
left outer join industry_group as b on a.symbol = b.symbol

Right outer join where null

• b테이블에 속하는 것만 추출

select 
a.symbol as a_symbol
b.symbol as b_symbol
from company as a
right outer join industry_group as b on a.symbol = b.symbol
where a.symbol is null

Full outer join 각 테이블 데이터만 추출

• null를 사용하여 한다.

select 
a.symbol as a_symbol,
b.symbol as b_symbol
from company as a
full outer join industry_group as b on a.symbol = b.symbol
where 
a.symbol is null
or b.symbol is null

교차조인

• 자주 사용하지는 않지만, 모든 경우의 수를 조합한 데이터가 필요한 경우 교차조인 사용 가능

select [열이름]
from [테이블 1]
cross join [테이블 2]
where [검색조건]

예시

create table a(num int);
create table b(name nvarchar(10));
insert into a values (1), (2), (3);
insert into b values ('do'), ('it'), ('sql');

select 
a.num
b.name
from a
cross join b 
where a.num=1

서브쿼리

• 쿼리 안에 포함되는 또 다른 쿼리를 뜻함
• 조인하지 않은 상태에서 다른 테이블과 일치하는 행을 찾거나, 조인 결과를 다시 조인할 때 사용할 수 있다.

특징

• 반드시 소괄호 감싸 사용
• 주 쿼리를 실행하기 전에 1번만 실행
• 비교 연산자에 서브 쿼리를 사용하는 경우 서브 쿼리를 오른쪽에 기술해야 한다
• 내부에서 정렬 구문인 order by문을 사용할 수 없다.

where문 사용

• 서브 쿼리 중에서 where 사용하는 서브쿼리를 중첨 서브쿼리라고 한다.(조건문의 일부로 사용)
• 비교연산자와 함께 사용할 때 반드시 서브쿼리의 반환 결과가 1건 이하여야 한다.
• 2건 이상일 경우 비교연산자가 아닌 다중 행 연산자 사용해야한다.
  (in, any, exists, all)

단일행 서브쿼리

select [열 이름]
from [테이블]
where [열] = (select [열] from [테이블])

• 성공(단일행)

select * 
from company
where symbol = (select symbol from company where symbol in('msft'))

• 실패(다중행)

select * 
from company
where symbol = (select symbol from company where symbol in('msft', 'amd', 'amzn'))

다중행 서브쿼리

select [열이름]
from [테이블]
where [열] in (select [열] from[테이블])

not in

• not in문으로 industry그룹이 '자동차'가 아닌 행을 검색

select * from company
where symbol not in(
select symbol from industry_1 as a
inner join industry_2 as b on a.num = b.num
where a.industry = n'자동차'
)

any

• 하나라도 만족하는 조건

select * from company
where symbol = any(
select symbol from company_1 where symbol in('msft','df','dfdf')
)

select * from company
where symbol < any(
select symbol from company_1 where symbol in('msft','df','dfdf')
)

exists

• 1행이라도 있으면 true 없으면 false를 반환

select * from company
where exists (
	select symbil from company
    where symbol in('mst','sdf')
)

all

• 결과값 모두를 만족하는 결과가 주 쿼리의 결과값에 없으므로 출력 안함

from 문에 서브쿼리

• 인라인뷰라고 한다

select [열 이름]
from [테이블] as a
inner join (select [열] from [테이블] where [열] = 값) as b on [a.열] = [b.열]
where [열] = [값]

select 문에 서브쿼리

• 서브쿼리는 반드시 1개의 행을 반환해야하므로, sum, count, min, max등 집계 함수 사용하는 경우가 많다
  -> 하지만 성능 저하되므로 select문에 서브쿼리를 집게합수로 사용하지 않는 것이 좋다.
• 스칼라 서브쿼리라고도 하며 1개 이상을 사용 가능

select [열 이름]
 (select <집계 함수> [열 이름] from [테이블2]
 where [테이블 2. 열] = [테이블1.열]) as alias
from [테이블1]
where [조건]

공통테이블식

• 데이터베이스에 없는 테이블이 필요할 때 사용하며, 바로 다음에 실행할 select문에만 사용해야 한다는 특징이 있다.

일반CTE

• UNION, UNION ALL, INTERSECT, EXCEPT문 사용하여 여러개의 CTE쿼리 결합 가능
• 복잡한 쿼리를 단순하게 할 때 사용하기 좋다.

with [CTE_테이블 이름] (열 이름 1, 열 이름2, ...)
as
( 
  <select 문>
)
select [열 이름] from [CTE_테이블 이름];

• 성공
• cte관련 절에서는 2021.1.1이후 데이터 검색해 반환후 이후 select문에서 사용하는 방식

# 필드 결과와 cte정의 개수 같아야 한다.
with cte_stock_price(date, symbol, price) 
as
(
	select date, symbol, [close] from stock
    where date >= '2021-01-01'
)
select * from cte_stock_price where symbol ='mst'

• 실패
• cte의 select문에서 얻은 열 목록이 다르면 오류가 발생

# 필드 결과와 cte정의 개수 같아야 한다.
with cte_stock_price(date, symbol, price) 
as
(
	select date, symbol,[open], [close] from stock
    where date >= '2021-01-01'
)
select * from cte_stock_price where symbol ='mst'

UNION, UNION ALL

• 중복을 제거한 행 포함 여부가 차이점
• 중복을 제거한 결과를 보고 싶다면 UNION문(성능 문제 일으킨다)
• UNION ALL 사용전
  • 되도록이면 다른 쿼리에서 중복을 제거한 다음 union all문을 사용 할 것

# 필드 결과와 cte정의 개수 같아야 한다.
with cte_stock_price(date, symbol, price) 
as
(
	select date, symbol,[open], [close] from stock
    where date >= '2021-01-01'
    union all #두개의 결과 합친 것
    select date, symbol,[open], [close] from stock
    where date >= '2021-01-02'
    union all
)
select * from cte_stock_price where symbol ='mst'

intersect

• 내부조인과 비슷하지만 내부조인 경우 테이블 사이의 조인 조건에 맞는 데이터 반환
• intersect는 각 쿼리에서 반환한 결과에서 중복 결과를 걸러내 반환

except

• not in과 비슷하지만 not in은 중복을 제거하지 않고
• except는 중복을 제거하고 반환