Is There Better Oracle Operator to Do Null-Safe Equality Check

Comparing against null in a tuple in Oracle 11g

The simplest method might be to insert a new value for NULL. Something like this:

SELECT COUNT(*)
FROM MSG_TYP_LKUP
WHERE ((MSG_TYP, NM, COALESCE(SUB_TYP, '<NULL>'), DIRECT, FORMAT) IN
            (
                    ('setr.005','Cancel to Fund', '<NULL>','OUT','SWIFT-XML'),
                    ('setr.011','Cancel to Fund', '<NULL>','OUT','SWIFT-XML'),
                    ('setr.013','Order to Fund', '<NULL>','OUT','SWIFT-XML'),
                    ('setr.014','Cancel to Fund', '<NULL>','OUT','SWIFT-XML'),
                    ('setr.016','Order Received','RECE','OUT','SWIFT-XML'),
                    ('setr.016','Order Acknowledgement','STNP','OUT','SWIFT-XML')
            )
     );

I'm not thrilled with this approach. But Oracle doesn't have a NULL-safe comparison operator so this is probably the simplest method.

Oracle Equality that Includes Null

You can use coalesce in your join:

SELECT t1.column, t2.column 
FROM TABLE t1
    LEFT JOIN TABLE2 t2
        ON coalesce(t1.foo,0) = coalesce(t2.foo,0)

The second value of the coalesce depends on your datatype. Choose a value which won't already exist in the database.

SQL not equals & null

In Oracle, there is no difference between an empty string and NULL.

That is blatant disregard for the SQL standard, but there you go ...

In addition to that, you cannot compare against NULL (or not NULL) with the "normal" operators: "col1 = null" will not work, "col1 = '' " will not work, "col1 != null" will not work, you have to use "is null".

So, no, you cannot make this work any other way then "col 1 is null" or some variation on that (such as using nvl).

Not equal != operator on NULL

<> is Standard SQL-92; != is its equivalent. Both evaluate for values, which NULL is not -- NULL is a placeholder to say there is the absence of a value.

Which is why you can only use IS NULL/IS NOT NULL as predicates for such situations.

This behavior is not specific to SQL Server. All standards-compliant SQL dialects work the same way.

Note: To compare if your value is not null, you use IS NOT NULL, while to compare with not null value, you use <> 'YOUR_VALUE'. I can't say if my value equals or not equals to NULL, but I can say if my value is NULL or NOT NULL. I can compare if my value is something other than NULL.

Oracle/PL SQL/SQL null comparison on where clause

Either produce different SQL depending on the contents of that parameter, or alter your SQL like this:

WHERE (column2 = variableY) OR (variableY IS NULL AND column2 IS NULL)

Checking whether any two of ten fields are different (not NULL or equal)

UNPIVOT the columns to rows and then GROUP BY your primary key and COUNT the DISTINCT values in the unpivoted columns to see if there is more than one unique value:

Oracle 11 Setup:

CREATE TABLE table_name ( id, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10 ) AS
  SELECT 1, 'A', 'A',  NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM DUAL UNION ALL
  SELECT 2, 'A', NULL, 'B',  NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM DUAL UNION ALL
  SELECT 3, 'A', NULL, 'A',  'A',  NULL, 'A',  'A',  'A',  'A',  'A'  FROM DUAL UNION ALL
  SELECT 4, 'A', NULL, 'A',  'A',  'B',  NULL, NULL, NULL, NULL, NULL FROM DUAL;

Query:

SELECT id
FROM   table_name
UNPIVOT ( value FOR name IN ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10 ) )
GROUP BY id
HAVING COUNT( DISTINCT value ) > 1

Output:

| ID |
| -: |
|  2 |
|  4 |

db<>fiddle here

Oracle:Difference between NULL and EMPTY string

This is one of the most annoying features of Oracle - not found in other DB products. You will have to put up with it, for all the other massive advantages of Oracle - and be prepared that the learning curve is not very quick.

To check for equality of nulls, the best approach is to write explicitly what you are doing, instead of using gimmicks. For example:

... where NAME = INPUT or (NAME IS NULL and INPUT IS NULL)

This will make it a lot easier for yourself, and for others after you, to debug, maintain, and modify the code, now and especially later. There are other solutions, too, but they may confuse others in the future; for example, this is something I wouldn't use (for several reasons):

... where NAME || 'z' = INPUT || 'z'

although it would obviously achieve the same result with less typing.

One more thing, in most cases you should NOT include in your results rows where you treat NULL as "equal" - the values are NULL for a reason, and in most cases if you make two NULL's equal, that is NOT the intended result.

How to make null equal null in oracle

How about:

SELECT  COUNT(1)
  FROM    TableA
WHERE 
  wrap_up_cd = val
  AND ((brn_brand_id = filter) OR (brn_brand_id IS NULL AND filter IS NULL))

I'm not an Oracle expert, but I'd expect that to work - basically make the query match if both the filter and the value are NULL.

How to write a null safe compare = in pure SQL?

Related question is Get null == null in SQL. But that answer requires the search value to be written 2 times (that is: 2 question marks in my PreparedStatement)!?

The second-ranked and subsequent answers give a method to do this without binding the search value twice:

SELECT * FROM ROUTERS 
WHERE coalesce(ROUTER_ADDRESS, '') = coalesce( ?, '');

Note that this requires a dummy value that can never be valid column value (that's "out of band"); I'm using the empty string. If you don't have any such value, you'll have to put up with binding the value twice:

SELECT * FROM ROUTERS 
WHERE ROUTER_ADDRESS = ? or (ROUTER_ADDRESS is null and ? is null);

---

Related Topics