Merge table in Oracle with delete condition refering to source table

Question

For the following question it is said that the answer should be C. But I think the correct answer is Answer D as NOT MATCHED block inserts all unmatching records to target table. Can anybody explain this? Thank you.

Q)View the Exhibit and examine the data in ORDERS_MASTER and MONTHLY_ORDERS tables.

Evaluate the following MERGE statement:

MERGE INTO orders_master o
USING monthly_orders m
ON (o.order_id = m.order_id)
WHEN MATCHED THEN
UPDATE SET o.order_total = m.order_total
DELETE WHERE (m.order_total IS NULL)
WHEN NOT MATCHED THEN
INSERT VALUES (m.order_id, m.order_total);

What would be the outcome of the above statement?

A. The ORDERS_MASTER table would contain the ORDER_IDs 1 and 2.

B. The ORDERS_MASTER table would contain the ORDER_IDs 1,2 and 3.

C. The ORDERS_MASTER table would contain the ORDER_IDs 1,2 and 4.

D. The ORDERS_MASTER table would contain the ORDER IDs 1,2,3 and 4.

Answer: C

Answer 1

The correct answer is indeed C, this is because the source of the merge operation is the monthly_orders table, which only contains two records with order_id 2 and 3 respectively.

Think about what will happen for each of these records:

• For order_id = 2, because this id exists in the order_master table, we'll execute the MATCHED part of the merge statement, updating the order_total to 2500. Since the quantity for this record is not NULL, the DELETE won't do anything.
• For order_id = 3, again, the id exists in the order_master table, so we execute the MATCHED part of the merge statement, updating the order_total to NULL and then issuing a DELETE on order_master for the row we just updated because the quantity on monthly_order is NULL.

This leaves us with order_id 1, 2 and 4, which matches answer C.

Code

CREATE TABLE orders_master (
    order_id NUMBER(1) NOT NULL
   ,order_total NUMBER(10) NULL
)
/

CREATE TABLE monthly_orders (
    order_id NUMBER(1) NOT NULL
   ,order_total NUMBER(10) NULL
)
/

INSERT INTO orders_master (order_id, order_total) VALUES (1, 1000)
/

INSERT INTO orders_master (order_id, order_total) VALUES (2, 2000)
/

INSERT INTO orders_master (order_id, order_total) VALUES (3, 3000)
/

INSERT INTO orders_master (order_id, order_total) VALUES (4, NULL)
/

INSERT INTO monthly_orders (order_id, order_total) VALUES (2, 2500)
/

INSERT INTO monthly_orders (order_id, order_total) VALUES (3, NULL)
/

MERGE INTO orders_master o
USING monthly_orders m
ON (o.order_id = m.order_id)
WHEN MATCHED THEN 
    UPDATE SET o.order_total = m.order_total
    DELETE WHERE m.order_total IS NULL
WHEN NOT MATCHED THEN
    INSERT VALUES (m.order_id, m.order_total)
/

COMMIT
/


SQL> select * from orders_master
2  /

  ORDER_ID ORDER_TOTAL
---------- -----------
         1        1000
         2        2500
         4