Why is this SQL statement incorrect?

9
SELECT * FROM produtos WHERE categoria = 'Vestidos' AND cor = 'Branco' OR cor = 'Rosa' 

Returned 3 records, 2 of them in the Dresses category and 1 in the Shoes category. Why did not you just return from the Dresses category since I instructed categoria = 'Vestidos' ?

    
asked by anonymous 24.04.2015 / 19:29

4 answers

13

AND takes precedence over OR . So your criteria is understood like this:

(categoria = 'Vestidos' AND cor = 'Branco') OR cor = 'Rosa' 

You need to place the parentheses in the appropriate place to achieve the desired result.

    
24.04.2015 / 19:34
6

As an alternative to the other answers, you can also use the comparison operator IN to check if the desired value is within a set of values, eg:

SELECT * 
FROM produtos 
WHERE categoria = 'Vestido' AND cor IN ('branco','rosa');
    
24.04.2015 / 19:42
5
SELECT * FROM produtos WHERE categoria = 'Vestidos' AND (cor = 'Branco' OR cor = 'Rosa') 
    
24.04.2015 / 19:33
5

Understand your query as the following (explaining bar talk mode): You want to know if there are products of the 'Dresses' category and the 'White' color and if it is not that (OR) that is of the color 'Rosa', that is, it forgets the previous two conditions and starts to interpret only the last one, or if there are results for both conditions it returns.

So if the condition color = 'Rose' is part of the category category = 'Dresses', your query should associate the 'ORs' with parentheses, being:

SELECT * FROM produtos WHERE categoria = 'Vestidos' AND (cor = 'Branco' OR cor = 'Rosa')

The right way

    
24.04.2015 / 19:33