2

I"m trying to write a query in Oracle SQL to return rows if the value in field 1 does not equal the value in field 2 (where field2 includes some null values)

If the table looks like this

Field1    Field2
1         1
2         3
4

If I use this query:

select * from table where field1 != field2

I only get back row 2, but not row 3. Is there a single query I can use to get both rows 2,3 retreived?

Jonjilla
  • 433
  • 6
  • 17
  • 1
    Orace uses three valued logic - http://www.oracle.com/technetwork/issue-archive/2005/05-jul/o45sql-097727.html – Error_2646 Sep 12 '17 at 20:44
  • Do you want to include or exclude rows where both fields are null? –  Sep 12 '17 at 21:02
  • @mathguy exclude when they are both null, since they are effectively equivalent ( I know they are both null so we can't say that for sure but that is good enough) – Jonjilla Sep 12 '17 at 21:04

5 Answers5

5

Also include if field2 is NULL. You know the null isn't a match but want to show it.

SELECT * FROM table 
WHERE field1 != field2
    OR (field1 IS NULL AND field2 IS NOT NULL)
    OR (field1 IS NOT NULL AND field2 IS NULL)

Additionally, you can use COALESCE to assert the null as another value. I used 0 in this case. Only use 0 if there is no 0 for field1 or field2. Basically choose a value that won't happen in your table.

select * from table where COALESCE(field1, 0) != COALESCE(field2, 0)

Edit: OP asserted that field1 could be null, so I changed the first query. Query 1 is a bit more clear on what's going on, and Query 2 is a more concise way to achieve the same end result.

Read more here: COALESCE Function in TSQL

You can also use NVL() to deal with NULL like COALESCE, however it is less efficient to my understanding. More info: Oracle Differences between NVL and Coalesce

justiceorjustus
  • 2,017
  • 1
  • 19
  • 42
  • Thanks, I get that, but I'm wondering why this is necessary? Isn't it unambiguously true that field1 doesn't = field2 if one of them is null? – Jonjilla Sep 12 '17 at 20:44
  • It's nitpicky, but this doesn't handle the case when they are both NULL, or where field1 is NULL and field2 is not null. The COALESCE edit does though. – Error_2646 Sep 12 '17 at 20:46
  • 1
    @Jonjilla I'm sure someone can answer this better... but `NULL` just not a value that can be compared. It's literally the absence of a value. It's comparing something that exists vs something that is non-existent. Not comparing two things that exist. – justiceorjustus Sep 12 '17 at 20:48
  • The OP explained that a row should also be included if field1 is NULL but field2 is not, and a row should **not** be included if both fields are NULL. This solution includes a row if both fields are NULL, but excludes a row where field1 is NULL but field2 is not NULL. So, this does **not** solve the OP's problem. –  Sep 12 '17 at 23:05
  • @mathguy OP infers `field2` is the only field that contains `null`, so it essentially performs the same with or without `COALESCE` on `field1`. He also states "if the value in field 1 does not equal the value in field 2." – justiceorjustus Sep 12 '17 at 23:41
  • I asked the OP if he wants to show rows where **both** fields are NULL. He/she said "no, they should be considered equivalent so they shouldn't be included". I take it from this answer that in fact there may be NULL in field1 also. –  Sep 12 '17 at 23:49
  • @mathguy If both are `null` then `0 != 0` will not show. – justiceorjustus Sep 12 '17 at 23:52
  • Right; I meant my comment for the first solution only. –  Sep 12 '17 at 23:55
  • @mathguy Oh, yes, you're 100% right. I will edit when I can get to my computer or you can if you wish to. :D – justiceorjustus Sep 12 '17 at 23:58
1

If you want to exclude rows where both fields are null (as you indicated in a Comment), then you can use

select *
from   <table>
where  decode(field1, field2, 1, 0) = 0
;

This takes advantage of the peculiar definition of decode. It returns the third argument (1 in this example) if field1 = field2 and ALSO when they are both null (contrary to the "proper" treatment of null in SQL). In all other cases it returns 0, which is what you want.

0
select * 
  from table  
 where field1 != field2
    or (field1 IS NOT NULL AND field2 IS NULL)
    or (field1 IS NULL AND field2 IS NOT NULL)
Error_2646
  • 2,555
  • 1
  • 10
  • 22
0

Hi some correct answers here already but here is another one you may like better:

select * from table where field1 != nvl(field2,-field1);

Let us know if it works ok for you. For any further clarification don't hesitate to ask me again.

Ted

Ted at ORCL.Pro
  • 1,602
  • 1
  • 7
  • 10
  • This solution is incorrect (considering the OP's definition of "not equal"). Indeed, if field1 is NULL but field2 is **not** NULL, then the row will not be included; however, the OP wanted it included. –  Sep 12 '17 at 23:03
0

You can use case statement to test equality in the reqiured sence. This expression never evaluates to null, so it is safe to apply not to over it and get the complementary rows.

select * from my_table
where 
not case 
  when field1=field2 then 1
  when field1 is null and field2 is null then 1
  else 0
end = 1;
JiSkr
  • 1
  • 1