June 6, 2012 at 2:30 am
Hi,
I have 2 tables with below data and eid is the common column in both tables. I want to retrieve column [eid] values which are not exist in table2. I have written query to get the same using subquery but I want to implement it using joins. Following are the details.
Please help.
--===== Create the test table with
CREATE TABLE mytable1
(eid numeric,fname varchar(10),lname varchar(10))
create table mytable2
(eid numeric,estatus varchar(10),points numeric)
--inserting data into 1st table (mytable1)
insert into mytable1 (eid,fname,lname)
select '1', 'abc','def' union all
select '2', 'zzz','yyy' union all
select '3', 'ada','sda' union all
select '4', 'wqw','aas' union all
select '5', 'sga','sds' union all
select '6', 'ttt','eee'
--inserting data into 2nd table (mytable2)
insert into mytable2 (eid,estatus,points)
select '1', 'good',100 union all
select '2', 'verygood',200 union all
select '3', 'good',300 union all
select '4', 'verygood',400 union all
--used SubQuery used to get desired result (which is to get EID values not in mytable2)
select eid,fname,lname from mytable1
where eid not in (select eid from mytable2)
--Looking for same result using Joins??
Thanks
June 6, 2012 at 2:43 am
Would be a case for a left outer join where mytable2.eid is null
select
t1.eid
from
mytable1 t1
left outer join
mytable2 t2
on
t1.eid = t2.eid
where
t2.eid is null
returns mytable1.eid 5 and 6
June 6, 2012 at 2:56 am
Why do you want to use a less-efficient mechanism? Joins are more expensive than in/exists or not in/not exists subqueries are.
http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/
http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply