[공부] 프로그래밍/DB
[SQL 공부] INNER JOIN
woodisco
2023. 4. 12. 01:26
INNER JOIN
-> 테이블들의 교집합을 만들어 내는 조인
① posts 테이블
id
|
body
|
user_id
|
1
|
Hello
|
3
|
2
|
Hi
|
1
|
3
|
Good
|
2
|
② users 테이블
id
|
name
|
1
|
taro
|
2
|
jiro
|
3
|
hanako
|
4
|
saito
|
위의 두 테이블을 LEFT 조인을 할 경우
SELECT *
FROM users
JOIN posts
ON users.id = posts.user_id
실행결과는
id
|
name
|
id
|
body
|
user_id
|
3
|
hanako
|
1
|
Hello
|
3
|
1
|
jiro
|
2
|
Hi
|
1
|
2
|
taro
|
3
|
Good
|
2
|
WITH tmp1 AS
(
SELECT
account_id
, account_name
, cross_category
, company_name_lbc
, advertiser_name
FROM 테이블명1
)
, tmp2 AS
(
SELECT
log_date
, ss_account_id
, SUM(revenue) AS revenue
, SUM(clicks) AS clicks
, SUM(impressions) AS impressions
FROM 테이블명2
WHERE log_date BETWEEN '해당날짜' AND '해당날짜'
GROUP BY 1,2
)
SELECT
a.account_id
, account_name
, cross_category
, company_name_lbc AS "해당이름"
, advertiser_name AS "해당이름"
, revenue
, clicks
, impressions
FROM tmp1 a
INNER JOIN tmp2 b ON a.account_id = b.ss_account_id
ORDER BY log_date, a.account_id ASC
;