Thank you Norm!
The link is also very useful.
Von: email@example.com [mailto:firstname.lastname@example.org] Im Auftrag von Dunbar,
Gesendet: Dienstag, 1. März 2011 10:52
Betreff: RE: [toad] Brain fry OT: NVL vs. Coalesce
NVL is Oracle specific and not SQL Standard. Coalesce is standard.
NVL takes only two arguments, the first to be tested for NULL and the
next to be returned if the first is NULL. Both are evaluated, as you
COALESCE takes multiple arguments, and will only evaluate up to the
first non-null argument.
So, while you get a barf on this:
SELECT NVL(1,1/0) FROM dual; – ORA-01476: Divisor is Null
You don’t on this because 1 is NOT NULL, so the second arg need not be
SELECT COALESCE(1,1/0) FROM dual;
Try it again on a column though, I rather suspect it will blow up in
your face, exactly as NVL does, when you hit the first NULL.
As to performance, I haven’t done any comparisons yet. One day, I might.
However, there is a note here about performance of NVL when it evaluates
the second argument:
Information in this message may be confidential and may be legally privileged.
If you have received this message by mistake, please notify the sender
immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still
check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under
the Freedom of Information Act, Data Protection Act or for litigation. Email
messages and attachments sent to or from any Environment Agency address may also
be accessed by someone other than the sender or recipient, for business
If we have sent you information and you wish to use it please read our terms and
conditions which you can get by calling us on 08708 506 506. Find out more about
the Environment Agency at www.environment-agency.gov.uk