[TIL] SQL복습&심화_21일차(UNION,JOIN,SUB QUERY)

이전 시간에 이어서 튜터님께 배운 SQL 을 복습하고 실습하는 수업에 대한 정리를 하겠습니다.

데이터와 친해지는 SQL - 3회차

🔥 수업 목표

1. SQL 테이블 결합: UNION, UNION ALL 함수

2. SQL 테이블 결합: JOIN 함수 ( PK와 FK의 개념 )

3. UNION 과 JOIN 함수의 차이점

4. 실습 문제

예제 샘플 데이터셋은 튜터님께서 제공해주셨습니다.

1. SQL 테이블 결합 : UNION, UNION ALL 함수

1) UNION 함수

① 여러개의 SELECT 문의 결과를 하나의 테이블로 연결하여 보고 싶을 때 사용됩니다.

② 수직 결합, 행을 밑으로 추가

[ 예제 ] A라는 테이블에 1월 데이터가 있고, B라는 테이블에 2월달 데이터가 존재한다고 가정해볼게요.

Q. 1월달과 2월달 매출데이터를 하나의 결과로 보고싶을 수 있겠죠? 이러한 경우, UNION 또는 UNON ALL을 사용합니다!

[ SQL 작성구문 ]

union/union all 은 컬럼 순서가 같고, 그 형식이 반드시 같아야 합니다.

# union/union all 기본구조
select name, goods_nm, pay_date #컬럼 순서가 같고, 그 형식이 같아야 함 
from 테이블명1

union (all) #수직결합 명시 # (all)은 생략 가능

select name, goods_nm, pay_date
from 테이블명 2

2) UNION, UNION ALL 의 공통점, 차이점, 주의점

💡 공통점: 두 쿼리문을 하나로 수직 결합

💡 주의점 :

(1) 열의 갯수와 순서가 모든 쿼리에서 동일해야 합니다.

(2) 데이터의 형식이 일치(컬럼명도 동일)해야 합니다.

💡 차이점:

(1) UNION 은 중복된 행을 하나로 표기 ( 중복 제거하고 표기 )

(2) UNION ALL 은 모두 표현 ( 중복을 제거하지 않고 표기 )

[ TIP ] SQL에서의 합집합 / 교집합 / 차집합

2. SQL 테이블 결합 : JOIN 함수

1) SQL 구문의 꽃 = JOIN

JOIN 함수도 여러 개의 SELECT 문의 결과를 단일 결과 세트로 연결할 때 사용합니다. 원하는 데이터를 추출하기 위해 두 개의 테이블을 결합하는 역할을 수행해요.

[예시 테이블]

2) 조인하는 3단계

1️⃣ 공통컬럼 찾기

공통컬럼 = 두 테이블에서 공통으로 존재하는 컬럼

join문에서 on으로 연결합니다.

위 테이블에서는 ‘name’ 컬럼이 공통컬럼

2️⃣ 공통컬럼 관계 찾기(PK와 FK 찾기)

어떤 테이블이 기준이 되고, 어떤테이블이 종속되어있는지 파악

1(기준) : 1(기준)
1(기준) : N(비교)
N(비교) : 1(기준)
N(비교) : N(비교)

N(비교) : N(비교) 구조

기준 테이블이 없으므로 데이터가 부풀려져 리턴될 수 있고 데이터 검증을 할 수 없기에, 조인함수에서는 N:N 구조는 사용하지 않는 것을 권장합니다.

[🔎TIP ✨ ]

MySQL 환경에서는 PK 및 FK 컬럼을 명시하여 테이블 관계를 파악해야 합니다.

[🔎 PK와 FK의 개념 ]

PK : 기본키라고 부르며, NULL 일 수 없고, 유일한 값을 가집니다. 테이블 당 하나의 기본키만 가질 수 있습니다. 따라서, PK 컬럼은 모든 데이터를 식별하는 기준이 되는 컬럼입니다.

