WITH name AS
(
SELECT 100 id, 1 idx, '김철수' name FROM dual
UNION ALL SELECT 101, 2, '김영희' FROM dual
)
, detail AS
(
SELECT 100 id, 1 idx, '디테일 블라블라' detail FROM dual
UNION ALL SELECT 101, 2, '디테일 블라블라' FROM dual
UNION ALL SELECT 100, 3, '디테일 블라블라' FROM dual
UNION ALL SELECT 100, 4, '디테일 블라블라' FROM dual
UNION ALL SELECT 100, 5, '디테일 블라블라' FROM dual
UNION ALL SELECT 101, 6, '디테일 블라블라' FROM dual
UNION ALL SELECT 101, 7, '디테일 블라블라' FROM dual
UNION ALL SELECT 101, 8, '디테일 블라블라' FROM dual
UNION ALL SELECT 100, 9, '디테일 블라블라' FROM dual
)
SELECT a.id
     , a.idx
     , a.name
     , COUNT(b.id) cnt
  FROM name a
  LEFT OUTER JOIN detail b
    ON a.id = b.id
 GROUP BY a.id, a.idx, a.name
 ORDER BY id
;
-- http://gurubee.net/lecture/1032
-- http://gurubee.net/oracle/sql