this is the table im working with
–Write a query to display the name of those students that have the letters "ae" or "ph" in their name and are NOT 19 years old.
So this is my solution:
SELECT *
FROM students
WHERE (student_name LIKE ‘%ae%’ OR student_Name LIKE ‘%ph%’ AND age != 19)
and this is the actual correct solution I got from my class notes:
SELECT *
FROM students
WHERE (student_name like ‘%ae%’ OR student_name like ‘%ph%’)
AND age != 19;
The correct resulting output shows no records
I dont understand how my query returns 2 records where the age is 19, however in the correct solution I got from my class notes it returns properly and there are no ages of 19 years old. The OR caluse is working correctly it seems, but for some reason my AND condition is not being applied against the records.
Any help would be greatly appreciated
>Solution :
This is a lesson in operator precedence.
You may be familiar with the idea that in standard arithmetic "1 + 2 × 3" gives 7, not 9, because the "2 × 3" is calculated first. If we add parentheses, "(1 + 2) × 3" gives 9, because "(1 + 2)" is calculated first.
The same applies to boolean arithmetic with "and" and "or" – the "and" is calculated first, unless we add parentheses.
So "(student_name LIKE ‘%ae%’ OR student_Name LIKE ‘%ph%’ AND age != 19)" will first group "student_Name LIKE ‘%ph%’ AND age != 19" into one condition; it will then match rows where either that is true, or where "student_name LIKE ‘%ae%’" is true – students with "ae" in their name can be any age.
When you add the parentheses, the "or" is calculated first, and it will match rows where both "(student_name LIKE ‘%ae%’ OR student_Name LIKE ‘%ph%’)" is true and "age != 19" is true – regardless of name, no 19-year-olds are allowed.
Since this is an easy mistake to make, I would advise always adding parentheses when mixing "and" and "or", to make the intent clear.