Will a SELECT . . . INTO use short-circuit evaluation for a COALESCE?

I'm building a stored proc that can take in two separate values that each can be used to identify a single record. One of these values may be null.

My logic is as follows: if IN_ORDER_ID is not NULL, save it to V_ORDER_ID. If it is NULL, then retrieve ORDER_ID with a query and save that to V_ORDER_ID. V_ORDER_ID is then used in the rest of the procedure.

Here is the code:

SELECT COALESCE(IN_ORDER_ID, order_id) INTO V_ORDER_ID FROM SCHEMA.ORDER WHERE SXC_ORDER_NUMBER = IN_SXC_ORDER_NUMBER;

My Question: Will the coalesce in this query still take advantage of short-circuit evaluation?

Yes, per documentation:

Database SQL Language Reference (Oracle 11g): COALESCE()


Oracle Database uses short-circuit evaluation. The database evaluates each expr value and determines whether it is NULL, rather than evaluating all of the expr values before determining whether any of them is NULL.

Note (having been burned by this myself): be wary of relying on proof-of-concept to determine order-of-operations in SQL language. SQL is declarative, and the engine is often given the flexibility to execute portions of a statement in arbitrary orders. So just because one ad-hoc example shows the order of execution of operations A, B, C to be A-then-B-then-C, it may be the case that in different circumstances, engine will opt for alternate execution order, e.g. B-then-A-then-C.

StackExchange Database Administrators Q#145644, answer score: 6