[SQL 공부] INNER JOIN

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
;