Code Forums - View Single Post

bdl · 03-07-2004, 09:34 AM

If I may throw my own 2 cents into the hat; sdeming is absolutely right, you do need to perform a LEFT OUTER JOIN in order to retrieve a resultset regardless if there are categories with no articles. Reason being is, in a standard INNER JOIN, you're retrieving only the resultset that match all criteria, i.e. only categories with articles. If you change the query so you're performing a LEFT JOIN, it will retrieve all records from the table on the LEFT side of the equation (makes sense, right? ;-) ) and only those from the table on the right that match the criteria. So for example, breaking your SQL statement down further, let's look at it from the standpoint of LEFT JOIN.

Code:

SELECT Category.CatID,Category.CatName,count(ArtCat.ArticleID)

Ok so in the SELECT, the tables we want to use in the resultset are Category and ArtCat. Notice that Category is the table declaration on the left. It's that simple. If we wanted to retrieve all articles regardless if they have a category assigned to them, we would want all records from the table on the right, and it would become a RIGHT OUTER JOIN.

When you declare a LEFT or RIGHT OUTER JOIN, you skip the table declaration in the FROM (as you've already found out) and use it in the declaration for the LEFT JOIN itself. You then use the keyword ON to match the columns up. In a simple INNER JOIN, the ON would be something like using the WHERE clause. Furthermore, if we have two columns from different tables that have the same name, you can use the USING keyword instead, like

Code:

SELECT Category.CatID,
       Category.CatName,
       count(ArtCat.ArticleID) 
FROM Category
LEFT OUTER JOIN ArtCat
  USING (CatID) 
GROUP BY Category.CatID

Taking it one step further and cleaning the resultset up a bit, I'll use an ALIAS on the tables and on the COUNT so we have a little nicer formatting in the end, e.g.

Code:

SELECT c.CatID,
       c.CatName,
       COUNT(ac.ArticleID) AS ArticleCount
FROM Category AS c
LEFT OUTER JOIN ArtCat AS ac
  USING (CatID) 
GROUP BY c.CatID

So now, rather than having a result like COUNT(ArtCat.ArticleID) you can simply refer to it as ArticleCount (or whatever word strikes you as making sense). Strictly speaking, the OUTER keyword isn't needed, you can use simply LEFT or RIGHT join, but I find in circles where SQL is spoken most often they do use the full syntax in statements. I suppose it leaves very little chance for ambiguity in the statement.

Anyway, hope that clears things up a bit.

03-07-2004, 09:34 AM	#5 (permalink)
bdl Senior Contributor Join Date: May 2002 Location: vta.ca.usa Posts: 555	If I may throw my own 2 cents into the hat; sdeming is absolutely right, you do need to perform a LEFT OUTER JOIN in order to retrieve a resultset regardless if there are categories with no articles. Reason being is, in a standard INNER JOIN, you're retrieving only the resultset that match all criteria, i.e. only categories with articles. If you change the query so you're performing a LEFT JOIN, it will retrieve *all* records from the table on the LEFT side of the equation (makes sense, right? ;-) ) and only those from the table on the right that match the criteria. So for example, breaking your SQL statement down further, let's look at it from the standpoint of LEFT JOIN. Code: SELECT Category.CatID,Category.CatName,count(ArtCat.ArticleID) Ok so in the SELECT, the tables we want to use in the resultset are Category and ArtCat. Notice that Category is the table declaration on the left. It's that simple. If we wanted to retrieve all articles regardless if they have a category assigned to them, we would want all records from the table on the right, and it would become a RIGHT OUTER JOIN. When you declare a LEFT or RIGHT OUTER JOIN, you skip the table declaration in the FROM (as you've already found out) and use it in the declaration for the LEFT JOIN itself. You then use the keyword ON to match the columns up. In a simple INNER JOIN, the ON would be something like using the WHERE clause. Furthermore, if we have two columns from different tables that have the same name, you can use the USING keyword instead, like Code: SELECT Category.CatID, Category.CatName, count(ArtCat.ArticleID) FROM Category LEFT OUTER JOIN ArtCat USING (CatID) GROUP BY Category.CatID Taking it one step further and cleaning the resultset up a bit, I'll use an ALIAS on the tables and on the COUNT so we have a little nicer formatting in the end, e.g. Code: SELECT c.CatID, c.CatName, COUNT(ac.ArticleID) AS ArticleCount FROM Category AS c LEFT OUTER JOIN ArtCat AS ac USING (CatID) GROUP BY c.CatID So now, rather than having a result like COUNT(ArtCat.ArticleID) you can simply refer to it as ArticleCount (or whatever word strikes you as making sense). Strictly speaking, the OUTER keyword isn't needed, you can use simply LEFT or RIGHT join, but I find in circles where SQL is spoken most often they do use the full syntax in statements. I suppose it leaves very little chance for ambiguity in the statement. Anyway, hope that clears things up a bit.