[공부] 프로그래밍/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
;