서브쿼리(Subquery)와 with절

Subquery☆
큰 쿼리 안에 들어가있는 쿼리문

SELECT u.user_id, u.name, u.email from users u 
inner join orders o on u.user_id = o.user_id 
where o.payment_method = 'kakaopay'

/*위와 아래는 동일함*/

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

1.  Where (중첩 서브쿼리)

 

형태 : Where 필드명 in (subquery)

 

가장 일반적인 서브쿼리로, A 테이블의 X 데이터와 B 테이블의 X 데이터를 조회

--ex) point_users와 users를 이용하여 이씨 성을 가진 유저의 평균 보인트보다 큰 유저들의 데이터 추출
SELECT * from point_users pu 
WHERE point > (
   SELECT AVG(point)  from point_users pu 
   inner join users u on pu.user_id = u.user_id
   where u.name = '이**'
)
--다른 풀이
SELECT * from point_users pu 
WHERE point > (
   SELECT * from point_users pu
   WHERE user_id in (
      select user_id from users u where u.name = '이**'
   )
)

2. Select

 

형태 : select 필드명, 필드명, (subquery) from

 

select절에 사용되는 서브쿼리로, 하나의 상수 또는 Join을 대체하기 위한 용도. 하나의 열처럼 사용.

/*ex) checkins에 course를 Join하고 checkin_id, user_id, title, likes, course_avg를 출력하세요. 
course_avg는 course_id별 좋아요의 평균값임*/

SELECT ch.checkin_id,
       ch.user_id,
       c.title,
       ch.likes,
       (
          select ROUND(AVG(likes),1) from checkins ch2
      WHERE ch2.course_id = ch.course_id
       ) as course_avg
 from checkins ch 
 inner join courses c on ch.course_id = c.course_id

3. From

 

형태 : select * from ( select * from ) 테이블명

하나의 테이블처럼 사용. 열 이름을 반드시 명시해야 함.

/*ex) checkins와 orders의 course_id별 중복 없이 유저의 체크인 개수를 구하고, 
course의 title과 course_id, cnt_checkins, cnt_total, ratio(비율)*/

SELECT c2.title,
       a.course_id, 
       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 c
   group by c.course_id
) a
inner join (
   SELECT course_id, count(DISTINCT(user_id)) as cnt_total from orders o
   group by o.course_id 
) b on a.course_id = b.course_id
inner join courses c2 on a.course_id = c2.course_id

 

 

With 절

제일 윗단에 작성. 임시 테이블 같은 역할.

with table1 as (
   SELECT course_id, COUNT(DISTINCT(user_id)) as cnt_checkins from checkins c
   group by c.course_id
), table2 as (
   SELECT course_id, count(DISTINCT(user_id)) as cnt_total from orders o
   group by o.course_id 
)

SELECT c2.title,
       a.course_id, 
       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 c2 on a.course_id = c2.course_id 

/*위의 from문과 같음. with문이 더 명시적이라는 장점*/

실전 SQL 문자열

1. substring_index (문자 쪼개기)

SELECT user_id, email, SUBSTRING_INDEX(email,'@',-1) from users u 
-- 1은 첫번째, -1은 마지막 것을 보여줌

2. substring (문자열 일부만 출력)

--ex) 날짜의 일부(시간 생략)만 출력해 주문 개수 세기
SELECT SUBSTRING(created_at, 1, 10) as date, COUNT(*) as total from orders o
group by date