FK : 외래키라고 부르며, 다른 테이블의 PK 와 연결되어 테이블 간 관계를 나타내주는 컬럼을 의미합니다. 따라서, 기준이 되는 컬럼(PK)을 확인하기 위한 연결컬럼(FK) 입니다.

3️⃣ 적절한 조인 방식 찾기

조인함수는 아래 이미지와 같은 다양한 방식을 지원해요.

3) INNER JOIN

① INNER JOIN의 간단한 예

간단한 inner join의 경우, 아래와 같이 작성 할 수 있어요.

두 테이블에 where 절이 없을 때, 간단하게 조인하실 수 있습니다.

# INNER JOIN 작성법(기초편)
select 컬럼1, 컬럼2... 
from 테이블1 as a
inner join 테이블2 as b
on a.공통컬럼=b.공통컬럼

② INNER JOIN의 약간은 복잡한 예(subquery 사용)

서브쿼리는 ‘쿼리를 구조적으로 작성하기’ 위해 있습니다.

서브쿼리 1 과 서브쿼리2를 보시면, 동일 선상에서 괄호가 열리고, 닫힌 것을 보실 수 있어요. 쿼리를 구조적으로 작성하는 것은 필수요소는 아니지만, 추후 다중 조인시 쿼리의 실행순서를 파악(에러 발생 시 문제 파악을 위해)하는 데 많은 도움됩니다.

서브쿼리1,2가 실행되고 INNER JOIN으로 두 서브쿼리를 결합해줄게요.

그 다음, 맨 마지막 ON 절에 공통컬럼을 넣으면, 테이블1과 테이블2의 조건을 만족하는 결과를 맨 바깥쪽 쿼리의 SELECT절에서 조회 할 수 있게 됩니다.

# INNER JOIN 작성법 (서브쿼리 활용편)
select 테이블명1.컬럼a, .... 
from(   select 컬럼a, 컬럼b ... 
				from 테이블1
		 )as 테이블명1 #첫번째 셀렉트 절의 결과를 '테이블명1' 로 명시 
inner join #(또는 join) #테이블1과 테이블2를 inner join 하겠다는 의미
	  (   select 컬럼c, 컬럼d... 
			  from 테이블2
		 )as 테이블명2 #두번째 셀렉트 절의 결과를 '테이블명2' 로 명시 
on a.공통컬럼=b.공통컬럼# 공통된 의미의 컬럼으로, 컬럼이름이 같을필요는 없음
# 공통컬럼은 ON 절의 조건절이 되며, 조건절은 1개가 아닐 수 있음
# 즉, on a.공통컬럼=b.공통컬럼 and a.공통컬럼2=b.공통컬럼2 가 될 수 있음

4) LEFT JOIN JOIN

① LEFT JOIN의 간단한 예

두 테이블에 where 절이 없을 때, 간단하게 조인하실 수 있습니다.

• 'LEFT 를 기준으로 위쪽에 작성하는 테이블'

⇒ 'LEFT에 위치해 있는 테이블'

⇒ 'LEFT JOIN 에서 기준이 되는 테이블'

기준이 되는 테이블은, 조인 조건을 만족해도/만족하지 못해도 모두 출력됩니다.

• 'LEFT 를 기준으로 아래쪽에 작성하는 테이블'

⇒ 'RIGHT 에 위치해 있는 테이블'

⇒ 'LEFT JOIN 에서 조건에 따라 출력되는 테이블'

RIGHT에 위치한 테이블은, 조인 조건을 만족하는 경우 출력되며, 만족하지 못할경우 NULL 값으로 출력됩니다.

# LEFT JOIN 작성법(기초편)
select 컬럼1, 컬럼2... 
from 테이블1 as 테이블명1
left join 테이블2 as 테이블명2   
on a.공통컬럼=b.공통컬럼

