Oracle sql select where is null

LOC from dept,emp where emp. DNAME, dept. December 27, - pm UTC. I myself prefer to just code select deptno, dname, loc from dept where deptno in select emp. How does NULL 'work' in these cases? Robert, December 27, - pm UTC. How do we 'get by' returning it as the result set of a subquery. I would have thought that the outer query would have responded to the returned NULL as 'unknown' thus causing the entire query to return some 'unknown' result.

Why does NULL work here Thanks, Robert. I made short test and index was used in nvl version. The first version is better than the second, but "ability to use an index" is not among the reasons. Show 2 more comments. NVL will be your friend here. Dessma Dessma 2 2 silver badges 11 11 bronze badges. UsagiMiyamoto - Why? Basically, over large datasets an NVL will be slower. Is that the desired result? Probably not! Add a comment. I3rutt I3rutt 4 4 silver badges 18 18 bronze badges.

MT0 add changes — I3rutt. Choua Yang Choua Yang 1. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog. Podcast Making Agile work for data science. Stack Gives Back If we want to represent the value instead of the code we could write a query like this:.

The case statement has two flavors. The simple case and the searched case. In the simple case the expression is written only once and depending on the result of this expression one of the possible cases is being used. This can be helpful when you need to choose from a set of distinct values. In the searched case every case has its own expression. This can for instance be helpful when using ranges. The else clause is optional. NULL values are one of the most mind-boggling values in the Oracle database.

Find out more. Fortnightly newsletters help sharpen your skills and keep you ahead, with articles, ebooks and opinion to keep you informed. View all articles by Patrick Barel. FROM emp t. WHERE t. NVL expr1 , expr2. NVL2 expr1 , expr2 , expr3. LNNVL condition. FROM emp e.