Geek Answers Handbook

Oracle: check if rows exist in another table

I have a query joining multiple tables and returning multiple columns.

The indexed column of another table refers to the PK of one of these joined tables. Now I would like to add another column to the query that indicates whether at least one row with this identifier exists in the new table.

So, if I have one of the old tables

ID 1 2 3

and new table

 REF_ID 1 1 1 3

then i would like to get

 ID REF_EXISTS 1 1 2 0 3 1

I can come up with several ways to do this, but what is the most elegant / efficient?

EDIT I tested the performance of queries containing 50,000 records in the old table, each other record being matched by two rows in the new table, so half of the records have REF_EXISTS = 1.

I add average results as comments to the answers in case someone is interested. Thanks everyone!

+4
source share
4 answers

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
+7
source

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

If you have indexes on PC and FK, you will leave with table scans and indexes.

Relationship To

+4
source

Using:

  SELECT DISTINCT t1.id, CASE WHEN t2.ref_id IS NULL THEN 0 ELSE 1 END AS REF_EXISTS FROM TABLE_1 t1 LEFT JOIN TABLE_2 t2 ON t2.ref_id = t1.id

Added DISTINCT to display only unique lines.

+1
source

A join can return multiple rows for a single id, as for id=1 in the example data. You can limit it to one line on id using a group:

 SELECT t1.id , COUNT(DISTINCT t2.ref_id) as REF_EXISTS FROM TABLE_1 t1 LEFT JOIN TABLE_2 t2 ON t2.ref_id = t1.id GROUP BY t1.id

group by provides only one line per identifier. And count(distinct t2.ref_id) will be 1 if the string is found and 0 otherwise.

EDIT: you can rewrite it without group by , but I doubt it will simplify the situation:

 SELECT t1.id , CASE WHEN EXISTS ( SELECT * FROM TABLE_2 t2 WHERE t2.ref_id = t1.id) THEN 1 ELSE 0 END as REF_EXISTS , .... FROM TABLE_1 t1
+1
source

More articles:


All Articles