[SQL] subquery(where, select, from), alias.*, distinct, with 구문, substring, case문

https://spartacodingclub.kr/online/sql

스파르타코딩클럽 [엑셀보다 쉬운 SQL]

4주차 수업 필기

​

0. SUBQUERY 문

큰 쿼리문 안에 들어가는 쿼리문 x3: where 절, select 절, from 절

쿼리문을 더 복잡하게 쓸 수 있도록 해주는 테크닉.

잘 활용시 엑셀 등으로 하기 어려운 복잡한 처리 가능

 

 

1. WHERE subquery 문

 

2에서 where subquery문을 사용

where 필드명 in subquery

<orders 테이블에서 kakaopay로 결제한 유저의 정보를 users 테이블에서 출력하려는 상황>

1) users 테이블과 orders 테이블을 inner join한 후 payment_method가 kakaopay인 유저의 정보를 select함

 

2) where subquery를 사용하여 orders 테이블에서 payment_method가 kakaopay인 user_id와 users 테이블의 user_id가 같은 경우 그 유저의 정보를 select하도록 함

 

 

+하나의 쿼리문에는 where이 한 번만 들어갈 수 있음.

where을 여러 개 사용하고 싶다면 and 또는 or을 이용해서 조건을 연결해주면 됨.

 

 

2. SELECT subquery 문

select 필드명1, 필드명2, subquery from ~

 

<checkin_id와 user_id 필드 옆에 avg_likes_user 라는 필드를 하나 더 추가하려는 상황>

+avg_likes_user는 한 user_id의 평균 like 수를 의미

checkin_id, user_id가 하나씩 select 되고 이어서 subquery의 user_id가 메인 쿼리문의 user_id와 같을 때 그 user_id의 평균 like 수를 출력하도록 함

 

+ subquery에서 select avg(likes) from checkins 는 메인 쿼리문의 checkins의 알리아스와 동일하게 하면 안됨

-> 'subquery와 메인 쿼리문의 user_id가 같을 때' 라는 의미이므로 같은 알리아스 사용하면 의미가 없음

 

 

3. FROM subquery 문

select 필드명 from subquery inner join subquery

 

<orders 테이블에서 course_id와 cnt_total을, checkins 테이블에서 course_id와 cnt_checkins를 select한 후 course_id를 key로 하여 하나의 테이블로 만드려는 상황>

 

-> 각각의 select 문을 만든 후 이를 원래 있던 테이블처럼 사용

+ subquery(각각의 select 문) 에는 꼭 key로 하려는 필드가 있어야 함 (ex. course_id)

+ 다짜고짜 subquery inner join subquery 는 안되고, 메인 쿼리문 내에 오도록 해야 함

 

 

4. alias.*

join한 상황에서 select를 통해 몇 개의 필드만 볼 수 있는 상황일 때 내가 붙인 테이블이 제대로 붙여졌는지 확인하는 방법

내가 붙인 테이블의 알리아스.* 을 select 에 넣으면 내가 붙인 테이블의 전체 필드도 확인 가능

 

5. distinct

중복된 것 제외하고 세는 방법

 

count(distinct(필드명))

 

 

6. with 구문

from subquery 문에서 했듯 내가 직접 만든 select문을 메인 쿼리문에 직접 넣으면 쿼리문이 너무 복잡해 보임

 

이때 with 구문을 사용하면

 

with table 1 as ( ), table 2 as ( )

를 사용하여 table1, table2.. 를 테이블에 대한 일종의 alias처럼 사용 가능

 

구구절절 쓰던 긴 subquery 문을 간단하게 table1, table2로 작성 가능

+with 구문과 select문 사이에 빈 줄 있으면 안됨. 붙여서 써주기

 

 

7. substring

문자열 데이터를 우리가 원하는 형태로 정리하는 방법

1) 문자열 쪼개보기(substring_index)

substring_index(필드명, '기준', 1/-1)

-> 어떤 필드를 '기준'을 기준으로 하여 앞부분(1)/뒷부분(-1) 출력하기

 

ex) substring_index(email, '@', 1)

-> email 필드에서 @를 기준으로 하여 앞부분만 출력

 

+ 문자열이므로 기준이 되는 것도 문자이기에 꼭 '' 따옴표 붙여주기

 

 

2) 문자열 일부만 출력하기

substring(필드명, a, b)

-> 어떤 필드에서 a 번째 부터 b번째까지 출력

 

ex) substring(created_at, 1, 10)

-> created_at 필드에서 1번째부터 10번째까지 출력

 

 

8. case문

경우에 따라 원하는 값 새로운 필드에 출력

case when 조건1 then 출력1

when 조건2 then 출력2

else 출력3 end

 

조건을 만족할 때마다 출력할 값을 지정해주고 마지막으로는 else 와 end로 마무리해주면 됨