내일배움캠프 26일차

<오늘의 학습>

오늘은 SQL 두번째 시간이다.
Join, Union, Subquery에 대해서 정리해보겠다.

Join

두 테이블의 공통된 정보 (key값)를 기준으로 테이블을 연결해서 한 테이블처럼 보는 것을 의미.

select * from 테이블1
left join 테이블2 on 테이블1.필드명 = 테이블2.필드명

select * from 테이블1
inner join 테이블2 on 테이블1.필드명 = 테이블2.필드명

여기서 필드명은 테이블1,테이블2 가 공통적으로 가지고 있는 필드여야 한다!

---

• inner join
  교집합이라고 생각하면 된다. 비어있는 필드는 나타내지 않고 데이터가 들어있는 녀석들만 합친다.

• left join
  left 조인은 테이블 A, 테이블 B가 있다고 했을때
  A에 B를 붙인다! 그리고 없는 데이터는 NULL로 나타낸다. 라고 생각 하면 된다.

where 필드명 is NULL

where 필드명 is not NULL

NULL로 데이터가 표시될 때 NULL을 활용해서 자료를 조회 할 수도 있다.

join을 사용하다 보면 join을 2번, group/order by를 2번씩 쓸 때가 있는데
join은 그냥 join을 두 번 사용하면 되고, group/order by는 ,를 사용해서 2개를 적으면 된다!

select c.title,ck.week,count(*)as cnt from checkins ck
inner join courses c on ck.course_id = c.course_id
inner join orders o  on ck.user_id = o.user_id
where o.created_at >='2020-08-01'
group by c.title,ck.week

---

Union

두 개의 쿼리를 한 번에 보고 싶다!

(쿼리1)
union all
(쿼리2)

쿼리를 괄호로 묶어주고 union all 을 해주면 된다.

여기서 재밌는 점은 괄호 안 쿼리의 order by는 적용되지 않는다!! 쿼리 별로 order by를 하면 안되고

union all 을 하고 나서 order by 를 해야한다!

---

Subquery

쿼리 안의 쿼리라는 의미를 가지고 얼마든지 사용 할 수 있다.

• where 에 사용하는 Subquery

where 필드명 in(서브쿼리)

select user_id ,name ,email  from users u 
where user_id in(
	select user_id from orders o 
	where payment_method = 'kakaopay'
)

---

• select 에 사용하는 Subquery

select 필드명,필드명,(서브쿼리) from ~

select c.checkin_id ,
	   c.user_id ,
	   c.likes,
	   (
	   select avg(likes) from checkins
	   where user_id = c.user_id
	   )as avg_likes
  from checkins c

---

• from 에 사용하는 Subquery(가장 많이 사용되는 유형)

  작성한 쿼리를 원래 있던 테이블처럼 만들어서 사용하는 방법!

select pu.user_id , pu.`point`, a.avg_likes from point_users pu
inner join(
	select user_id , round(avg(likes),1)as avg_likes from checkins c 
	group by user_id
) a on pu.user_id = a.user_id

group by user_id 를 사용한 쿼리의 결과를 원래 있던 테이블 처럼 만든다는 소리.

()a는 쿼리의 결과(테이블)의 이름을 a라고 붙여주겠다~ 라는 뜻.

---

• with 사용하기

select pu.user_id , pu.`point`, a.avg_likes from point_users pu
inner join(
	select user_id , round(avg(likes),1)as avg_likes from checkins c 
	group by user_id
) a on pu.user_id = a.user_id

위 from 절에서 서브쿼리를 사용했을때

with table1 as (
	select user_id , round(avg(likes),1)as avg_likes from checkins c 
	group by user_id
)
select pu.user_id , pu.`point`, a.avg_likes from point_users pu
inner join table1 a on pu.user_id = a.user_id

이렇게 with로 따로 처리해주면 select ~ from ~ 부분이 아주 깔끔하게 작성된다.

with로 테이블을 여러개 만들때는 ,로 구분해준다!

---

그 외 사용하는 문법

• SUBSTRING_INDEX()
  문자열을 나눌 때 사용. 파이썬의 split 과 비슷하면서 다르다.

SUBSTRING_INDEX(필드명,나눌 글자,나눈 후 n번째 글자)

select user_id, email, SUBSTRING_INDEX(email,'@',1) as id  from users u

users 테이블에서 email을 ‘@’으로 나눈 다음 1번째 글자를 나타내줘~
만약 email이 tmdgh717@naver.com 이라면 @으로 나누고 → tmdgh717, naver.com
1번째 → tmdgh717 / -1번째 → naver.com

파이썬에서는 [tmdgh717, naver.com] 이런 식으로 나눠져서 0번째가 tmdgh717 이었는데
SQL에서는 1번째라고 한다…

---

• SUBSTRING()

SUBSTRING(필드명,시작,시작부터 몇글자)

select order_no, SUBSTRING(created_at,6,5) as date  from orders o

orders 테이블에 created_at 필드를 나눠보자~
created_at 필드에 만약 2022-05-24가 있었다고 한다면

SUBSTRING(created_at,1,4) 라고 한다면 2022 가 출력
SUBSTRING(created_at,6,5) 라고 한다면 05-24가 출력된다.

---

• case
  case 문은 다른 코딩 언어에서의 if와 비슷하다.

case when 필드명 then 내용
		 when 필드명 then 내용
		 else 내용 end

이런 식으로 작성이 가능하고, 이것을 서브쿼리, with를 사용해서 다양한 사용을 할 수 있다.

with table1 as(
	select pu.user_id,pu.point,
	   (case when pu.point > 10000 then '1만이상'
	   		 when pu.`point` > 5000 then '5천이상'
	   		 else '5천 미만' END) as comment
		from point_users pu
)
select a.comment,count(*) from table1 a
group by a.comment

이렇게 작성하면 5천 미만, 5천 이상, 1만 이상의 갯수를 구할 수 있어서 통계를 내기에 좋다.

---

--오늘의 한 줄--

간단하고 어려운 SQL문을 모두 공부했다. 이제 남은 시간 Java Spring 돌입한다...
자~ 드가자~~~