How to get 0 COUNT values in SQL.

Bob Swisshelm from Lilly had the most elegant solution to this. Please note that if you specify the child column inside the count operator a count of 0 will be returned when no child rows exist!

  1 select o.order_no, count(p.order_no)   2 from orders o, product p
  3 where o.order_no = p.order_no (+)
  4 and o.order_no < 10
  5* group by o.order_no

  ORDER_NO COUNT(P.ORDER_NO)

---------- -----------------
         1                 0
         2                 1
         3                 0
         4                 0
         5                 3
         6                 0
         7                 0
         8                 1
         9                 0

9 rows selected. Received on Fri Aug 27 1993 - 15:04:52 CEST