[SQL] 서브쿼리(Subquery), with절, case문 등 유용한 문법 활용하기

Database

[SQL] 서브쿼리(Subquery), with절, case문 등 유용한 문법 활용하기

EEEUN 2023. 8. 13. 21:35

이 글은 제가 개발을 하며 알게 된 내용, 느낀 점 등을 기록하고자 쓰는 글입니다. '스파르타코딩클럽'의 '엑셀보다 쉬운 SQL' 강의를 듣고 배우고 있습니다. 내용에 대한 반박과 피드백은 늘 감사히 받겠습니다.

서브쿼리(Subquery)란?

서브쿼리(Subquery)는 쿼리 안의 쿼리이다.

하위 쿼리의 결과를 상위 쿼리에서 사용하면, SQL 쿼리가 훨씬 간단해진다.

즉, Subquery를 사용하지 않아도 원하는 데이터를 얻어낼 수 있겠지만, 더 편하고 간단하게 원하는 데이터를 얻기 위해 사용되는 파워풀한 기능이다.
Subquery는 With 구문과 함께 사용하면 훨씬 더 효율적으로 사용할 수 있다.
Subquery는 where, from, select절에서 유용하게 사용된다.

Where절에 사용되는 Subquery

Where은 조건문이다. 그래서 Subquery의 결과를 조건에 활용하는 방식으로 사용한다.

구조 : where 필드명 in (subquery)

select * from users u
where u.user_id in (select o.user_id from orders o 
                    where o.payment_method = 'kakaopay');

(where절에 사용되는 Subquery 코드 예시)

Select절에 사용되는 Subquery

Select는 결과를 출력해주는 부분이다. 그래서 기존 테이블에 함께 보고싶은 통계 데이터를 손쉽게 붙이는 것에 사용한다.

구조 : 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;
Colored by Color Scripter

(Select절에 사용되는 Subquery 코드 예시)

From절에 사용되는 Subquery

From은 내가 만든 Select와 이미 있는 테이블을 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
Colored by Color Scripter

(From절에 사용되는 Subquery 코드 예시)

with절 연습하기

with 절로 더 깔끔하게 쿼리문을 정리하기

select c.title,
       a.cnt_checkins,
       b.cnt_total,
       (a.cnt_checkins/b.cnt_total) as ratio
from
(
    select course_id, count(distinct(user_id)) as cnt_checkins from checkins
    group by course_id
) a
inner join
(
    select course_id, count(*) as cnt_total from orders
    group by course_id 
) b on a.course_id = b.course_id
inner join courses c on a.course_id = c.course_id

△ with절을 쓰기 이전 코드. Subquery 내용이 많아 한 눈에 코드 구조를 알기 어렵다.

with table1 as (
    select course_id, count(distinct(user_id)) as cnt_checkins from checkins
    group by course_id
), table2 as (
    select course_id, count(*) as cnt_total from orders
    group by course_id
)

select c.title,
       a.cnt_checkins,
       b.cnt_total,
       (a.cnt_checkins/b.cnt_total) as ratio
from table1 a inner join table2 b on a.course_id = b.course_id
inner join courses c on a.course_id = c.course_id

△ with절을 쓴 코드. Subquery 코드를 위에 따로 선언해주었다.

(C나 JAVA 코드 짤 때 함수를 따로 선언하고 코드에서는 함수명만 쓰는 그런 느낌인 것 같다.)

문자열 쪼갤 때 유용한 명령어

SUBSTRING_INDEX
SUBSTRING

CASE : 경우에 따라 원하는 값을 새 필드에 출력해보기

특정 조건에 따라, 데이터를 구분해서 정리해주고 싶을 때 CASE 라는 문법이 사용된다.

select pu.point_user_id, pu.point,
case 
when pu.point > 10000 then '1만 이상'
when pu.point > 5000 then '5천 이상'
else '5천 미만'
END as lv
from point_users pu

△ 예시코드 : 포인트 점수가 10000 이상이면 '1만 이상', 5000 이상이면 '5천 이상', 그 아래이면 '5천 미만'이라고 출력한다.

select level, count(*) as cnt from (
    select pu.point_user_id, pu.point,
    case 
    when pu.point > 10000 then '1만 이상'
    when pu.point > 5000 then '5천 이상'
    else '5천 미만'
    END as lv
    from point_users pu
) a
group by lv

ㄴ 결과화면

△ 위 코드에서 Subquery를 이용해 점수별 인원수를 카운트하였다.

👉 참고한 내용

스파르타코딩클럽 '엑셀보다 쉬운 SQL' 4주차 강의 & 강의자료

저작자표시