② LEFT JOIN의 약간은 복잡한 예(subquery 사용)

서브쿼리1,2가 실행되고 LEFT 조인문이 실행됩니다.

그 다음, 맨 마지막 ON 절에 조건절을 넣어주시면, LEFT 에 위치한 데이터는 모두 출력되며, RIGHT 에 위치한 데이터는 조건을 만족할 경우 값을 출력하지만, 조건을 만족하지 못할경우, NULL 값을 출력합니다.

5) RIGHT JOIN (잘 사용하지 않아요)

• 'RIGHT 를 기준으로 위쪽에 작성하는 테이블'

⇒ 'LEFT에 위치해 있는 테이블'

⇒ 'RIGHT JOIN' 에서 조건에 따라 출력되는 테이블’

LEFT에 위치한 테이블은, 조인 조건을 만족하는 경우 출력되며, 만족하지 못할경우 NULL 값으로 출력됩니다.

• 'RIGHT 를 기준으로 아래쪽에 작성하는 테이블'

⇒ 'RIGHT 에 위치해 있는 테이블'

⇒ 'RIGHT JOIN 에서 기준이 되는 테이블'

기준이 되는 테이블은, 조인 조건을 만족해도/만족하지 못해도 모두 출력됩니다.

# RIGHT JOIN 작성법(기초편)
select 컬럼1, 컬럼2... 
from 테이블1 as 테이블명1
right join basic.theglory2 as 테이블명2   
on a.공통컬럼=b.공통컬럼

6) FULL JOIN (비추천)

테이블의 모든 데이터를 출력하고 싶을 때 사용하는 JOIN입니다.

[🔎TIP ✨ ] MySQL 환경에서는 FULL OUTER JOIN을 지원하지 않습니다.

⚠️ FULL OUTER JOIN = LEFT JOIN + RIGHT JOIN 으로 표현 가능

이는 각각의 NULL 값을 출력해 주는 것과 같고, 이러한 NULL 값들은 쿼리의 비용 이슈(저장 공간 부족)를 야기할 수 있어 현업에서는 자주 사용하지 않습니다.

따라서 각 테이블의 모든 값이 출력되었고, 조건에 부합하지 않는 컬럼(없는 데이터는)들이 모두 NULL 값으로 반환됩니다.

# FULL OUTER JOIN 작성법(기초편)
select 컬럼1, 컬럼2,...
from table1 left join table2
on a.공통컬럼=b.공통컬럼

union

select 컬럼1, 컬럼2,...
from table1 right join table2
on a.공통컬럼=b.공통컬럼

3. UNION 과 JOIN 함수의 차이점

1) UNION 과 JOIN 은 필요에 따라 자유롭게 사용할 수 있어요.

1회차 강의를 함께 떠올려볼게요!

① 테이블들의 열을 맞춰 UNION 을 하고, 결합된 데이터를 가지고

공통컬럼을 기반으로 JOIN 을 할 수 있습니다.

반대로,

② 공통컬럼을 기준으로 JOIN 을 하고, 결합된 데이터를 가지고 열을 맞추는 UNION 을 할 수 있습니다.

[🔎TIP ✨ ] 추출쿼리에 따라, 순서에 상관없이 사용이 가능합니다.

[ 비교표 ]

4. 실습 문제

지난번 DBeaver에 업로드 한 users.csv 에 이번 실습에서는 추가로 payment.csv를 업로드합니다. DBeaver에 CSV 파일을 업로드를 알고 싶다면 여기를 클릭!!( 이 블로그 마지막 항목에 있습니다. )

# users 원본 데이터
SELECT logid, ip_addr, `date`, game_account_id,
       game_actor_id, `level`, `exp`, serverno, 
       zone_id, etc_num1, etc_num2, etc_str1, etc_num3, etc_str2
FROM basic.users;

# payment 원본 데이터
SELECT game_account_id, pay_amount, pay_type, approved_at
FROM basic.payment;

