user75ponic user75ponic - 25 days ago 7
SQL Question

NVL Column and NULL

I have rows as shown below

ProductId ProductName ProductDesc ProductLoc
101 Camel Pencil B-10
102 Parker Pen
103 Mirado Pen C-10


When I execute the following SQL query

SELECT *
FROM tablename
WHERE productloc = NVL ('', productloc)


It gives me the 1st and 3rd row, what I would like to achieve is if productloc is null in where condition of the SQL, then I should get all three rows.

How can I get the desired output.

Answer

select * from tablename;

Is what you need in that case because your where gives no effect. You compare column with itself.

If you want to filter and include nulls you can do (but probably replace one productloc with some value:

select * from tablename where productloc = productloc or productloc is null;

Or:

select * from tablename where nvl(productloc, 'SOME_UNIQUE_VAL') = nvl(productloc, 'SOME_UNIQUE_VAL');

and also replace one of productloc by some value.