mysqlsqlwhere-clauseor-operatorand-operator

MySQL How to use and OR inside an AND operator for a where clause?

I'm trying to use AND with OR's inside my WHERE clause but I'm not getting expected results. This is my query: 

select * 
  from Pitches 
 where BatterID = @playerID 
   and YEAR(GameDate) in (2019) 
   and (BattedBallID is not null or BattedBallID <> 0);

The problem is I'm getting BattedBallID's that have 0 in the rows but none of them have null. I want it so my results have BattedBallID's that aren't 0 and aren't null;

Solution

  • I want it so my results have BattedBallID's that aren't 0 and aren't null;

    You want:

    and BattedBallID is not null and BattedBallID <> 0;

    Instead of:

    and (BattedBallID is not null or BattedBallID <> 0);

    Your original expression will actually always evaluate as true, since both conditions cannot be false at the same time : if something is null, then it is not equal to 0, and if something is equal to 0, then it is not null.