subquery(1)(where절, select절)
2023-02-21(화) 16:41
주말에 끝내온다 했지만 사정이 있었네요
subquery 서브쿼리는 다양하게 사용할 수 있습니다
where절에 들어가서 하나의 조건으로 사용할 수 있고, select절에 들어가서 출력 데이터에 새로운 데이터를 추가할 수도 있고, from절에 들어가서 서브쿼리로 만든 테이블과 기존 테이블을 조인시켜 원하는 형태로 데이터를 출력할 수 있습니다!
자! 그럼 where절에 들어가는 서브쿼리부터 보겠습니다!
select * from 테이블명
where 필드명 in (서브쿼리) 가장 기본적인 형태입니다
어떻게 사용할까요?
select * from users u
where u.user_id in (select o.user_id from orders o
where o.payment_method = 'kakaopay')
users 테이블에서 유저의 정보를 출력하려고 합니다
조건절을 보면 users 테이블의 user 컬럼을 이용하네요
서브쿼리를 보면
select o.user_id from orders o ->order 테이블에서 user_id를 가져올 것입니다
where o.payment_method = 'kakaopay' ->order 테이블의 payment_method(결제방식)이 kakaopay여야 하는 조건이 붙어있습니다
쿼리가 돌아가는 것을 보면 좀 더 이해하기 쉽습니다
users 테이블의 정보를 불러오고 서브쿼리를 돕니다 order 테이블에서 payment_method가 kakaopay인 user_id를 가져옵니다
서브쿼리에서 뽑은 user_id를 통해 해당하는 user_id를 가진 유저의 정보를 출력합니다
다른 테이블끼리 하나의 컬럼을 공유하여 정보를 가져오는 것은 join의 역할과 비슷하면서도 서브쿼리를 사용한다면 오히려 더 빠르고 효율적이게 정보를 출력할 수 있습니다
문제를 풀어보겠습니다
point_users 테이블을 이용하여 point가 평균보다 많은 유저의 정보를 출력해보겠습니다
select * from point_users pu
where pu.point > (select avg(pu2.point) from point_users pu2)
먼저 select * from point_users pu 를 이용하여 테이블을 파악합니다
point_users테이블의 형태입니다
point의 평균을 먼저 구해줍니다
select avg(pu2.point) from point_users pu2
평균은 5,380이네요
이제 서브쿼리를 이용해봅시다
select * from point_users 를 하나 더 적어줍니다
요런 느낌으로!
where을 이용하여 서브쿼리를 이용한 조건문을 만들어봅시다
select * from point_users pu
where pu.point > (select avg(pu2.point) from point_users pu2)
좋습니다 출력결과를 보겠습니다
평균인 5,380보다 큰 포인트 값을 가진 유저의 정보가 출력됩니다 !
조금 더 심화과정입니다
성이 이 씨인 유저의 평균 포인트보다 큰 유저를 뽑아보겠습니다
select * from point_users pu
where pu.point >
(select avg(pu2.point) from point_users pu2
inner join users u
on pu2.user_id = u.user_id
where u.name like '%이%'
저는 이럴 때에는 조건을 먼저 풀어보는 것을 선호합니다
이 씨 성을 가진 유저의 평균 포인트를 먼저 구하겠습니다
select * from point_users pu2
평균 포인트를 구해야 하니 point_users 테이블을 먼저 확인합니다
위의 표를 보면 point_users 테이블에는 유저의 이름이 없습니다
유저 이름이 있는 users 테이블과 join해줍니다
select * from point_users pu2
inner join users u
on pu2.user_id = u.user_id
join이 되었다면 where을 이용하여 성이 이 씨인 유저를 먼저 찾아줍니다
where u.name like '%이%'
db에 기록된 형태의 이름이라면
where u.name = '이**' 으로도 찾을 수 있습니다
이제 조건에 따라 성이 이 씨인 유저들만 출력되니 평균을 구합니다
select avg(pu2.point) from point_users pu2
inner join users u
on pu2.user_id = u.user_id
where u.name like '%이%'
7,453이 평균값으로 나옵니다
이제 이 값을 이용하여 7,453보다 큰 포인트를 가진 유저를 출력해봅시다
select * from point_users pu
where pu.point > (
select avg(pu2.point) from point_users pu2
inner join users u
on pu2.user_id = u.user_id
where u.name like '%이%')
이 씨 성을 가진 유저들의 평균 포인트인 7,453보다 큰 포인트를 가진 유저들이 출력됩니다!
다음은 select절에서 사용되는 서브쿼리를 알아보겠습니다
보통 select절은 무엇을 출력할 것인지를 정하는 영역입니다
위의 point_users 테이블에서 select user_id, point from point_users
을 사용한다면 point_user_id와 created_at, updated_at 필드는 출력되지 않고 선택했던 필드만 출력될 것입니다
이것만 기억하면 select절에서 사용하는 서브쿼리는 끝입니다
기본형태부터 보겠습니다
select 필드명, 필드명, 필드명, (서브쿼리) from 테이블
이렇게 보면 이해가 어려우니 문제로 바로 넘어가겠습니다
checkins테이블에서 checkin_id, user_id, likes를 가져오기+ 유저가 적은 comment가 받은 likes(좋아요)의 평균을 구하고 함께 출력하여 비교할 수 있게 데이터를 뽑아보겠습니다
먼저 checkins 테이블의 모습을 봐야합니다
가져와야 하는 것 먼저 봅시다
select c.checkin_id, c.user_id, c.likes,(여기에 서브쿼리가 들어갑니다) from checkins c
이제 서브쿼리를 작성해 줍시다
평균을 구해야하니 이렇게 하면 되겠죠
select avg(c2.likes) from checkins c2
아닙니다 이렇게 되면 테이블 전체의 likes의 평균이 구해집니다
그렇다면 그룹으로 지정해서 하면 어떨까요?
select c2.checkin_id, c2.user_id, avg(c2.likes) from checkins c2
group by c2.checkin_id
괜찮은거 같습니다 조금만 바꿔서 select절에 넣어보겠습니다
select c.checkin_id, c.user_id, c,likes,
(select avg(c2.likes) from checkins c2
group by checkin_id) as avg_likes
from checkins c
오류가 발생합니다 왜 그럴까요?
1:1 매칭이 안되기 때문입니다
그렇다면 어떻게 해야할까요
서브쿼리를 바꿔보겠습니다
select avg(c2.likes) from checkins c2
where c2.user_id = c.user.id => user_id로 1:1로 매칭시켜줍니다
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
문제하나 풀어보겠습니다
checkins 테이블을 이용하여 checkin_id, course_id, user_id, likes를 출력하고 더하여 course_id별 평균을 구하여 출력해봅시다
select c.checkin_id, c.course_id, c.user_id, c.likes,
(select avg(c2.likes) from checkins c2
where c.course_id = c2.course_id)
from checkins c
먼저 뽑을것을 적어봅시다
select c.checkin_id, c.course_id, c.user_id, c.likes,(서브쿼리) from checkins c
서브쿼리를 작성해 봅시다
select * from checkins c2에서 시작해야겠죠?
1:1매칭을 시켜줍시다 대상은 course_id 입니다
where c.course_id = c2.course_id
서브쿼리를 넣어줍니다
select c.checkin_id, c.course_id, c.user_id, c.likes,
(select avg(c2.likes) from checkins c2
where c.course_id = c2.course_id)
from checkins c
보기좋게 alias를 사용하여 별칭을 줍니다
select c.checkin_id, c.course_id, c.user_id, c.likes,
(select avg(c2.likes) from checkins c2
where c.course_id = c2.course_id) as course_avg
from checkins c
잘 출력되었습니다!
조금 더 알아보겠습니다
위의 결과물에 courses테이블을 course_id를 통해 join시키고 course_id 대신 title를 출력시켜보겠습니다
select c.checkin_id, c.course_id, c.user_id, c.likes,
(select avg(c2.likes) from checkins c2
where c.course_id = c2.course_id) as course_avg
from checkins c
여기까지는 위에 만든 것을 사용합니다 join시켜줍니다
select c.checkin_id, c.course_id, c.user_id, c.likes,
(select avg(c2.likes) from checkins c2
where c.course_id = c2.course_id) as course_avg
from checkins c
inner join courses co
on c.course_id = co.course_id
이제 course_id를 빼고 title을 넣어보겠습니다
select c.checkin_id, co.title, c.user_id, c.likes,
(select avg(c2.likes) from checkins c2
where c.course_id = c2.course_id) as course_avg
from checkins c
inner join courses co
on c.course_id = co.course_id
추가로 round를 사용하여 course_avg의 소수점 첫 번째 자리까지 표시해줍니다
select c.checkin_id, co.title, c.user_id, c.likes,
round((select avg(c2.likes) from checkins c2
where c.course_id = c2.course_id),1) as course_avg
from checkins c
inner join courses co
on c.course_id = co.course_id
좋습니다 잘 출력되었습니다
다음으로 from절에 사용되는 서브쿼리를 알아보는건 내일해야겠습니다
글이 길어지면 나중에 찾아보기 힘들어지니 조금만 더 다듬어서 와야겠네요