(SQL) In parentheses in the WHERE clause

Asked

Viewed 1,428 times

2

Hello, I didn’t find much information in research I did on... but I am currently having a problem which is as follows:

I have an appointment with many simple conditions, just sequences of and. But today I had to include some or, and lo and behold the query just can’t finish anymore, it runs for minutes until it stops for exceeding the memory limit of Postgressql or something like that. Behold, then I thought of separating these or inside a block, in this case, I put them in parentheses and then the query rotated very quickly and brought me a result without errors. So I decided to do a test with another query, this time it was much simpler and... when I put the parentheses, the query now would not bring me anything else, but if I removed them, it would perform as expected.

So I started asking myself, what was really going on and what is the right procedure to do and how the parentheses should be used in WHERE. A thank you already!

Example of the query I am using without parentheses:

nfe.id_empresa = 4 and 
nfe.ano = '2016' and
nfe.id_empresa = nfe_item.id_empresa and
nfe.ano = nfe_item.ano and
nfe.mes = nfe_item.mes and
nfe_item.cst_icms = '010' or 
nfe_item.cst_icms = '030' or
nfe_item.cst_icms = '060' or
nfe_item.cst_icms = '070' or
nfe_item.cst_icms = '110' ;

Now in parentheses:

nfe.id_empresa = 4 and 
nfe.ano = '2016' and
nfe.id_empresa = nfe_item.id_empresa and
nfe.ano = nfe_item.ano and
nfe.mes = nfe_item.mes and
(
    nfe_item.cst_icms = '010' or 
    nfe_item.cst_icms = '030' or
    nfe_item.cst_icms = '060' or
    nfe_item.cst_icms = '070' or
    nfe_item.cst_icms = '110' 
);
sql logic

  • The second query is correct, has a and and all options with or in parentheses (only one comment, could use in removing the or and simply for a row), now you have to see the number of records in the table (how many records does the table have? ). The field cst_icms must be varchar, I’m sure?

  • Without parentheses, if nfe_item.cst_icms = '110' for example, it will already return a value independent of any other and that you have placed. I believe your logic requires parentheses to be correct. You’re probably not bringing anything for another logic flaw.

  • Hello! Good then that I was doing correctly. Table posssui 109 thousand records, is a lot of stuff. and yes, it is a VARCHAR a cst_icms!

  • Try later to use IN to simplify the syntax and not need the parentheses: AND nfe_item.cst_icms IN ('010','030','060','070','110')

  • Very good tip, I tested here and I’m already using. Thank you!

3 answers

4


Just as multiplication and division take precedence/priority over addition and subtraction, the AND takes precedence/priority over OR.

I mean, this:

nfe.id_empresa = 4 and 
nfe.ano = '2016' and
nfe.id_empresa = nfe_item.id_empresa and
nfe.ano = nfe_item.ano and
nfe.mes = nfe_item.mes and
nfe_item.cst_icms = '010' or 
nfe_item.cst_icms = '030' or
nfe_item.cst_icms = '060' or
nfe_item.cst_icms = '070' or
nfe_item.cst_icms = '110' ;

Is equivalent to that:

(
    nfe.id_empresa = 4 and 
    nfe.ano = '2016' and
    nfe.id_empresa = nfe_item.id_empresa and
    nfe.ano = nfe_item.ano and
    nfe.mes = nfe_item.mes and
    nfe_item.cst_icms = '010'
) or 
nfe_item.cst_icms = '030' or
nfe_item.cst_icms = '060' or
nfe_item.cst_icms = '070' or
nfe_item.cst_icms = '110' ;

And that’s not what you want.

See more about precedence here.

In the form without parentheses, in cases where the cst_icms for '030', '060', '070' or '110', all records will be brought independent of other fields (id_empresa, ano, mes or anything from Tablea nfe). The result of this is a huge set of data, and so it takes a long time.

Already in the second form with the parentheses in ors, the clauses with and have already filtered a lot of results that would be wrong/unwanted before applying filtering by cst_icms.

  • Thank you! You enlightened me a lot.

  • Perfect Victor. @Rafaelaraujo, me made this example with simpler data and conditions to better fix the idea. In addition to everything that has already been said in the answers, even though the precedences are observed when using combinations of OR and AND, I would recommend that the parentheses at all times included: Avoids this problem and becomes much clearer for someone else to interpret the logic of the code.

  • I appreciate the advice, it is really very good to get a code written in an easy way to interpret. Thanks!

1

The parentheses causes Postgresql to treat all grouped conditions as one thing, for example, in its first query, as the OR are outside the parentheses, so if any cst_icms is equal to '030', '060', '070' or '110', will return these lines ignoring their other conditions, because one of their OR was met.

Now in the second query, where you left your OR conditions grouped between parentheses, for a row to be returned you need to meet all the previous conditions And one of the conditions grouped by OR.

I hope I’ve been clear so far.

Now in the matter of spending BD query time, what may be happening is that as in your first query, without parentheses, you are returning many results, enough so your bank can not display all. Put a LIMIT 100, for example, in your first query and you will see that it does have results, the problem is that they are many.

1

It’s just a matter of precedence of operators where "An operator at higher levels is evaluated before an operator at a lower level".

In other words, AND is processed before OR. 

true and false and true or true or false
[   false    ]
[          false      ] 
[               true          ]
[                   true               ] = true

Changing precedence using parentheses: 

true and false and (true or (true or false))
                             [    true    ]
                    [      true           ] 
         [            false               ]
[                   false                 ] = false

Browser other questions tagged sql logic

You are not signed in. Login or sign up in order to post.