可以使用MERGE语句和PARTITION艾德ed OUTER JOIN：

MERGE INTO applicant dst
USING (
  WITH benefits (benefit) AS (
    SELECT 'food'            FROM DUAL UNION ALL
    SELECT 'rent assistance' FROM DUAL UNION ALL
    SELECT 'bus ticket'      FROM DUAL
  )
  SELECT a.ROWID AS rid,
         a.pID,
         b.benefit
  FROM   benefits b
         LEFT OUTER JOIN applicant a
         PARTITION BY (a.pID)
         ON (a.received = b.benefit)
) src
ON (src.rid = dst.ROWID)
WHEN NOT MATCHED THEN
  INSERT (pID, Received) VALUES (src.pID, src.benefit);

其中，对于样本数据：

CREATE TABLE applicant (pID, Received) AS
SELECT 1, 'food' FROM DUAL UNION ALL
SELECT 1, 'rent assistance' FROM DUAL UNION ALL
SELECT 1, 'bus ticket' FROM DUAL UNION ALL
SELECT 2, 'food' FROM DUAL UNION ALL
SELECT 2, 'rent assistance' FROM DUAL UNION ALL
SELECT 3, 'food' FROM DUAL UNION ALL
SELECT 4, 'bus ticket' FROM DUAL UNION ALL
SELECT 5, null FROM DUAL;

然后，在MERGE之后，该表包含：
| PID|接收|
| --|--|
| 1 |食品|
| 1 |租金援助|
| 1 |汽车票|
| 2 |食品|
| 2 |租金援助|
| 3 |食品|
| 4 |汽车票|
| 5 |（空值）|
| 2 |汽车票|
| 3 |汽车票|
| 3 |租金援助|
| 4 |食品|
| 4 |租金援助|
| 5 |汽车票|
| 5 |食品|
| 5 |租金援助|
提琴