문제1

case when 구문과 join 함수를 사용하여, users 테이블을 기준으로, 결제를 한 유저와 결제를 하지 않은 유저를 추출해주세요.

SELECT case when pay_amount is null then '결재안함'
	   else '결재함' end gb, count(distinct a.game_account_id) usercrt
FROM basic.users a 
LEFT JOIN basic.payment b 
on a.game_account_id=b.game_account_id
group by gb ;

# 튜터님 정답쿼리
select case when b.game_account_id is null then '결제안함' else '결제함' end as gb
, count(distinct a.game_account_id)as usercnt 
from(	select game_account_id
		from basic.users 
	)as a 
left outer join 
	(	select game_account_id 
		from basic.payment
	)as b
on a.game_account_id=b.game_account_id
group by case when b.game_account_id is null then '결제안함' else '결제함' end

문제2

서버번호가 2 이상인 데이터와 결제방식이 CARD 인 경우를 join해 주시고
game_account_id 를 기준으로 game_actor_id 갯수를 중복값없이 세어주시고, actorcnt 으로 컬럼명을 명시해주세요.
pay_amount 값을 더해주시고, sumamount 으로 컬럼명을 명시해주세요.
having 을 사용하지 않고, subquery 사용으로 game_actor_id 갯수가 2 이상인 경우만 추출해주세요. 결과값은 아래와 같아야 합니다. (전체결과 중 일부입니다.)

# 처음 것
select *
from (SELECT a.game_account_id,
	         count(distinct a.game_actor_id) as actorcnt,
	         sum(distinct b.pay_amount) as sumamount # sum에 distinct하면 안됨
	  FROM (select *
	        from basic.users
	        where serverno >= 2
	       ) as a 
	  JOIN 
		  (select *
		   from basic.payment
		   where pay_type = 'CARD'
		   ) as b
	  on a.game_account_id=b.game_account_id
	  group by game_account_id
	  ) as c
where actorcnt >= 2 ;

# 고친 것 
select *
from (SELECT a.game_account_id,
	         count(distinct a.game_actor_id) as actorcnt,
	         b.sumamount
	  FROM (select game_account_id, game_actor_id
	        from basic.users
	        where serverno >= 2
	       ) as a 
	  JOIN 
		  (select game_account_id, sum(pay_amount) as sumamount
		   from basic.payment
		   where pay_type = 'CARD'
		   group by game_account_id
		   ) as b
	  on a.game_account_id=b.game_account_id
	  group by game_account_id
	  ) as c
where actorcnt >= 2 ;

# 튜터님 정답쿼리
select *
from(	select a.game_account_id, count(distinct game_actor_id) as actor_cnt, sum(pay_amount)as sumamount 
		from(	select game_account_id, game_actor_id 
				from basic.users 
				where serverno>=2
			)as a 
		inner join 
			(	select game_account_id, pay_amount 
				from basic.payment
				where pay_type='CARD'
			)as b 
		on a.game_account_id=b.game_account_id 
		group by a.game_account_id
	)as a 
where actor_cnt>=2

문제3

user 테이블에서 game_account_id, date, serverno 를 추출한 데이터와 매출 테이블에서 game_account_id 별 가장 마지막 결제일자를 찾고 join 을 진행해주세요.
그 다음, datediff 함수를 사용해 결제일자-접속일자를 구해주세요. 그리고 컬럼이름을 diffdate로 설정해주세요. 두 날짜의 형식은 같아야 합니다.
마지막으로, 인라인 뷰 subquery 를 이용하여 서버별 평균 diffdate를 구해주세요. 다만, 평균 datediff 컬럼은 정수 형태로 출력되어야 합니다. 또한, 조건절에 diffdate 값이 10일 이상인 경우를 필터링해주세요. 그리고 서버번호를 기준으로 내림차순 정렬해주세요. (전체결과 중 일부입니다.)

