Priority in SQL script (and, or and brackets)

Time:2021-6-13

On the third day of February, xinchou year, niuxinmao month, renxu day

I haven’t had more blog for many days. Why? Because my demand is online, and then it’s Bibi again, which makes me feel sad and shivering… Oh, my God

Let’s go back to today’s SQL modification:

The requirement is as follows: query the data of personnel types (“1003%”, “1004%”, “1006%”) in a date range (2020-03-01 to 2021-03-12).

1. My original wrong way of writing is:

When I query the data in 2015, and the query time is very slow

--Select m.no, m.calldate, m.persontype, the data queried by this script has nothing to do with time
      from amain m
     where m.callDate >= date '202-03-01'
       and m.callDate< date'2021-03-12'
       and m.personType LIKE '1003%' 
       or m.personType LIKE '1004%' 
       or m.personType LIKE '1006%' ;

–That is, when:

— condition1 and condition2  OR   condition3 

–Its operation is actually equivalent to: (condition1 and   condition2) or condition3     // First calculate and   Re operation or

–The order of operation is and > or

2. The correct writing method is as follows:

select m.no, m.callDate, m.personType
      from amain m
     where m.callDate >= date '202-03-01'
       and m.callDate< date'2021-03-12'
       and (m.personType LIKE '1003%' 
          or m.personType LIKE '1004%' 
          or m.personType LIKE '1006%' );--  After adding brackets, you can find out the correct data -- condition1 and  ( condition2    OR     Condition 3) -- the operation actually runs the or inside the brackets first, and then the and outside the brackets -- the operation order is () > and

Combining the above two cases, we can get: the order of operation is () > and > or

 

Recommended Today

Dandelion · Jerry technology weekly Vol.21 – technology weekly · React Hooks vs Vue 3 + Composition API

Dandelion · Jerry technology weekly Vol.21 Choose react or Vue, everyone will have their own answer in their heart. There are many reasons to pick the framework of heart water, but when we ask ourselves, can we really evaluate the difference between the two. Perhaps we should return to the original intention and look at […]