SQL _ Subquery (where , select, from, substring_index,substring,with,case)

1. subquery는 쿼리 안의 쿼리라는 의미이다. 

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

-> kakaopay로 결제한 user_id, u.name, u.email을 보는 방법이다.

-> 먼저 따로 

select user_id from orders
where payment_method = 'kakaopay'

kakaopay로 결제하는 user_id를 orders 테이블에서 구한다. 이 내용을 표시하고 싶은 데이터가 있는 users쿼리 안에 집어넣는다.

->위의 내용은 inner join으로도 되지만 suquery의 장점은 직관적이라고 한다.

 

2. where에 들어가는 subquery

예시) where 필드명 in (subquery)이다.  (in은 필수요소는 아니다. 사용할 때도 있고 아닐 때도 있고)

select * from point_users pu 
where pu.point > (

                              select avg(pu2.point) from point_users pu2

                           )

->위의 예시는 point_users의 point가 point 전체 평균보다 '>'(높은) point_users테이블의 모든 데이터를 출력한다.

->여기서는 'in'이 사용되지 않았다. in을 사용하니 오히려 error가 발생한다.

 

3. select에 들어가는 subquery

예시) select 필드명, 필드명, (subquery) from.. 

select c.checkin_id, c.user_id, c.likes, 
   (select avg(likes) from checkins c2
   where c2.user_id = c.user_id) as avg_like_user
from checkins c;

->위의 예시는 user_id별로 likes의 평균을 구해 checkin_id와 user_id 그리고 likes 우측에 avg_like_user를 붙여서 출력한다.

-> where 부분에 보면 user_id가 같을 때마다 user_id의 likes 평균을 구하도록 되어 있다.

 

4. from에 들어가는 subquery (가장 많이 사용하는 유형) 

내가 만든 select와 이미 있는 테이블을 join 하고 싶을 때 사용한다.

< point_users에 있는 key user_id, point값과 함께 유저 별 좋아요 평균을 출력할 것이다.>

 

<1. 유저 별로 좋아요 평균을 구한다.>

select user_id, round(avg(likes),1) as avg_like from checkins
group by user_id

<2. point_users테이블에 있는 key값을 출력하고>

select pu.user_id,pu.point from point_users pu
<from subquery와 inner join을 이용해서 붙인다.>

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

-> point_users 테이블에 checkins를 inner join 했다.

->() 안에 join할 select 테이블을 넣어야 한다. ()는 필수이다.

->그리고 () 안의 내용을 별칭 'a'로 지정한다. point_users와 checkins의 공통된 key값 user_id로 join을 완성한 뒤.

->바깥의 point_users select에 a.avg_like를 추가하여 출력되도록 한다.

만약 a.avg_like를 추가해주지 않으면 출력이 되지 않는다. 

꼭 잊지 말고 추가하자.

 

5.with는 쿼리가 계속 길어질 때 보기 편하게 정리하는 방법이다.

with table1 as(
select * from point_users pu
), table2 as (
select user_id, round(avg(likes),1) as avg_like from checkins
group by user_id
)
select a.user_id, b.avg_like, a.point 
from table1 a inner join table2 b on a.user_id = b.user_id

->위의 예시는 2개의 select 테이블을 with에서 table1과 table2의 별칭으로 만들고 난 뒤 바깥의 select에서 table1과 table2를 inner join 하여 출력하고 자하는 내용을 실행한다.

->경우에 따라서는 with와 서브쿼리 사용하지 않고도 간단한 해결 방법이 있을 수 있지만 많은 연습이 필요하다.

->with가 필수는 아니지만 직관적이다.

 

6. SUBSTRING_INDEX로 문자열 쪼개기.

select user_id, email, SUBSTRING_INDEX(email, '@', -1) from users

-> users 테이블에 있는 key값 email을 @기준으로 나누고 -1 한 값을 출력한다.

-> -1은 쪼개진 조각 중 마지막 것을 가져온다.

-> 1은 첫 번째 조각을 가져온다.

 

7. SUBSTRING로 문장열 일부 출력하기.

예시) SUBSTRING(문자열, 출력을 하고 싶은 첫 글자의 위치, 몇 개의 글자를 출력하고 싶은지)

select order_no, created_at, substring(created_at,1,10) as date from orders

->첫 글자의 위치 변경 가능.

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

->단 첫 글자의 위치부터 범위는 하나씩 세어야 함.

 

8. Case 경우에 따라 원하는 값을 새 필드에 출력한다.

select pu.point_user_id, pu.point,
case 
when pu.point > 10000 then '잘 하고 있어요!'
else '조금 더 달려주세요!'
END as '구분'
from point_users pu;

-> point의 점수가 10000 이상이면 '잘 하고 있어요!'가 출력이 되고, 아닌 경우에는 '조금 더 달려주세요!'가 출력된다.

->if/else 와 비슷하다. case로 시작해서 첫 번째 조건을 적어주고 아닌 경우는 else로 적어준다. 

->end로 마무리해줘야 case의 끝을 알 수 있다. (하지 않을 시 error발생)