Skip to Main Content


Question and Answer

Chris Saxon

Thanks for the question, Gary.

Asked: August 04, 2022 - 5:34 pm UTC

Last updated: August 05, 2022 - 12:13 am UTC

Version: 19c

Viewed 100+ times

You Asked

hello I'm curious how you would deal with the following.

Table t1 has mixture of usernames, some with format of email address, some with format of first_name.last_name.
Table t2 lookup table has username in email format.
Requirement to return all 3 rows

create table t1 (taskid number, username varchar2(50))

insert into t1 values (1,'john.smith@12]');
insert into t1 values (2,'');
insert into t1 values (3,'dave.smith');


create table t2 (username varchar2(50));

insert into t2 values ('');
insert into t2 values ('');


Via ansi syntax, quite forward:

select *
from t1
left outer join t2
on t1.username = t2.username
t1.username  = substr(t2.username,1,instr(t2.username,'@')-1)
order by 1

Via oracle syntax - As can't use outer joins with OR, do you have any (easier) alternatives than this?

select t1.taskid, t1.username, t2.username
from t1, t2
where t1.username = t2.username
union all 
select t1.taskid, t1.username, t2.username
from t1, t2
where t1.username = substr(t2.username,1,instr(t2.username,'@')-1)
union all 
select t1.taskid, t1.username, to_char(null)
from t1
where not exists 
    (select 1 
    from t2
    where t1.username = t2.username 
    or t1.username = substr(t2.username,1,instr(t2.username,'@')-1))
order by 1


and Connor said...

A lateral does the trick nicely

SQL> select t1.*, x.username
  2  from t1,
  3       lateral(
  4         select t2.username
  5         from   t2
  6         where  t1.username = t2.username
  7         or t1.username = substr(t2.username,1,instr(t2.username,'@')-1)
  8         ) (+) x;

---------- -------------------- --------------------
         1 john.smith@12]
         3 dave.smith 


  (1 rating)


A reader, August 05, 2022 - 6:12 am UTC

More to Explore


The Oracle documentation contains a complete SQL reference.