Post reply

To get the result without using joins (not subquery)

  • June 6, 2012 at 2:30 am

    #262056

    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

    #1497460

    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

    #1497465

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply