SQL

Where 절의 개념

👉 Where 절은, Select 쿼리문으로 가져올 데이터에 조건을 걸어주는 것을 의미

​

Select 쿼리문에 Where 절 함께 써보기

​

select * from orders

where payment_method = "kakaopay";

• 아주 직관적이죠? "orders 테이블에서 payment_method가 kakaopay인 것만 가져와줘!" 라는 의미랍니다.

Q) 잠깐! 왜 kakaopay가 아니라 "kakaopay"라고 쓰나요? A) kakaopay를 필드명이나 테이블명이 아닌 문자열로 인식시키려는거에요.

만약 "kakaopay"가 아니라 kakaopay라고 쓰면? 당연히 에러가 납니다!

[Unknown column 'kakaopay' in 'where clause'] 에러문구를 천천히 읽어볼까요? Where 절에 있는 kakaopay라는 컬럼은 없다! 는 뜻이에요. 여기서 컬럼은 필드를 의미해요. 정말, kakaopay라는 글자를 컬럼(필드명)으로 인식해버렸죠?

​

Select 쿼리문에 Where 절 함께 써보기 연습

(1) 원하는 테이블과 (2) 조건, 이 두 가지만 기억하면 됩니다!

SQL을 잘 하기 위해서는, 원하는 정보가 어느 테이블에 담겨있는지 잘 찾는 것이 매우 중요하답니다!

[퀴즈] 포인트가 20000점보다 많은 유저만 뽑아보기!

select * from point_users where point > 20000;

​

[꿀팁🍯] 이렇게 쿼리를 작성하면 편해요!

1) show tables로 어떤 테이블이 있는지 살펴보기 2) 제일 원하는 정보가 있을 것 같은 테이블에 select from 테이블명 쿼리 날려보기 3) 원하는 정보가 없으면 다른 테이블에도 2)를 해보기 4) 테이블을 찾았다! 조건을 걸 필드를 찾기 5) select from 테이블명 where 조건 이렇게 쿼리 완성!

​

Where 절과 자주 같이쓰는 문법 배우기

'같지 않음' 조건 걸어보기

'같지 않음' 조건은 != 로 걸 수 있습니다.

[코드스니펫] '같지 않음' 조건 걸어보기

select * from orders

where course_title != "웹개발 종합반";

잠깐 상식! '!=' 에서 ! (느낌표)는 부정 (not)을 의미합니다. '='는 같음을 의미하니, '!='는 같지 않음이겠죠!

​

*'범위' 조건 걸어보기

7월 13일, 7월 14일의 주문데이터만 보고 싶어졌어요. 어떻게 해야 할까요?

'범위' 조건은 between 으로 걸 수 있어요.

select * from orders

where created_at between "2020-07-13" and "2020-07-15";

*'포함' 조건 걸어보기

👉 1, 3주차 사람들의 '오늘의 다짐' 데이터만 보고 싶어졌어요.

'포함' 조건은 in 으로 걸 수 있어요.

select * from checkins

where week in (1, 3);

👉 다음 (daum) 이메일을 사용하는 유저만 보고 싶어졌어요. 어떻게 하죠?

'패턴' 조건은 like 으로 걸 수 있어요.

*'패턴' (문자열 규칙) 조건 걸어보기

select * from users

where email like '%daum.net';

[꿀팁🍯] Like의 다양한 사용법

where email like 'a%': email 필드값이 a로 시작하는 모든 데이터

where email like '%a' email 필드값이 a로 끝나는 모든 데이터

where email like '%co%' email 필드값에 co를 포함하는 모든 데이터

where email like 'a%o' email 필드값이 a로 시작하고 o로 끝나는 모든 데이터

​

*일부 데이터만 가져오기: Limit

👉 테이블에 어떤 데이터가 들어있나 잠깐 보려고 들어왔는데, 데이터를 다 불러오느라 시간이 오래 걸리면 힘들겠죠?

그런 경우를 대비해, 일부 데이터만 가져오는 Limit이라는 기능이 있습니다.

select * from orders

where payment_method = "kakaopay"

limit 5;

딱 5개만 출력된 것을 알 수 있죠?

*중복 데이터는 제외하고 가져오기: Distinct

select distinct(payment_method) from orders;

요렇게, 네 개의 결제수단이 있다는 사실을 한 줄로 알아낼 수 있답니다!

*몇 개인지 숫자 세보기: Count

이번에는 orders 테이블에 데이터가 몇 개 들어있는지 궁금해요! (결제가 몇 건이나 들어왔죠? 두근두근) 1) orders 테이블의 데이터를 모두 불러와서, 한줄한줄씩 센다 2) count 기능을 사용해서 한방에 본다

당연히 2번이죠?

select count(*) from orders

한 줄로 끝! select from orders에서 을, count로 감싸주면 됩니다.

*[응용] Distinct와 Count를 같이 써보기

👉 스파르타 회원 분들의 성(family name)씨가 몇개인지 궁금하다면? 이번에도 마찬가지로 쉬운 방법이 있습니다!

select distinct(name) from users;

이렇게 하면 성씨가 쭉 나오죠?

SELECT count(distinct(name)) from users;

그리고, 여기에 count를 입혀주면 몇 개의 성씨가 있는지 알 수 있습니다. (참 쉽죠?)

​[코드스니펫] 성씨별 회원수를 Group by로 쉽게 구해보기

select name, count(*) from users

group by name;

1. from users: users 테이블에서 데이터를 불러옵니다

2. group by name: name이라는 필드에서 동일한 값을 갖는 데이터를 하나로 합쳐줍니다

3. select name, count(): 이름과 count()를 출력해 주는데, 여기서 count(*)는 group by로 합쳐진 데이터의 개수를 세어주는 것입니다!

​

Group by 제대로 알아보기: SQL 쿼리가 실행되는 순서

select name, count(*) from users

group by name;

👉 위 쿼리가 실행되는 순서: from → group by → select

[코드스니펫] users 테이블 전체 불러오기

select * from users;

[코드스니펫] users 테이블에서 '신' 씨를 가진 데이터만 불러와서 개수 살펴보기

select * from users

where name = "신**";

지난주 배웠던 where를 사용해서 뽑아봤어요. 14개가 나오네요.

[코드스니펫] group by를 사용해서 '신'씨를 가진 데이터가 몇 개인지 살펴보기

select name, count(*) from users

group by name;

이렇게 나오면 성공! 신씨를 가진 데이터가 14개로 동일하게 나오죠?

​

Group by 기능 알아보기

사용할 테이블 알아보기!

자, select * from checkins limit 10 쿼리를 날려서 테이블 구조를 볼까요?

checkins 테이블 보러가기

이번 강의에서 사용할 필드는 요거! week: 수강생이 '오늘의 다짐'을 남긴 시점의 강의 주차를 의미합니다. likes: 남긴 '오늘의 다짐' 게시물에 달린 좋아요의 수를 의미합니다.

동일한 범주의 개수 구하기

[코드스니펫] 주차별 '오늘의 다짐' 개수 구하기

select 범주별로 세어주고 싶은 필드명, count(*) from 테이블명

group by 범주별로 세어주고 싶은 필드명;

이 규칙으로 하면 된답니다. 참 쉽죠? (여기선, week가 범주별로 세어주고 싶은 필드명이죠!)

동일한 범주에서의 최솟값 구하기

[코드스니펫] 주차별 '오늘의 다짐'의 좋아요 최솟값 구하기

select 범주가 담긴 필드명, min(최솟값을 알고 싶은 필드명) from 테이블명

group by 범주가 담긴 필드명;

이 규칙으로 하면 된답니다.

여기서는, 범주가 담긴 필드명은 week, 최솟값을 알고 싶은 필드명은 likes 겠죠.

동일한 범주에서의 최댓값 구하기

[코드스니펫] 주차별 '오늘의 다짐'의 좋아요 최댓값 구하기

select 범주가 담긴 필드명, max(최댓값을 알고 싶은 필드명) from 테이블명

group by 범주가 담긴 필드명;

이 규칙으로 하면 된답니다. 최솟값 구할때와 달라진건 하나밖에 없어요.

여기서는, 범주가 담긴 필드명은 week, 최대값을 알고 싶은 필드명은 likes 겠죠.

동일한 범주의 평균 구하기

[코드스니펫] 주차별 '오늘의 다짐'의 좋아요 평균값 구하기

select 범주가 담긴 필드명, avg(평균값을 알고 싶은 필드명) from 테이블명

group by 범주가 담긴 필드명;

이 규칙으로 하면 된답니다. 최솟값 구할 때와 달라진건 하나밖에 없어요.

여기서는, 범주가 담긴 필드명은 week, 평균값을 알고 싶은 필드명은 likes 겠죠.

동일한 범주의 합계 구하기

[코드스니펫] 주차별 '오늘의 다짐'의 좋아요 합계 구하기

select 범주가 담긴 필드명, sum(합계를 알고 싶은 필드명) from 테이블명

group by 범주가 담긴 필드명;

이 규칙으로 하면 된답니다. 최솟값 구할 때와 달라진건 하나밖에 없어요.

여기서는, 범주가 담긴 필드명은 week, 합계를 알고 싶은 필드명은 likes 겠죠.

​

Order by로 앞의 결과를 정렬해보자

[코드스니펫] 원본 쿼리 살펴보기

select name, count(*) from users

group by name;

[코드스니펫] 결과의 개수 오름차순으로 정렬해보기

select name, count(*) from users

group by name

order by count(*);

아까 코드에 order by count() 만 추가해줬어요. 갯수 (count() 값)을 기준으로 정렬해달라는 뜻이에요.

[코드스니펫] 결과의 개수 내림차순으로 정렬해보기

select name, count(*) from users

group by name

order by count(*) desc;

👉 [꿀팁!] 여기서의 desc는 내림차순을 의미하는 영단어 descending의 약자입니다.

Order by 사용해보기

Order by는 모든 SQL 쿼리에 적용될 수 있는 기능입니다.

👉 like를 많이 받은 순서대로 '오늘의 다짐'을 출력해 볼까요?

select * from checkins

order by likes desc;

적게 받은 순서대로는 보기 위해서는, 맨 끝의 desc를 제거해 주면 되어요!

규칙을 살펴볼까요?

select * from 테이블명

order by 정렬의 기준이 될 필드명;

바로 위 예시에서는, like의 갯수가 정렬의 기준이 되는 필드명이겠죠!

Order by 제대로 알아보기: SQL 쿼리가 실행되는 순서

select name, count(*) from users

group by name

order by count(*);

위 쿼리가 실행되는 순서: from → group by → select → order by

1.from users: users 테이블 데이터 전체를 가져옵니다.

2.group by name: users 테이블 데이터에서 같은 name을 갖는 데이터를 합쳐줍니다.

3.select name, count(*): name에 따라 합쳐진 데이터가 각각 몇 개가 합쳐진 것인지 세어줍니다.

예) 이, 이, 김, 김, 박 이렇게 데이터가 있었다면, 이는 2개, 김은 2개, 박은 1개겠죠!

4.order by count(*): 합쳐진 데이터의 개수에 따라 오름차순으로 정렬해줍니다.

​

Where와 Group by, Order by 함께 사용해보기

😎 원리는 간단해요. Where절로 조건이 하나 추가되고, 그 이후에 Group by, Order by가 실행되는 것!

웹개발 종합반의 결제수단별 주문건수 세어보기

1. orders 테이블에서 주문 데이터를 읽어오고

2. 웹개발 종합반 데이터만 남기고

3. 결제수단(범주) 별로 그룹화하고

4. 결제수단별 주문건수를 세어준다!

select payment_method, count(*) from orders

where course_title = "웹개발 종합반"

group by payment_method;

위와 같이, group by와 select 사이에 where로 조건을 넣어주면 끝!

​

더 알아보기: SQL 쿼리가 실행되는 순서

select payment_method, count(*) from orders

where course_title = "웹개발 종합반"

group by payment_method;

👉 위 쿼리가 실행되는 순서: from → where → group by → select

1.from orders: users 테이블 데이터 전체를 가져옵니다.

2.where course_title = "웹개발 종합반": 웹개발 종합반 데이터만 남겨줍니다.

3.group by payment_method: 같은 payment_method을 갖는 데이터를 합쳐줍니다.

4.select payment_method, count(*): payment_method에 따라 합쳐진 데이터가 각각 몇 개가 합쳐진 것인지 세어줍니다.

kakaopay는 1개겠죠!

예) CARD, CARD, kakaopay 이렇게 데이터가 있었다면, CARD는 2개, 만약 order by가 추가된다면? order by는 맨 나중에 실행됩니다! (결과물을 정렬해주는 것이기 때문!)

​

혼자서도 문제를 해결하려면

👉 다시 돌아온 같이 삽질해보기 시간!

SQL을 사용하다보면 예상하지 못했던 에러나 결과를 자주 마주하게 됩니다. 이에 대한 해결책을 모두 외우는 것은 현실적으로 불가능하겠죠?

자주 발생하는 문제, 저와 같이 삽질하면서 해결해봐요!

왜 이런 결과가 나왔을까요?

👉 범주에 따른 통계치를 구하고 싶어 group by를 사용해 보았습니다. 하지만, 통계치는 나오지 않고 4개의 데이터만 출력된 것을 알 수 있죠.

왜 그럴까요?

👉 범주별로 묶어달라는 명령어 (group by)는 작성해줬지만, 묶은 데이터를 '어떤 통계치'로 출력해달라는 명령어가 없어서 그렇습니다!

그러면 어떻게 하면 좋을까요?

👉 '어떤 통계치로' 출력해달라는 명령어를 추가하면 됩니다. 범주별 갯수를 세어볼까요? 아래와 같이요!

앗, 뭔가 원하는 통계치가 나온 것 같지만 각각 어떤 범주에 대한 통계치인지는 나와있지 않아요.

그도 당연한 것이, select 문 안에 count(*)만 적혀있어서 그렇겠죠?

👉 그렇다면 group by에 들어간 필드를 똑같이 적어주면 됩니다. 깔끔하게 원하는 데이터가 잘 나오는걸 확인할 수 있습니다! 👉 원하는 결과가 출력되지 않을 땐, SQL 쿼리의 실행순서에 따라 차근차근 생각해봐요. 어디서 쿼리를 잘못 짰는지 찾아내는 가장 빠르고 정확한 방법입니다!

​

​

문자열을 기준으로 정렬해보기

select * from users order by email;

동일하게 알파벳으로도 잘 정렬이 됩니다.

select * from users order by name;

그리고, 한글로도 잘 정렬이 됩니다. 참 쉽죠?

시간을 기준으로 정렬해보기

select * from users order by created_at desc;

최근 데이터부터 보고싶을 때, 유용해요!

Group by 연습하기

👉 자, Group by는 퀴즈를 풀어볼까요? (1) 원하는 테이블과 (2) 범주로 사용할 필드 (3) 범주에 따라 통계를 계산하고 싶은 필드 (개수의 경우 제외)

이 세 가지만 기억하면 됩니다!

​

[꿀팁] 이렇게 쿼리를 작성하면 편해요!

👉 1) show tables로 어떤 테이블이 있는지 살펴보기 2) 제일 원하는 정보가 있을 것 같은 테이블에 select * from 테이블명 limit 10 쿼리 날려보기 3) 원하는 정보가 없으면 다른 테이블에도 2)를 해보기 4) 테이블을 찾았다! 범주를 나눠서 보고싶은 필드를 찾기 5) 범주별로 통계를 보고싶은 필드를 찾기 6) SQL 쿼리 작성하기!

​

별칭 기능: Alias

👉 쿼리가 점점 길어지면서 종종 헷갈리는 일이 생길 수 있습니다. 그래서 SQL은 Alias라는 별칭 기능을 지원합니다.

select * from orders o

where o.course_title = '앱개발 종합반'

요렇게 테이블명 뒤에 as를 붙여서 별칭을 추가하는 것도 가능하고,

select payment_method, count(*) as cnt from orders o

where o.course_title = '앱개발 종합반'

group by payment_method

count(*)가 아니라 cnt로 출력되었네요!

👉 이처럼, 혼동을 최소화하고 원하는 이름으로 결과를 출력하기 위해 사용돼요. 이 기능은 다음 주차부터 굉장히 유용하게 사용되니, '이런게 있구나' 정도로만 기억해 주세요!

​

​

​Join 이란?

👉 Join이란? 두 테이블의 공통된 정보 (key값)를 기준으로 테이블을 연결해서 한 테이블처럼 보는 것을 의미해요.

예) user_id 필드를 기준으로 users 테이블과 orders 테이블을 연결해서 한 눈에 보고 싶어요!

👉 위의 예시와 같이, 두 테이블의 정보를 연결해서 함께 보고싶을 때가 있겠죠?

그럴 때를 대비해서 무언가 연결된 정보가 있을 때, user_id 처럼 동일한 이름과 정보가 담긴 필드를 두 테이블에 똑같이 담아놓는답니다. 이런 필드를 두 테이블을 연결시켜주는 열쇠라는 의미로 'key'라고 불러요.

[오늘의 꿀팁!] 병원에서 의사선생님이 '환자번호 101번님 진료받으러 들어오세요' 라고 불렀는데 같은 환자번호를 가진 사람이 여러명이 있으면 누가 들어와야 할지 환자번호만으로 알 수 없겠죠? SQL에서의 Join도 마찬가지에요. key값을 사용해 연결하고 싶은 테이블에 찾아가서 똑같은 값을 가지는 key를 찾게 되는데, 똑같은 key를 가지는 데이터가 여러개 있으면 어느 데이터를 가져와서 연결해야 할지 알 수 없어요.

[코드스니펫] Join을 사용해서 Key값으로 두 테이블 연결해보기

select * from point_users

left join users

on point_users.user_id = users.user_id

👉 [오늘의 꿀팁!] 혹시 엑셀을 잘 쓰신다면?

SQL의 Join은 엑셀의 vlookup과 동일하다고 생각하시면 됩니다 :-)

[코드스니펫] 유저 데이터로 Left Join 이해해보기

select * from users u

left join point_users p

on u.user_id = p.user_id;

어떤 데이터는 모든 필드가 채워져있지만, 어떤 데이터는 비어있는 필드가 있습니다. 꽉찬 데이터: 해당 데이터의 user_id 필드값이 point_users 테이블에 존재해서 연결한 경우 비어있는 데이터: 해당 데이터의 user_id 필드값이 point_users 테이블에 존재하지 않는 경우

비어있는 데이터의 경우, 회원이지만 수강을 등록/시작하지 않아 포인트를 획득하지 않은 회원인 경우에요!

​

Where를 사용해서 직접 확인해보기!

👉 꽉찬 데이터, 비어있는 데이터에서 하나씩 user_id를 뽑아서 orders 테이블에서 조회해볼까요?

꽉찬 데이터의 user_id값 예시: d90e7626 비어있는 데이터의 user_id값 예시: 3b3eac9f

😎 위의 user_id값으로 조회하면 데이터가 정상적으로 한 개의 데이터가 출력되지만, 아래의 user_id값으로 조회하면 데이터가 출력되지 않는 것을 알 수 있습니다!

[코드스니펫] 유저 데이터로 Inner Join 이해해보기

select * from users u

inner join point_users p

on u.user_id = p.user_id;

앗, 여기서는 비어있는 필드가 있는 데이터가 없어요! 그 이유는, 같은 user_id를 두 테이블에서 모두 가지고 있는 데이터만 출력했기 때문이에요.

​

Join 함께 연습해보기

[실습] orders 테이블에 users 테이블 연결해보기

select * from orders o

inner join users u

on o.user_id = u.user_id;

[오늘의 팁!] 주문을 하기 위해서는 회원정보가 있어야 할테니, orders 테이블에 담긴 user_id는 모두 users 테이블에 존재하겠죠?

[실습] checkins 테이블에 users 테이블 연결해보기

select * from checkins c

inner join users u

on c.user_id = u.user_id;

👉 [오늘의 팁!]

연결의 기준이 되고싶은 테이블을 from 절에, 기준이 되는 테이블에 붙이고 싶은 테이블을 Join 절에 위치해 놓습니다.

[실습] enrolleds 테이블에 courses 테이블 연결해보기

select * from enrolleds e

inner join courses c

on e.course_id = c.course_id;

SQL 쿼리가 실행되는 순서

select * from enrolleds e

inner join courses c

on e.course_id = c.course_id;

👉 위 쿼리가 실행되는 순서: from → join → select

[코드스니펫] 과목별 오늘의 다짐 갯수 세어보기

select co.title, count(co.title) as checkin_count from checkins ci

inner join courses co

on ci.course_id = co.course_id

group by co.title

[오늘의 팁!] 2주차에 배운 alias는 이렇게 사용하면 편합니다. 연결되는 테이블이 많아지면서 필드명과 테이블명이 헷갈려 실수할 수 있는데, 이렇게 alias를 지정해 주면 편하고 깔끔하게 SQL 쿼리를 작성할 수 있어요.

point_users 테이블에 users 테이블 연결해서 순서대로 정렬해보기

[코드스니펫] 많은 포인트를 얻은 순서대로 유저 데이터 정렬해서 보기

select * from point_users p

inner join users u

on p.user_id = u.user_id

order by p.point desc

orders 테이블에 users 테이블 연결해서 통계치 내보기

[코드스니펫] 네이버 이메일 사용하는 유저의 성씨별 주문건수 세어보기

select u.name, count(u.name) as count_name from orders o

inner join users u

on o.user_id = u.user_id

where u.email like '%naver.com'

group by u.name

SQL 쿼리가 실행되는 순서

select u.name, count(u.name) as count_name from orders o

inner join users u

on o.user_id = u.user_id

where u.email like '%naver.com'

group by u.name

위 쿼리가 실행되는 순서: from → join → where → group by → select

1.from orders o: orders 테이블 데이터 전체를 가져오고 o라는 별칭을 붙입니다.

2.inner join users u on o.user_id = u.user_id : users 테이블을 orders 테이블에 붙이는데, orders 테이블의 user_id와 동일한 user_id를 갖는 users 테이블 데이터를 붙입니다. (*users 테이블에 u라는 별칭을 붙입니다)

3.where u.email like '%naver.com': users 테이블 email 필드값이 naver.com으로 끝나는 값만 가져옵니다.

4.group by u.name: users 테이블의 name값이 같은 값들을 뭉쳐줍니다.

5.select u.name, count(u.name) as count_name : users 테이블의 name필드와 name 필드를 기준으로 뭉쳐진 갯수를 세어서 출력해줍니다.

​

Join의 실행 순서는 항상 from 과 붙어다닌다고 생각하면 편해요!Select를 두 번 할 게 아니라, 한번에 모아서 보고싶은 경우, 있을걸요!

예를 들면 이렇게!

👉 근데, 그러려면 한 가지 조건이 있어요! 노란색과 파란색 박스의 필드명이 같아야 한답니다. 🙂 (당연하겠죠?)

Union을 이용해서 아래와 같은 모습을 만들어볼까요?

우선, 'month'를 붙여줘야겠네요!

select '7월' as month, c.title, c2.week, count(*) as cnt from checkins c2 inner join courses c on c2.course_id = c.course_id inner join orders o on o.user_id = c2.user_id where o.created_at < '2020-08-01' group by c2.course_id, c2.week order by c2.course_id, c2.week

여기에 아래 위로 Union all을 사용해 붙여주면 끝!

( select '7월' as month, c.title, c2.week, count() as cnt from checkins c2 inner join courses c on c2.course_id = c.course_id inner join orders o on o.user_id = c2.user_id where o.created_at < '2020-08-01' group by c2.course_id, c2.week order by c2.course_id, c2.week ) union all ( select '8월' as month, c.title, c2.week, count() as cnt from checkins c2 inner join courses c on c2.course_id = c.course_id inner join orders o on o.user_id = c2.user_id where o.created_at > '2020-08-01' group by c2.course_id, c2.week order by c2.course_id, c2.week )

앗, 그런데, 한 가지! 정렬이 깨졌네요!? 😂

👉 네 맞습니다! union을 사용하면 내부 정렬이 먹지 않아요. 이 때 유용한 방법이 있지요. 바로, SubQuery(서브쿼리) !

​Subquery: 원하는 데이터를 더 쉽게 얻어보기

Subquery란? 쿼리 안의 쿼리라는 의미입니다. 하위 쿼리의 결과를 상위 쿼리에서 사용하면, SQL 쿼리가 훨씬 간단해져요!

즉, Subquery를 사용하지 않아도 원하는 데이터를 얻어낼 수 있겠지만, 더 편하고 간단하게 원하는 데이터를 얻기 위해 사용되는 파워풀한 기능입니다.

Subquery에 대한 이해도가 생기면, With구문을 이용해서 더 간단하게 만들어볼게요!

​

Subquery 사용방법 익혀보기

kakaopay로 결제한 유저들의 정보 보기

→ 우선, 이렇게 볼 수 있겠죠? users 와 orders 의 inner join으로!

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'

그런데, 이것을 이렇게 할 수도 있습니다. 조금 더 직관적이지 않나요?

우선 kakaopay로 결제한 user_id를 모두 구해보기 → K 라고 합시다.

select user_id from orders

where payment_method = 'kakaopay'

그 후에, user_id가 K 에 있는 유저들만 골라보기

→ 이게 바로 서브쿼리!

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'

)

자주 쓰이는 Subquery 유형 알아보기

👉 자주 쓰는 Subquery를 먼저 알아볼까요? Subquery는 where, select, from 절에서 유용하게 사용될 수 있어요!

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');

쿼리가 실행되는 순서를 이렇게 상상하면 편해요!

👉 (1) from 실행: users 데이터를 가져와줌 (2) Subquery 실행: 해당되는 user_id의 명단을 뽑아줌 (3) where .. in 절에서 subquery의 결과에 해당되는 'user_id의 명단' 조건으로 필터링 해줌 (4) 조건에 맞는 결과 출력

Select 에 들어가는 Subquery

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

select 필드명, 필드명, (subquery) from .. 이렇게요!

앞서 보았던것처럼, '오늘의 다짐' 데이터를 보고 싶은데 '오늘의 다짐' 좋아요의 수가, 본인이 평소에 받았던 좋아요 수에 비해 얼마나 높고 낮은지가 궁금할 수 있겠죠?

그럼, 평균을 먼저 구해봅시다! user_id='4b8a10e6' 를 예시로!

select avg(likes) from checkins c2 where c2.user_id = '4b8a10e6'

그러면, 이렇게 표현할 수 있어요!

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;

쿼리가 실행되는 순서를 이렇게 상상하면 편해요!

👉 (1) 밖의 select * from 문에서 데이터를 한줄한줄 출력하는 과정에서 (2) select 안의 subquery가 매 데이터 한줄마다 실행되는데 (3) 그 데이터 한 줄의 user_id를 갖는 데이터의 평균 좋아요 값을 subquery에서 계산해서 (4) 함께 출력해준다!

From 에 들어가는 Subquery (가장 많이 사용되는 유형!)

👉 From은 언제 사용하면 좋을까요? 내가 만든 Select와 이미 있는 테이블을 Join하고 싶을 때 사용하면 딱이겠죠!

​

자, 우선 유저 별 좋아요 평균을 먼저 구해볼까요?

→ checkins 테이블을 user_id로 group by 하면 되겠죠?

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

자, 이제 여기서 해당 유저 별 포인트를 보고 싶다면?

→ 그러면, 포인트와 like의 상관정도를 알 수 있겠죠?

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

쿼리가 실행되는 순서를 이렇게 상상하면 편해요!

👉 (1) 먼저 서브쿼리의 select가 실행되고, (2) 이것을 테이블처럼 여기고 밖의 select가 실행!

​

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

[코드스니펫] 코스제목별 like 개수, 전체, 비율

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

다시 위의 코드를 볼까요?

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

이렇게 계속 서브쿼리가 붙으면, inner join 안쪽이 너무 헷갈리겠죠!

→ 그 때 쓰는 것이 with 절! 결과는 같은데 훨씬 보기가 좋죠?

문자열 데이터 다뤄보기

👉 실제 업무에서는, 문자열 데이터를 원하는 형태로 한번 정리해야 하는 경우가 많습니다.

문자열 쪼개보기

👉 이메일 주소에서 @앞의 아이디만 가져오거나, @뒤의 이메일 도메인을 가져오고 싶어요!

SUBSTRING_INDEX 라는 문법을 사용하면 됩니다. 바로 쿼리를 볼까요?

[코드스니펫] 이메일에서 아이디만 가져와보기

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

👉 @를 기준으로 텍스트를 쪼개고, 그 중 첫 번째 조각을 가져오라는 뜻!

[코드스니펫] 이메일에서 이메일 도메인만 가져와보기

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

👉 @를 기준으로 텍스트를 쪼개고, 그 중 마지막 조각을 가져오라는 뜻!

문자열 일부만 출력하기

👉 orders 테이블에서 created_at을 날짜까지만 출력하게 해봅시다!

SUBSTRING 이라는 문법을 사용하면 됩니다. 바로 쿼리를 보러 가시죠!

[코드스니펫] orders 테이블에서 날짜까지 출력하게 해보기

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

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

[코드스니펫] 일별로 몇 개씩 주문이 일어났는지 살펴보기

select substring(created_at,1,10) as date, count(*) as cnt_date from orders group by date

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

👉 10000점보다 높은 포인트를 가지고 있으면 '잘 하고 있어요!', 평균보다 낮으면 '조금 더 달려주세요!' 라고 표시해 주려면 어떻게 해야할까요?

[코드스니펫] 포인트 보유액에 따라 다르게 표시해주기

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

CASE: 실전을 위한 트릭!

👉 Subquery를 이용하면 이런 통계도 낼 수 있어요!

우선 몇 가지로 구분을 나누고,

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

서브쿼리를 이용해서 group by로 통계를 낼 수 있습니다.

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

with 절과 함께하면 금상첨화죠!