Select statement returning data I don't want-CodePudding

Trying to exclude any rows that have m or missing, but they are still present.

SELECT temp, wind_chill, wind_spd_flag
FROM weather
WHERE temp<0 OR wind_chill<0
AND wind_spd_flag NOT 'M' OR 'Missing';

Pretty new to this so thanks for the help!

CodePudding user response：

ORIGINAL

SELECT temp, wind_chill, wind_spd_flag
FROM weather
WHERE temp<0 OR wind_chill<0
AND wind_spd_flag NOT 'M' OR 'Missing';

When using OR's in a where clause be sure to use ()'s around the "OR" conditions or the and's will not work right
NOT This or that... is a NOT IN or multiple ands

Consider 1:

SELECT temp, wind_chill, wind_spd_flag
FROM weather
WHERE (temp<0 OR wind_chill<0)
  AND wind_spd_flag NOT in ('M','Missing');

Consider 2: (<> or != depending on your envionrment)

SELECT temp, wind_chill, wind_spd_flag
FROM weather
WHERE (temp<0 OR wind_chill<0)
  AND wind_spd_flag <> 'M'
  AND wind_spd_Flag <> 'Missing';

Why?

Suppose we used the a valid syntax without the ()'s

WHERE temp<0 OR wind_chill<0
  AND wind_spd_flag <> 'M'
  AND wind_spd_Flag <> 'Missing';

We'd get all records with temp<0 and those with windchill <0 which also had to exclude windspeeds of M or Missing. It's math.. left to right no order of operations... the ()'s are needed to specify how you want the order executed.

So temps <0 with M or missing would still be present.

WHERE (temp<0 OR wind_chill<0)
  AND wind_spd_flag <> 'M'
  AND wind_spd_Flag <> 'Missing';

Now says, "first get the records that have a temp or windchill < 0" then exclude the missing and m from that result set; thereby eliminating all M and Missing wind speed flags.

TIP: ()'s matter when using ORs in combinations with ANDs