Another option:
select O.ID , case when N.ref_id is not null then 1 else 0 end as ref_existsfrom old_table oleft outer join (select distinct ref_id from new_table) N on O.id = N.ref_id
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.
RegardsK
Use:
SELECT DISTINCT t1.id, CASE WHEN t2.ref_id IS NULL THEN 0 ELSE 1 END AS REF_EXISTS FROM TABLE_1 t1LEFT JOIN TABLE_2 t2 ON t2.ref_id = t1.id
Added DISTINCT to ensure only unique rows are displayed.
DISTINCT