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).
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.
