Is there a difference in these two querys?

Question

Is there any difference in these two querys? What is the difference of using parentheses in and?

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;

Answer 1

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

The AND it’s like a multiplication and the OR it’s like an addition, so no parentheses that change this precedence, which is next to the AND is used primarily to the detriment of what is close to the OR.

So read the codes like this to better understand:

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

Like the AND has natural precedence defined by the language the parentheses are not required in the first, but mandatory in the second to give the intended 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 unintentionally. Some say it should do 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, that our friend tried but became very confused, so they must have negativated:

1. All that is of the GROUP of "drinks" And at the same time has below PRICE 10. But if this condition is not met, you can take a product that has a stock equal to or less than 100, so if something has stock below this limit counts as a bag to be considered in the selection, it doesn’t matter which group it belongs to or what the price is. Which seems pretty incoherent to me and seems like a mistake.

2. Anything from the "drinks" group at least one of the following two conditions: have PRICE less than 10 OR have stock equal to or less than 100. In this case being in the group "drink" happens to be mandatory for everyone. What also seems wrong, but who am I to talk without knowing what the problem is. It looks like everything should be valid and then there should be 2 AND, which would dispense parentheses, although it can put to make more readable, although in this case change little.

Another question of readability is that it became barely visible where each clause is, it would be better to put the WHERE in a single line or indent:

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

I put in the Github for future reference.

Answer 2

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

Answer 3

I have seen many Selects and Ifs within the programming, and I confess that I keep thinking about how one does to keep the code cohesive... of course a little attention helps, but the use of "()" will not fall your hand and in my point of view leaves the programming much more organized.

Example without ():

SELECT * FROM TABELA WHERE CAMPO_A >= VALOR AND CAMPO_B <= VALOR AND STATUS = 'A'

With ()

SELECT * FROM TABELA WHERE (CAMPO_A >= VALOR) AND (CAMPO_B <= VALOR) AND (STATUS = 'A')

Now see an example where you might get an error by missing ()

SELECT * FROM TABELA A
WHERE CAMPO <= B.VALOR * C.VALOR - D.VALOR
INNER JOIN TABELA_B B ON(B.CODIGO = A.CODIGO)
INNER JOIN TABELA_C C ON(C.CODIGO = A.CODIGO)
INNER JOIN TABELA_D D ON(D.CODIGO = A.CODIGO)

We know that multiplication will be performed first, but what if you wanted subtraction first?

So you don’t have to rewrite all the code just use ()

SELECT * FROM TABELA A
WHERE CAMPO <= B.VALOR * (C.VALOR - D.VALOR)
INNER JOIN TABELA_B B ON(B.CODIGO = A.CODIGO)
INNER JOIN TABELA_C C ON(C.CODIGO = A.CODIGO)
INNER JOIN TABELA_D D ON(D.CODIGO = A.CODIGO)

The use of () is not only a matter of visualization, it is very important and should be taken into account

2 * 1 - 1 

Is different from

2 * (1 - 1) 

Answer 4

Well talking about from, there may be a difference if Case-sensitive (is an anglicism that refers to a type of typographic analysis of computer science. In Portuguese, it means something like "letter box sensitive" or "case sensitive") is enabled in DB (Database). Otherwise the two will have the same result. On the Where may have differences in the final result because the first vc is placing the conditional as "group = 'drinks' and price < 10" being true or "stock <= 100" truth, for the second vc this putting the conditional as "group = 'drinks'" truth and "price < 10" truth or "stock <= 100".

:)