[SQL 공부] LEFT JOIN

 

LEFT JOIN = LEFT OUTER JOIN

-> FROM의 테이블을 왼쪽으로 정렬하여 전부 표시한다.

 

① 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
LEFT 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
4	saito	NULL	NULL	NULL

 

WITH tmp AS
 (
    SELECT top 100
        ss_account_id,
        SUM(revenue) AS sum_revenue,
        SUM(clicks) AS sum_clicks,
        SUM(impressions) AS sum_impressions
    FROM 테이블명1
    WHERE log_date BETWEEN '해당날짜' AND '해당날짜'
    GROUP BY ss_account_id
    ORDER BY sum_revenue DESC 
 )
 
SELECT
    a.ss_account_id,
    b.account_name,
    a.sum_revenue AS rev,
    a.sum_clicks AS clicks,
    a.sum_impressions AS impressions
FROM tmp AS a
LEFT JOIN 테이블명2 AS b ON a.ss_account_id = b.ss_account_id
;

WITH tmp1 AS
 (
    SELECT 
        log_date,
        account_id,
        SUM(revenue) AS sum_revenue,
        SUM(clicks) AS sum_clicks,
        SUM(impressions) AS sum_impressions
    FROM 테이블명1
    WHERE log_date BETWEEN '해당날짜' AND '해당날짜'
    GROUP BY 1,2
)
  , tmp2 AS
(
    SELECT 
    	 log_date,
    	 account_id,
         SUM(all_conversions) AS sum_conversions
    FROM 테이블명2
    WHERE nonpayment_flag = 1
    AND log_date BETWEEN '해당날짜' AND '해당날짜'
    GROUP BY 1,2
)
  , tmp3 AS
(
    SELECT
  	    account_id,
  	    account_name
    FROM 테이블명3
  	GROUP by 1,2
)

SELECT TOP 100
    c.account_id,
    c.account_name,
    a.sum_revenue AS rev,
    a.sum_clicks AS clicks,
    a.sum_impressions AS impressions,
    b.sum_conversions AS conversion
FROM tmp1 AS a
LEFT JOIN  tmp2 AS b ON a.account_id = b.account_id
LEFT JOIN tmp3 AS c ON a.account_id = c.account_id
ORDER BY rev DESC 
;