Choosing between a union and a boolean condition

Question

Does this require a UNION?

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001,1002);

Or is it the same if you do it this way?

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
OR vend_id IN (1001,1002);

Answer 1

Yeah, in this case it's the same.

Answer 2

Not quite the same if you have a vend_id of 1001 with a price less than 5 then you will get two rows from the first query and one only one row from the second query.

Generally speaking the second form is better as it is both easier to read and probably faster to execute.

Answer 3

The database would generate a different execution plan for both queries. Without an actual performance problem, I'd go with the simpler query. Replacing or with union makes a simple query moderately complex. But it would make a complex query impossibly hard to understand.

There are also slight differences between both queries. The database will filter out duplicates for the second query (you're using union, not union all.) This can have surprising effects if you filter on columns you're not selecting.

Answer 4

Both are same in your example

But the second method would be faster as UNION will sort the result set that may be slow