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.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading