Oracle null check in where clause

View all articles by Patrick Barel. FROM emp t. WHERE t. NVL expr1 , expr2. NVL2 expr1 , expr2 , expr3. LNNVL condition. FROM emp e. AND e. FROM table ;. ELSE 'Unknown'. END color. Redgate Deploy is the solution to automate database deployments so that software can be delivered more frequently and reliably.

From versioning to Continuous integration and repeatable deployments, Redgate Deploy enables Continuous delivery of database changes for SQL Server, Oracle and 18 other databases.

Subscribe for more articles Fortnightly newsletters help sharpen your skills and keep you ahead, with articles, ebooks and opinion to keep you informed. Simple Talk. Rate this article. Patrick Barel View all articles by Patrick Barel. Load comments. To get the same info I'd rather do this to make the index work basing on updated query :. It will return the same result since seem to be a variable and it tends to be null or not null at the particular moment.

But also you can try using dynamic sql inside you stored procedure for such purposes:. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Asked 10 years, 3 months ago. Active 8 years, 3 months ago. Viewed 23k times. Thanks in advance! Improve this question. Stefan Stefan 5, 4 4 gold badges 22 22 silver badges 29 29 bronze badges.

Can you post the full SQL statement and the query plan in both cases? Is OrderDelivery a global temporary table? To do the reverse, and find all the rows with a non-null value, use the "is not null" condition:. If you want to include these, you must also test for this:. Oracle Database includes many functions to help you handle nulls. NVL and coalesce are two that map nulls to non-null values.

This is like NVL. But it can take any number of arguments. It returns the first non-null value it finds:. You can use these functions in the where clause to map nulls to a real value. So you no longer need a separate "or column is null" test. Null complicates your where clause. So some people are tempted to use "magic values" instead of null for missing or not applicable information.

For example, Mr. Penguin is made of fluff, not wood! You can now use standard comparison logic to find all the rows with a volume of wood less than 15 or where it doesn't apply:.