[postgresql] Left join 예제

SELECT T1.category, T1.sumCnt AS sumCnt, T1.changedCnt, COALESCE(T2.deletedCnt, 0)
FROM(
SELECT COALESCE(category, 0) AS category, COUNT(id) AS sumCnt, SUM(CASE WHEN  createdate != changedate AND SUBSTR(changedate,1,10) = '2015-01-26' THEN 1 ELSE 0 END) AS changedCnt
FROM metadata
GROUP BY category
) AS T1
LEFT OUTER JOIN
(SELECT COALESCE(category, 0) AS category,  SUM(CASE WHEN  TO_CHAR(deletiondate,'YYYY-MM-DD') = '2015-01-26' THEN 1 ELSE 0 END) AS deletedCnt
FROM deletedmetadata
GROUP BY category
)AS T2
ON T1.category = T2.category