Is it more performant to use a JOIN or a when using LIMIT 1? I have a products table. I want to select all fields (of a given row) and the most profitable discount that the user can claim (depends on ordered quantity of the product).

When selecting only one value only, is it more performant to use a JOIN, like this:

SELECT p.*, qd.price AS qd_price

FROM products AS p

-- Find all applicable QDs
LEFT OUTER JOIN quantity_discounts as qd
    ON p.id=qd.product_id
    AND qd.min_amount &lt;= ?
    AND (
        (qd.user_id IS NULL OR qd.user_id = 0 OR qd.user_id = ?)
        OR (qd.group_id IS NULL OR qd.group_id = 0 OR qd.group_id = ?)
    )

WHERE p.id=?

-- Get the lowest QD price
ORDER BY qd.price ASC
LIMIT 1


or a subquery, like this:

SELECT p.*,
(SELECT price FROM quantity_discounts
WHERE product_id=p.id
AND (
    (user_id=? or user_id=0 or user_id IS NULL)
    OR (group_id=? or group_id=0 or group_id IS NULL)
)
AND qd.min_amount &lt;= ?
ORDER BY price LIMIT 1) AS qd_price
FROM products as p
WHERE p.id=?
The ORs are deadly for performance.  For starters, decide on 0 or NULL so you won't have to test for both.  (That won't actually help any since there would still be other ORs.)

I'm dubious about the formulation of the LEFT JOIN.  (BTW, OUTER is optional.)  Normally when using LEFT, the ON only says how the tables are related (p.id=qd.product_id); the WHERE provides the filtering.

I don't know which is better, but since you seem to be limiting to one id (via WHERE p.id=?), then the second formulation will look into quantity_discounts only once, and (hopefully) won't have too many rows to check.  That is, the OR may not be as bad as I started out with.

A slight help on quantity_discounts will be

INDEX(product_id, price)  -- in this order


Now I am worried.  One formulation has AND qd.min_amount &lt;= ?, the other does not.