Best way to get list of element combinations from two mysql tables

Question

I have two tables:

1. Table "products"

ID	Name	Price	Quantity
1	Product A	10	10
2	Product B	20	10

2. Table "promotions"

ID	Product ID	Type	Price	Quantity
1	1	discount	5	3
2	1	discount	8	2
3	1	outlet	10	3
4	2	outlet	10	0

From tables above I can see that:

1. I have 10 pieces of Product A with regular price 10$
2. I have 3 pieces of Product A with discounted price 5$
3. I have 2 pieces of Product A with discounted price 8$
4. I have 3 pieces of Product A with outlet price 3$
5. I have 10 pieces of Product B with regular price 20$
6. I have 0 pieces of Product B with outlet price 3$.

What is the most efficient way to get all combinations of available products?

My solution was with three UNION SQL querys, for example:

SELECT * FROM (
  (<<all products in reular prices>>)
  UNION ALL
  (<<all products in discount prices>>)
  UNION ALL
  (<<all products in outlet prices>>)
) as tmp

Everything works fine, I am just wondering if this is the best way or is it better to use LEFT or RIGHT JOIN. Of course this is just an example, my tables have 20.000+ lines so I am looking for fastest way to list results.

Answer 1

One option could be:

• first apply a UNION between Products and Promotions on matching columns
• then JOIN back with Products to retrieve the products name

Following the query:

SELECT prices.ProductID,
       Products.Name,
       prices.Type,
       prices.Price,
       prices.Quantity
FROM (SELECT ID        AS ProductID,
             'regular' AS Type,
             Price,
             Quantity
      FROM Products
      UNION
      SELECT ProductID,
             Type,
             Price,
             Quantity
      FROM Promotions                 ) prices
INNER JOIN Products 
        ON prices.ProductID = Products.ID

Try it out here.