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