Trying to compare several similiar lists on one table to produce a concatenated result
JOIN is not that effective, because you need to access the table 4 times. With PIVOT you can do it with 1 table access.

create table list_of_stuff 
(
  list_id number(5,0),
  item varchar2(40 char)
);

insert all
  into list_of_stuff values (47, 'Baseball')
  into list_of_stuff values (47, 'Hat')
  into list_of_stuff values (47, 'Gloves')
  into list_of_stuff values (47, 'Sparkle Shoes')
  into list_of_stuff values (91, 'Baseball')
  into list_of_stuff values (91, 'Sparkle Shoes')
  into list_of_stuff values (91, 'Bubble Gum')
  into list_of_stuff values (91, 'Hat')
  into list_of_stuff values (91, 'Fish Tank')
  into list_of_stuff values (91, 'Book of Spiders')
  into list_of_stuff values (103, 'Silver Spoon')
  into list_of_stuff values (103, 'Fiddle')
  into list_of_stuff values (103, 'Trumpet')
  into list_of_stuff values (103, 'Paper Towels')
  into list_of_stuff values (103, 'Fireworks')
  into list_of_stuff values (103, 'Mosquito Netting')
  into list_of_stuff values (103, 'Cure for the Common Cold')
  into list_of_stuff values (295, 'Baseball')
  into list_of_stuff values (295, 'Fireworks')
  into list_of_stuff values (295, 'Trumpet')
  into list_of_stuff values (295, 'Bubble Gum')
  into list_of_stuff values (295, 'Hat')
  into list_of_stuff values (295, 'Gloves')
  into list_of_stuff values (295, 'Christmas Tree')
select * from dual;

commit;


JOIN version:

SQL&gt; set null "(null)"
SQL&gt; r
  1  select *
  2  from
  3                  (select * from list_of_stuff where list_id = 47)  L1
  4  full outer join (select * from list_of_stuff where list_id = 91)  L2 using (item)
  5  full outer join (select * from list_of_stuff where list_id = 103) L3 using (item)
  6  full outer join (select * from list_of_stuff where list_id = 295) L4 using (item)
  7  order by item
  8*

ITEM                              LIST_ID    LIST_ID    LIST_ID    LIST_ID
------------------------------ ---------- ---------- ---------- ----------
Baseball                               47         91 (null)            295
Book of Spiders                (null)             91 (null)     (null)
Bubble Gum                     (null)             91 (null)            295
Christmas Tree                 (null)     (null)     (null)            295
Cure for the Common Cold       (null)     (null)            103 (null)
Fiddle                         (null)     (null)            103 (null)
Fireworks                      (null)     (null)            103        295
Fish Tank                      (null)             91 (null)     (null)
Gloves                                 47 (null)     (null)            295
Hat                                    47         91 (null)            295
Mosquito Netting               (null)     (null)            103 (null)
Paper Towels                   (null)     (null)            103 (null)
Silver Spoon                   (null)     (null)            103 (null)
Sparkle Shoes                          47         91 (null)     (null)
Trumpet                        (null)     (null)            103        295


This returns a NULL where the item is not on the list, otherwise it returns the list_id. The actual value does not matter, just the fact that it is not null, so providing the desired output:

SQL&gt; r
  1  select
  2    nvl2(L1.list_id, item, null) as LIST_47,
  3    nvl2(L2.list_id, item, null) as LIST_91,
  4    nvl2(L3.list_id, item, null) as LIST_103,
  5    nvl2(L4.list_id, item, null) as LIST_295
  6  from
  7                  (select * from list_of_stuff where list_id = 47)  L1
  8  full outer join (select * from list_of_stuff where list_id = 91)  L2 using (item)
  9  full outer join (select * from list_of_stuff where list_id = 103) L3 using (item)
 10  full outer join (select * from list_of_stuff where list_id = 295) L4 using (item)
 11  order by item
 12*

LIST_47         LIST_91         LIST_103                       LIST_295
--------------- --------------- ------------------------------ ---------------
Baseball        Baseball        (null)                         Baseball
(null)          Book of Spiders (null)                         (null)
(null)          Bubble Gum      (null)                         Bubble Gum
(null)          (null)          (null)                         Christmas Tree
(null)          (null)          Cure for the Common Cold       (null)
(null)          (null)          Fiddle                         (null)
(null)          (null)          Fireworks                      Fireworks
(null)          Fish Tank       (null)                         (null)
Gloves          (null)          (null)                         Gloves
Hat             Hat             (null)                         Hat
(null)          (null)          Mosquito Netting               (null)
(null)          (null)          Paper Towels                   (null)
(null)          (null)          Silver Spoon                   (null)
Sparkle Shoes   Sparkle Shoes   (null)                         (null)
(null)          (null)          Trumpet                        Trumpet


This requires 28 buffer gets on my demo table:

SQL&gt; select * from table(dbms_xplan.display_cursor(format=&gt;'iostats last'));
...

Plan hash value: 912304261

