CODEWITHSUNDEEP
sqloracle-database
vkumar
vkumar

Reputation: 1

Oracle SQL compare two tables data

I have two tables A,B in my oracle db and I want to compare data of both tables based on unique field (userid) but B table contains user IDs as Puserid (P appended for all IDs)

How to use where condition in above case for two tables to retrieve data?

Upvotes: 0

Views: 83

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Technically, "P" is prepended, not appended.

You can just use ||:

from a join
     b
     on b.userId = 'P' || a.userId

This could have a big impact on performance. If you are able, you should define a new column in b (perhaps a virtual column). The above can use an index on b(userid).

You can also phrase this as:

from a join
     b
     on a.userId = substr(b.userId, 2)

And this can make use of an index on a(userid).

Upvotes: 2

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

You may use ltrim :

select a.*, b.*
  from tableA a join tableB b on ( a.userid = ltrim(b.userid,'P') );

Upvotes: 2

Related Questions