CODEWITHSUNDEEP
sql
MikeMB35
MikeMB35

Reputation: 49

Excluding certain numeric values from all columns in queries

I have a table that uses numerical values -9999 and 9999 to indicate missing values. I need to exclude all the records that feature either one of those values in my queries using the WHERE clause. This is what I have tried:

SELECT *
FROM weather
WHERE (* != -9999 & * != 9999);

I have heard you can use except instead but my instructor wants us to practice using where. Any help would be greatly appreciated, thank you.

Upvotes: 0

Views: 471

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I think the canonical solution would be NOT IN:

where col not in (-9999, 9999)

Unfortunately, you would have to do this for all columns . . . although I could imagine some tricks in some databases using, say, JSON functionality.

Upvotes: 1

Rizzle Sizzle
Rizzle Sizzle

Reputation: 11

SELECT *
FROM weather
WHERE table_column != -9999
AND table_column != 9999;

Change table_column for the right table column, the one that holds the values.
You can also use :
...
where table_column not in (9999,-9999)

Upvotes: 0

Related Questions