CODEWITHSUNDEEP
sqlsql-serverselectcaseunion
ReDEyeS
ReDEyeS

Reputation: 851

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

Upvotes: 0

Views: 79

Answers (4)

sqlint
sqlint

Reputation: 1089

I think this one. INNER JOIN is faster than UNION and nested queries.

Here's a Demo on SqlFiddle.

SELECT TOP 10 comment_product, comment_date, 
case when product_type = 'A' then a.product_name 
when product_type = 'B' then b.product_name 
when product_type = 'C' then c.product_name 
else '' end
FROM comments INNER JOIN productsA a ON product_id = a.id_product  
INNER JOIN productsB b ON product_id = b.id_product   
INNER JOIN productsC c ON product_id = c.id_product   
ORDER BY comment_date DESC

Upvotes: 1

Jacek Glen
Jacek Glen

Reputation: 1546

As much as I don't like it, it seems that using CASE will be faster. Any SELECT TOP N will result in N sub-queries. If you have index on id_product in all 3 product tables that should be fast enough.

UNION solution will trigger 3 full queries, union, sort and then top.

Upvotes: 0

Hart CO
Hart CO

Reputation: 34784

I'd suggest you need neither UNION or CASE and can just JOIN multiple times to comments:

SELECT TOP 10 
            comment_product
          , COALESCE(a.product_name,b.product_name,c.product_name) AS product_name
          , comment_date 
FROM comments z
LEFT JOIN productsA a
    ON z.product_id = a.id_product  AND z.product_type = 'A' 
LEFT JOIN productsB b
    ON z.product_id = b.id_product  AND z.product_type = 'B' 
LEFT JOIN productsC c
    ON z.product_id = c.id_product  AND z.product_type = 'C' 
WHERE COALESCE(a.id_product,b.id_product,c.id_product) IS NOT NULL
ORDER BY z.comment_dateDESCC

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425491

The second query would most probably use an index scan on comment_date with nested loops over product tables, i. e. at most 10 logical seeks plus whatever it takes to read 10 records from comments

This first query would most probably use an index scan and sort over each of the queries, then a MERGE UNION of their results.

If you have indexes on comment_date and id_product in all product tables, the second query would be much faster.

Upvotes: 1

Related Questions