SQL query that flattens many-to-one relationships

Question

I have a database with these tables, where each mapping table represents a "many-to-one" relationship between the table on the right and the table on the left (click to enlarge):

Database Diagram

I am selecting a single item from product and then adding on all of the other attributes through joins. Here is my current SQL:

SELECT 
 p.id, 
 p.name, 
 p.price, 
 p.description,
 GROUP_CONCAT(DISTINCT c.name SEPARATOR ",") AS categories,
 GROUP_CONCAT(og.options SEPARATOR ";") AS options 
FROM 
 product p 
 LEFT JOIN product_category pc ON p.id = pc.product_id 
 LEFT JOIN category c ON c.id = pc.category_id 
 LEFT JOIN product_optiongroup pog ON p.id = pog.product_id 
 LEFT JOIN (
 SELECT 
 og.id AS id, 
 CONCAT(
 og.name, 
 ":", 
 GROUP_CONCAT(oi.value)
 ) AS options 
 FROM 
 optiongroup og 
 LEFT JOIN optiongroup_optionitem ogoi ON og.id = ogoi.optiongroup_id 
 LEFT JOIN optionitem oi ON oi.id = ogoi.optionitem_id
 GROUP BY og.id
 ) AS og ON og.id = pog.optiongroup_id 
WHERE 
 p.id = 1 -- This is the input

My desired output is something like:

id name price description categories options
-------------------------------------------------------------------------------------------
1 Big Hat 10.00 A good hat. Hats,Clothing Color:Black,White;Style:Baseball,Sun

My concern is, would this be a terrible pain to maintain? Are there simpler solutions? Also, does it need to be optimized & how? I don't have much experience with SQL. The output format isn't set in stone; I'm working solo.

• Phrancis: Is there a specific reason you are using LEFT JOIN so much? Do you expect products to not have references in those tables a lot? LEFT JOIN are quite expensive and should be used only when needed
• castletheperson: @Phrancis I don't expect it to happen a lot, but I do expect it to happen. In particular, there are products with no options. But there aren't any products with no categories.

Answer 1

Don't LEFT JOIN unless you really need to

In a comment I asked:

Is there a specific reason you are using LEFT JOIN so much? Do you expect products to not have references in those tables a lot? LEFT JOIN are quite expensive and should be used only when needed – Phrancis

To which you responded:

I don't expect it to happen a lot, but I do expect it to happen. In particular, there are products with no options. But there aren't any products with no categories. – 4castle

That being said, you probably ought to build into your script some checks for that. In plain English, something like this might make sense. You can use if/else blocks to make it work.

1. Check with only a left join on your options. This should cover a large amount of cases, based on your response.

2. Should that return no rows for an existing product, then try with more left joins to see if something else might be missing.

3. Should that still return no rows, or invalid data, throw an error.

---

Aliases

There are a few points from your code that I think can be improved. For instance FROM product p is not a good alias (consider the other aliases as well), as aliases should at least give a hint as to what they are referring to, and p really doesn't. FROM product AS prod would be better in this case.

Also notice the addition of AS keyword which is good practice. It's less of a big deal with table aliases, but it can cause issues in particular with column aliases if it is missing, so it's really best to just include it.

• castletheperson: Thanks! That's really helpful. I'll see what I can do about reducing the left joins. Would an inner join be better for the categories and such? Are they faster? Also, will that change the ordering of when it looks at WHERE p.id = 1? I would think it should be analyzed first to speed things up, and I guess I thought that was a feature of left join (that it happens last).
• Phrancis: Inner join is normally faster because it doesn't have to scan the whole index/table and return null if no match is found. As for ordering, you should not rely on implicit ordering and just specify the order in an ORDER BY clause, that is if the order matters. When no order by clause is specified it usually orders by the primary key value, from my experience, but your mileage may vary.
• Phrancis: It really depends how the SQL engine decides to optimize it. I think in many cases it will go to the "where" clause to find a value to search for first, but it varies and is somewhat unpredictable what the engine will decide to do when you run a query...
• Phrancis: That being said, usually if the value you are searching in your "where" (or "join") clause is referencing an indexed column, that will be really fast on that table, no matter what
• castletheperson: Thanks! (Sorry I deleted my comment thinking it was a stupid question, thanks for the help though)