gistfile1.clj

clojureql.core> (let [photo-counts (table {} :photos [:user_id [:count:* :as :cnt]])]
                     (-> (table {} :users [:*])
                         (join photo-counts (= {:users.user_id :photos.user_id}))
                         compile))
"SELECT users.*,photos_aggregation.cnt FROM users 
LEFT OUTER JOIN 
(SELECT photos.user_id,count(photos.*) AS cnt FROM photos GROUP BY user_id) 
AS photos_aggregation ON (users.user_id = photos_aggregation.user_id)"