Thursday, 19 September 2013

which one have I to choose between "union" and "case"?

which one have I to choose between "union" and "case"?

I have a list of tables (i.e. productsA, productsB, productsN, ...) each
product in these tables may have a comment (stored in the comments table),
if I hed to select top 10 ordered comments wich of these is the best
solution to be adopted (in terms of performances and speed)?
using UNION:
http://www.sqlfiddle.com/#!3/bc382/1
select TOP 10 comment_product, product_name, comment_date FROM (
select comment_product, product_name, comment_date from comments inner
join productsA on product_id = id_product WHERE product_type = 'A'
UNION
select comment_product, product_name, comment_date from comments inner
join productsB on product_id = id_product WHERE product_type = 'B'
UNION
select comment_product, product_name, comment_date from comments inner
join productsC on product_id = id_product WHERE product_type = 'C'
) as temp ORDER BY comment_date DESC
using CASE:
http://www.sqlfiddle.com/#!3/bc382/2
select TOP 10 comment_product, comment_date,
CASE product_type
when 'A' then (select product_name from productsA as sub where
sub.id_product = com.product_id)
when 'B' then (select product_name from productsB as sub where
sub.id_product = com.product_id)
when 'C' then (select product_name from productsC as sub where
sub.id_product = com.product_id)
END
FROM comments as com
ORDER BY comment_date DESC

No comments:

Post a Comment