Is there any difference in these two queries?

2

Is there any difference in these two querys ? What is the difference between using% in parentheses%?

SELECT *
FROM produto
WHERE grupo = 'bebidas'
AND preco < 10 OR estoque <= 100
ORDER BY estoque DESC;

SELECT *
FROM PRODUTO
WHERE grupo = 'bebidas'
AND (preco < 10 OR estoque <= 100)
ORDER BY estoque DESC;
    
asked by anonymous 24.06.2018 / 19:17

3 answers

2

It is a matter of operator precedence and associativity. When you use an expression composed of AND and OR , that is, relational operators, which are those that evaluate " binary "and generate new ones according to Boolean algebra (see truth table and Karnaugh's map ).

The AND is like a multiplication and the OR is like an addition, so without any parentheses that change this precedence, what is close to AND is used primarily to the detriment of what is next of OR .

Then read the codes like this to understand better:

(grupo = 'bebidas' AND preco < 10) OR estoque <= 100
grupo = 'bebidas' AND (preco < 10 OR estoque <= 100)

As AND has natural precedence defined by the language, the parentheses are not necessary in the first, but mandatory in the second to give the desired semantics in it.

In fact there are those who say, and I agree, that for these operators should always use the parentheses to make clear and explicit what the intention is and avoid mistakes inadvertently. There are those who say they should do it for all operators, but this would be exaggerated, some no less intuitive than others for a "normal" human.

To try to make a reading of what each expression is, which our friend tried but was very confused, so should have denied:

1) Everything in the "drinks" group and at the same time have a price lower than 10. But if this condition is not met, you can pick up a product that has a stock of 100 or less, so if something has stock under that limit counts as an alfo to be considered in the selection, no matter what group it belongs to or what the price is. Which seems pretty incoherent to me and seems like a mistake.

2) Everything in the "drinks" GROUP E meets at least one of the following two conditions: have a PRICE less than 10 OR have a stock equal to or less than 100. in this case of the "drink" group becomes mandatory for all. Which also seems wrong, but who am I to speak without knowing what the problem is. It has a face that should be everything valid and there should be 2 AND , which would dispense parentheses, although it can put to make it more readable, although in this case little change.

Another legibility issue is that it was barely visible where each clause is, it would be best to put WHERE on a single line or indent:

SELECT *
FROM PRODUTO
WHERE grupo = 'bebidas' AND
    (preco < 10 OR estoque <= 100)
ORDER BY estoque DESC;
    
24.06.2018 / 21:39
0

A lot of difference. When we put a parenthesis, everything inside it will be considered as a result. In your example, the first select has THREE INDIVIDUAL conditions: it will bring all the records that belong to the 'drinks' group, the price is less than 10 OR (attention to 'or') less than / 100 stock. If there is a product with group 'foods' and stock equal to 9, it will appear because the last condition has been satisfied. The second select has TWO conditions: belong to the group 'drinks' and have one of the characteristics mentioned in the parenthesis, since the 'or' now belongs to the internal expression. The first condition is not negotiable. Only products belonging to the 'drinks' group will be listed if they have a price or stock according to the filter. That product from the 'food' group would not appear in the second select.

    
26.06.2018 / 19:24
-1

Well speaking of from, it may be different if Case-sensitive (an Anglicism that refers to a type of typographic analysis of information technology.) In Portuguese, it means something like "sensitive to the letter box" or "sensitive the case ") is enabled in the DB (Database). Otherwise the two will have the same result. On the where you can have differences in the final result because the first one you are putting the conditional as "group = 'drinks' AND price < 10" being true or "stock < = 100" true, for the second you are placing the conditional as "group = 'drinks'" truth and "price < 10" truth or "stock

24.06.2018 / 19:38