Problems encountered in the execution order of AND and OR in SQL statements

question

I encountered a problem when writing database SQL yesterday. The root of the problem lies in the execution priority of the AND and OR keywords in SQL statements. Let's test this issue.

Scenario

1. There is a student table Student, the table fields include Id (user primary key), Name (user name), Grade (grade), Class (class), Sex (gender). as follows:

Table Structure

2. Import ten test data into the table as follows:

Table Data

3. Now you need to find out the female students in grade 1 who are female, or the female students in class 2 who are female. The SQL statement is as follows:
select * from student where sex='女' and grade=1 or class=2
However, the result of the SQL query does not meet the requirements. The execution results are as follows:

Execution Results

The execution result also found male students in class 2, which is obviously incorrect.

4. Modify the SQL statement and add brackets. as follows:

select * from student where sex='女' and (grade=1 or class=2)

The result of the SQL query meets the requirements

analyze

From the above scenario, the key to the problem lies in the execution order of AND and OR.
According to the data, the priority of relational operators from high to low is: NOT >AND >OR
If there is an OR condition after where, OR will automatically separate the left and right query conditions.
Just like the first statement in the above scenario, its query condition is divided into two parts (or):

1. sex = 'female' and grade = 1
2. class=2

This means finding out the female students in grade 1 and the students in class 2. Does not meet the requirements of the original query.
The solution is to use brackets to distinguish the order of execution <br /> As in the second statement in the above scenario, the query condition is divided into two parts (and):

1. sex = 'female' 
2. (grade=1 or class=2)