CODEWITHSUNDEEP
sqlwhere-clause
userag123
userag123

Reputation: 41

Where clause not giving right result

From the following table, I want to select all records where food_1 column differs from food_2 and/or city_1 differs from city_2 for a given id

here is the table food_city

id  food_1  city_1  food_2  city_2
1   cake    paris   cake    paris
2   pepsi   london  pepsi   london
3   NULL    delhi   NULL    delhi
4   chicken berlin  chicken berlin
5   NULL    NULL    NULL    NULL
6   wine    NULL    NULL    NULL
7   NULL    cologne NULL    tokyo
8   egg mumbai  egg mumbai

I am writing:

SELECT id
FROM food_city
WHERE food_1 <> food_2 OR city_1 <> city_2

I want the result to be:

id
6
7

But getting only id 7 and not id 6!

Upvotes: 0

Views: 50

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

Almost any comparison with NULL returns NULL, which is treated as false. The standard NULL-safe comparison is IS NOT DISTINCT FROM:

SELECT id
FROM food_city
WHERE food_1 IS NOT DISTINCT FROM food_2 OR
      city_1 IS NOT DISTINCT FROM city_2;

Not all databases support this syntax. Some have their own version; some don't support a NULL-safe comparison.

Alternatively, you can be more explicit:

SELECT id
FROM food_city
WHERE (food_1 <> food_2) OR
      (food_1 IS NULL AND food_2 IS NOT NULL) OR
      (food_1 IS NOT NULL AND food_2 IS NULL) OR
      (city_1 <> city_2) OR
      (city_1 IS NULL AND city_2 IS NOT NULL) OR
      (city_1 IS NOT NULL AND city_2 IS NULL);

This is often shorted using COALESCE(), if there is a value that you know is "safe":

SELECT id
FROM food_city
WHERE COALESCE(food_1, '') <> COALESCE(food_2, '') OR
      COALESCE(city_1, '') <> COALESCE(city_2, '');

Upvotes: 1

Related Questions