Oracle: Check if rows exist in other table

Question

I've got a query joining several tables and returning quite a few columns.

An indexed column of another table references the PK of one of these joined tables. Now I would like to add another column to the query that states if at least one row with that ID exists in the new table.

So if I have one of the old tables

ID
 1
 2
 3

and the new table

REF_ID
1
1
1
3

then I'd like to get

ID   REF_EXISTS
 1            1
 2            0
 3            1

I can think of several ways to do that, but what is the most elegant/efficient one?

---

EDIT I tested the performance of the queries provided with 50.000 records in the old table, every other record matched by two rows in the new table, so half of the records have REF_EXISTS=1.

I'm adding average results as comments to the answers in case anyone is interested. Thanks everyone!

Answer 1

Another option:

select O.ID
    , case when N.ref_id is not null then 1 else 0 end as ref_exists
from old_table o
left outer join (select distinct ref_id from new_table) N
   on O.id = N.ref_id

Answer 2

I would:

select distinct ID,
       case when exists (select 1 from REF_TABLE where ID_TABLE.ID = REF_TABLE.REF_ID)
    then 1 else 0 end
    from ID_TABLE

Provided you have indexes on the PK and FK you will get away with a table scan and index lookups.

Regards K