Columns with Null Values in SQL Server vs Oracle

Question

In Oracle: ='' and 'is null' return NULL Values

 SELECT COL1 FROM TABLE WHERE COL1='' --> RETURN COL1 WITH NULL VALUES
 SELECT COL1 FROM TABLE WHERE COL1 IS NULL --> RETURN COL1 WITH NULL VALUES

Both queries return the same set of rows.

In Microsoft SQL:

SELECT COL1 FROM TABLE WHERE COL1='' --> RETURN COL1 WITH <BLANK> VALUES
SELECT COL1 FROM TABLE WHERE COL1 IS NULL --> RETURN COL1 WITH <NULL> VALUES

Here the first and second queries return different sets of rows.

Why is there a difference between the Microsoft SQL result set and the Oracle result set?

How could I retrieve both set of rows in Microsoft SQL? (Values which are '' and values which are NULL).

Because `''(empty string)` is **NOT** equal to `NULL`. Not sure about `Oracle` — Pரதீப், Dec 23 '16 at 14:50
In `Oracle`, an empty string is automatically converted to null. — Josh B, Dec 23 '16 at 14:57

score 1 · Accepted Answer · edited May 23 '17 at 11:53

1

In SQL Server '' is not null. In Oracle its a bit complicated x char := '' is not null in PL/SQL but as varchar2 empty string '' is exactly same as null as explained here And in case of queries this is varchar2 so condition COL = '' is never true.

If you need condition that will work in both DBs please use coalesce function.

select * from TAB 
  where coalesce(COL,'SOME_UNIQUE_STRING') = coalesce(FILTER_PARAM, 'SOME_UNIQUE_STRING')

Such condition should return rows when COLUMN is equal to FILTER as well as both are null or empty strings.

edited May 23 '17 at 11:53

Community

1
1

answered Dec 23 '16 at 18:26

Kacper

4,798
2
19
34

Thanks Kacper for the info. – Veera V Dec 25 '16 at 16:28

score 0 · Answer 2 · edited May 23 '17 at 12:09

0

To retrieve both sets of data, you could use isnull.

SELECT COL1 FROM TABLE WHERE ISNULL(COL1, '') = ''

Oracle automatically converts empty strings to null values, and sql server does not, which is why the result sets are different.

You can find more information on this here: Why does Oracle 9i treat an empty string as NULL?

edited May 23 '17 at 12:09

Community

1
1

answered Dec 23 '16 at 14:50

Josh B

324
1
3
20

There is no `isnull` in Oracle. http://stackoverflow.com/questions/3523036/what-is-the-oracle-equivalent-of-sql-servers-isnull-function – Kacper Dec 23 '16 at 18:23
Thanks much for the link. I could now understand the differences. – Veera V Dec 25 '16 at 16:27

score 0 · Answer 3 · answered Dec 23 '16 at 18:43

To avoid defining a unique string, you could use a case statement.

This way we evaluate col when null or empty set to the same value for comparison. Since case to my knowledge is DBMS independent this should work in both.

SELECT *
FROM Table
WHERE case when col is NULL or col = '' then 1 else 0 end = 1

Columns with Null Values in SQL Server vs Oracle

3 Answers3