------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               |      1 |        |     15 |00:00:00.01 |      28 |
|   1 |  SORT ORDER BY             |               |      1 |     21 |     15 |00:00:00.01 |      28 |
|   2 |   VIEW                     | VW_FOJ_0      |      1 |     21 |     15 |00:00:00.01 |      28 |
|*  3 |    HASH JOIN FULL OUTER    |               |      1 |     21 |     15 |00:00:00.01 |      28 |
|   4 |     VIEW                   |               |      1 |      7 |      7 |00:00:00.01 |       7 |
|*  5 |      TABLE ACCESS FULL     | LIST_OF_STUFF |      1 |      7 |      7 |00:00:00.01 |       7 |
|   6 |     VIEW                   | VW_FOJ_1      |      1 |     13 |     14 |00:00:00.01 |      21 |
|*  7 |      HASH JOIN FULL OUTER  |               |      1 |     13 |     14 |00:00:00.01 |      21 |
|   8 |       VIEW                 | VW_FOJ_2      |      1 |      6 |      7 |00:00:00.01 |      14 |
|*  9 |        HASH JOIN FULL OUTER|               |      1 |      6 |      7 |00:00:00.01 |      14 |
|  10 |         VIEW               |               |      1 |      4 |      4 |00:00:00.01 |       7 |
|* 11 |          TABLE ACCESS FULL | LIST_OF_STUFF |      1 |      4 |      4 |00:00:00.01 |       7 |
|  12 |         VIEW               |               |      1 |      6 |      6 |00:00:00.01 |       7 |
|* 13 |          TABLE ACCESS FULL | LIST_OF_STUFF |      1 |      6 |      6 |00:00:00.01 |       7 |
|  14 |       VIEW                 |               |      1 |      7 |      7 |00:00:00.01 |       7 |
|* 15 |        TABLE ACCESS FULL   | LIST_OF_STUFF |      1 |      7 |      7 |00:00:00.01 |       7 |
------------------------------------------------------------------------------------------------------


Then I have this PIVOT query that puts the number 1 (or I could have written any other value in MIN(), or use another aggregate function) to a list column where the item in the row exists:

SQL&gt; set null "(null)"
SQL&gt; r
  1  select *
  2  from
  3  (
  4    select list_id, item
  5    from   list_of_stuff
  6    where  list_id in (47, 91, 103, 295)
  7  )
  8  pivot (min(1) as in_list for list_id in (47, 91, 103, 295))
  9  order by item
 10*

ITEM                           47_IN_LIST 91_IN_LIST 103_IN_LIST 295_IN_LIST
------------------------------ ---------- ---------- ----------- -----------
Baseball                                1          1 (null)                1
Book of Spiders                (null)              1 (null)      (null)
Bubble Gum                     (null)              1 (null)                1
Christmas Tree                 (null)     (null)     (null)                1
Cure for the Common Cold       (null)     (null)               1 (null)
Fiddle                         (null)     (null)               1 (null)
Fireworks                      (null)     (null)               1           1
Fish Tank                      (null)              1 (null)      (null)
Gloves                                  1 (null)     (null)                1
Hat                                     1          1 (null)                1
Mosquito Netting               (null)     (null)               1 (null)
Paper Towels                   (null)     (null)               1 (null)
Silver Spoon                   (null)     (null)               1 (null)
Sparkle Shoes                           1          1 (null)      (null)
Trumpet                        (null)     (null)               1           1


Similarly, after transforming it to the desired output:

SQL&gt; r
  1  select
  2    nvl2("47_IN_LIST", item, null)  as list_47,
  3    nvl2("91_IN_LIST", item, null)  as list_91,
  4    nvl2("103_IN_LIST", item, null) as list_103,
  5    nvl2("295_IN_LIST", item, null) as list_295
  6  from
  7  (
  8    select *
  9    from
 10    (
 11      select list_id, item
 12      from   list_of_stuff
 13      where  list_id in (47, 91, 103, 295)
 14    )
 15    pivot (min(1) as in_list for list_id in (47, 91, 103, 295))
 16  )
 17  order by item
 18*

LIST_47         LIST_91         LIST_103                       LIST_295
--------------- --------------- ------------------------------ ---------------
Baseball        Baseball        (null)                         Baseball
(null)          Book of Spiders (null)                         (null)
(null)          Bubble Gum      (null)                         Bubble Gum
(null)          (null)          (null)                         Christmas Tree
(null)          (null)          Cure for the Common Cold       (null)
(null)          (null)          Fiddle                         (null)
(null)          (null)          Fireworks                      Fireworks
(null)          Fish Tank       (null)                         (null)
Gloves          (null)          (null)                         Gloves
Hat             Hat             (null)                         Hat
(null)          (null)          Mosquito Netting               (null)
(null)          (null)          Paper Towels                   (null)
(null)          (null)          Silver Spoon                   (null)
Sparkle Shoes   Sparkle Shoes   (null)                         (null)
(null)          (null)          Trumpet                        Trumpet


Much simpler, 1 table access, 7 buffer gets:

SQL&gt; select * from table(dbms_xplan.display_cursor(format=&gt;'iostats last'));

...

Plan hash value: 1664708432

-----------------------------------------------------------------------------------------------
| Id  | Operation           | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               |      1 |        |     15 |00:00:00.01 |       7 |
|   1 |  SORT GROUP BY PIVOT|               |      1 |     24 |     15 |00:00:00.01 |       7 |
|*  2 |   TABLE ACCESS FULL | LIST_OF_STUFF |      1 |     24 |     24 |00:00:00.01 |       7 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("LIST_ID"=47 OR "LIST_ID"=91 OR "LIST_ID"=103 OR "LIST_ID"=295))


Now if you don't insist on naming the columns LIST_47, LIST_91, ..., the JOIN version peforms worse, but it can be easily reused, by changing the 4 list_id parameters, you can even use bind variables there. The PIVOT performs better, but if you want to use different list_id values, you need to rewrite the query, because you can not dynamically provide the values for the pivot( ... in (***)). PIVOT needs to know the column names it generates at parse time.