It's something similar to a "conditional" cross join:

DECLARE @Products TABLE (ProdID int, ProdGrp char(1))

INSERT INTO @Products VALUES (11,'*')

INSERT INTO @Products VALUES (12,'*')

DECLARE @ProductGroups TABLE (ProdGrp char(1), ProdGrpDesc varchar(50))

INSERT INTO @ProductGroups VALUES ('A', 'Prod Group A')

INSERT INTO @ProductGroups VALUES ('B', 'Prod Group B')

INSERT INTO @ProductGroups VALUES ('C', 'Prod Group C')

SELECT P.ProdID, G.ProdGrp

FROM @Products AS P

INNER JOIN @ProductGroups AS G

ON P.ProdGrp = G.ProdGrp

OR P.ProdGrp = '*'

ORDER BY P.ProdID, G.ProdGrp