CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Sun Shyne
Sun Shyne

Reputation: 25

How to search for multiple names on different rows where first name and last name on different columns?

ID FIRST_NAME LAST_NAME
1 SARA JAMES
5 STEVE STEPHEN
2 KAL LEE
33 TONY KAMPLOO
3 SANDRA BROWN
23 HOPE WHITE
FIRST_NAME LAST_NAME
SARA JAMES

I have 2 tables:

Table1 has ID, First names and last names like the table above.

Table2 has only First names and last names. (subset of table1).

I need to match the names from table2 with names in table1 and return their IDs??? This is just a sample. My tables are large so I need an efficient way to do this.

Upvotes: 0

Views: 767

Answers (2)

user5683823
user5683823

Reputation:

The following query will show all the rows from the first table where the first name and the last name appear in the second table. The operation is called a semi join. For a given row from the first table, the "joining" stops as soon as a match is found in the second table. (In a standard join, searching will continue even after a match is found, since the join must find all matches - even if we know there is only one match, Oracle doesn't know that so it must continue searching.)

select t1.* 
from   t1
where  (first_name, last_name) in (select first_name, last_name from t2);

Upvotes: 0

Aleksej
Aleksej

Reputation: 22949

This looks like a plain INNER JOIN:

select t1.*
from table1 t1
       inner join table2 t2
         on(t1.first_name = t2.first_name and t1.last_name = t2.last_name)

Upvotes: 1

Related Questions