select serverno, round(avg(diffdate)) as avgdiffdate 
from (SELECT datediff(b.max_appdata, a.`date`) as diffdate ,serverno
	  FROM (select game_account_id, `date`, serverno
	        from basic.users
	       ) as a 
	  JOIN 
		  (select game_account_id, max(approved_at) as max_appdata
		   from basic.payment
		   group by game_account_id
		   ) as b
	  on a.game_account_id = b.game_account_id
	  where datediff(b.max_appdata, a.`date`) >= 10 # 작동 순서 생각하기!!
	  ) as c
group by serverno	  
order by serverno desc;

# 튜터님 정답쿼리
select serverno, round(avg(diffdate),0)as avgdiffdate
from(	select a.game_account_id, datediff(date_format(date2,('%Y-%m-%d')) ,`date`) as diffdate,serverno
		from(	select game_account_id, `date`, serverno
				from basic.users 
			)as a
		inner join 
			(	select game_account_id, date2
				from(	select game_account_id, max(approved_at)as date2 
						from basic.payment
						group by game_account_id
					)as b
				group by game_account_id
			)as c 
		on a.game_account_id=c.game_account_id 
	)as d 
where diffdate>=10
group by serverno
order by serverno desc

[🔎TIP ✨ ] SQL 의 작동순서

FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY

SQL 을 처음부터 보고싶으시다면 여기를 클릭📣

Python을 처음부터 보고싶으시다면 여기를 클릭📣

공부도 중요하지만 몸 관리 잘해야 해요~🌃

저작자표시 비영리 변경금지

'SQL' 카테고리의 다른 글

[TIL] SQL복습&심화_28일차(Window 함수 over) (0)	2024.07.01
[TIL] SQL복습&심화_20일차(집계함수,GROUP BY, HAVING,SUB QUERY) (0)	2024.04.24
[TIL] SQL복습&심화_19일차(SELECT, FROM,DISTINCT,WHERE,CASE WHEN,연산자,ORDER BY) (0)	2024.04.24
[TIL] SQL_10일차(Window Function,Rank,Sum, date type) (0)	2024.04.05
[TIL] SQL_9일차(null,coalesce,Pivot view) (0)	2024.04.04

Study Chronicle

[TIL] SQL복습&심화_21일차(UNION,JOIN,SUB QUERY)

데이터와 친해지는 SQL - 3회차

🔥 수업 목표

1. SQL 테이블 결합 : UNION, UNION ALL 함수

1) UNION 함수

2) UNION, UNION ALL 의 공통점, 차이점, 주의점

2. SQL 테이블 결합 : JOIN 함수

1) SQL 구문의 꽃 = JOIN

2) 조인하는 3단계

[🔎 PK와 FK의 개념 ]

3) INNER JOIN

4) LEFT JOIN JOIN

5) RIGHT JOIN (잘 사용하지 않아요)

6) FULL JOIN (비추천)

3. UNION 과 JOIN 함수의 차이점

4. 실습 문제

'SQL' 카테고리의 다른 글

티스토리툴바

[TIL] SQL복습&심화_21일차(UNION,JOIN,SUB QUERY)

데이터와 친해지는 SQL - 3회차

🔥 수업 목표

1. SQL 테이블 결합 : UNION, UNION ALL 함수

1) UNION 함수

2) UNION, UNION ALL 의 공통점, 차이점, 주의점

2. SQL 테이블 결합 : JOIN 함수

1) SQL 구문의 꽃 = JOIN

2) 조인하는 3단계

[🔎 PK와 FK의 개념 ]

3) INNER JOIN

4) LEFT JOIN JOIN

5) RIGHT JOIN (잘 사용하지 않아요)

6) FULL JOIN (비추천)

3. UNION 과 JOIN 함수의 차이점

4. 실습 문제

'SQL' 카테고리의 다른 글

관련글

티스토리툴바