Search code examples
sqloracle-databasenull

How to return NULL value from Oracle if no record is present

How can I return NULL or blank value even if that record doesn't exist in Oracle table?

Suppose I have a table with following data:

Id  Name  
1   somename1  
3   somenm4

If I run the following statement then it doesn't return any record because that Id doesn't exist.

SELECT * FROM MyTable WHERE Id = 2

Is it possible to return some NULL value even if query doesn't return any records something like:

Id  Name  
2   NULL
Solution

  • One "dirty" trick you can use is to left join on dual. Instead of providing the id in the where clause, query it as a literal from dual, and left join on it. If the id exists in mytable, you'll get the actual name, and if not, you'll get a null thanks to the left join:

    SELECT    d.id, m.name
FROM      (SELECT 2 AS id FROM dual) d
LEFT JOIN mytable m ON m.id = d.id