SQL Select Statement with WHERE, AND, OR

Question

Hi guys, I would like to perform a SELECT query with MySQL. My goal is to select all the dogs in a vet database that would be sex=male and fur=short and (color=black or size=big)

Note: I want to select dogs that are either black or size are big. They don't have to fulfill the 2 requirements. They just need to fulfill either one.

I have written the SQL statement below but I'm not not sure if I'm right:

SELECT name, sex, fur, color FROM dogs WHERE TRUE sex='male' AND fur='short' AND color='black' OR size="big";

Pardon my phrasing if it's too confusing.

Answer 1

The way you use parentheses in the description of your goal is correct. The syntax is:

SELECT name, sex, fur, color 
    FROM dogs 
    WHERE sex="male" AND fur="short" AND (color="black" OR size="big");

Answer 2

According to Operator precedence for MySQL AND has higher precedence than OR.

So C1 AND C2 OR C3 will be treated as (C1 AND C2) OR C3

To override the default precedence you need to use parenthesis as:C1 AND (C2 OR C3)

In your case the right query is:

SELECT name, sex, fur, color 
FROM dogs 
WHERE sex='male' AND fur='short' AND (color='black' OR size="big");

Answer 3

Make sure to add parentheses, so the OR condition gets evaluated correctly.

SELECT name, sex, fur, color 
FROM dogs 
WHERE sex='male' AND fur='short' AND (color='black' OR size='big');