Q

Counting rows in a LEFT OUTER JOIN

I have a question regarding outer joins. I want to show a list of all retailers in our database and a count of their orders. I came up with a query that shows all retailers that have at least one order and the count, but it won't show retailers which have no orders with a 0 count, as I had hoped.

I have a question regarding outer joins. I want to show a list of all retailers in our database and a count of

their orders. I came up with this query:

SELECT retailers.NA_ID, Count(Orders.NA_ID) As countOfOrders
FROM retailers
Left Outer Join Orders
ON retailers.NA_ID = Orders.NA_ID
WHERE (Orders.JointOR_ID <> '' OR Orders.OT_ID=2)
Group By retailers.NA_ID

This shows all retailers that have at least one order and the count, but it won't show retailers which have no orders with a 0 count, as I had hoped. Any help would be greatly appreciated.

Congratulations for using count(Orders.NA_ID) instead of count(*). As explained in Getting a count of zero for unmatched rows (19 May 2005), you must count the non-null values in the right table's join column.

Unfortunately, you've discovered another peccadillo with LEFT OUTER JOINs. As explained in LEFT OUTER JOIN with ON condition or WHERE condition? (16 September 2005), if you write a condition for any column in the right table, you need to put that condition into the ON clause, not the WHERE clause. Otherwise, your join will behave exactly like an INNER join. As you discovered, "it won't show retailers which have no orders with a 0 count." Change your query to this:

select retailers.NA_ID
     , count(Orders.NA_ID) as countOfOrders
  from retailers
left outer 
  join Orders
    on retailers.NA_ID = Orders.NA_ID
   and (
       Orders.JointOR_ID <> '' 
    or Orders.OT_ID = 2
       )
group 
    by retailers.NA_ID

Then you'll get the 0 counts as well.

This was first published in November 2005

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close