I have two similar tables and Table1 is superset of Table2, e.g.

Table1
=====
ModelCode      ReferenceType     ReferenceId     Data1     Data2
Data3
1111                Type1                 Value1            d1
d2          d3
1232                Type2                 Value2            e1
e2          e3
3232                Type3                 Value3            a1
a2          a3
4234                Type4                 Value4            b1
b2          b3

Table2
=====
ModelCode      ReferenceType     ReferenceId
1111                Type1                 Value1
4234                Type4                 Value4

Therefore, I want to output from Table1 not existing at Table2
ModelCode      ReferenceType     ReferenceId
1232                Type2                 Value2
3232                Type3                 Value3

Any faster and simpler way to do so?

Thanks!

select table1.modelcode from  table1 left join table2 on table1.modelcode =
table2.modelcode where table2.modelcode is null

Regards,
Augustin
http://augustinprasanna.blogspot.com

Show quoteHide quote

If the columns allow nulls this query will not return the correct result.

This one is safer:

select table1.modelcode
         from table1
         where (not exists (
                     select table2.modelcode
                              from table2
                              where (table1.modelcode = table2.modelcode)
                     ))


ML

---
http://milambda.blogspot.com/

Interesting point, ML, but I dont' quite follow.

Can you show example data such that Augustin's LEFT JOIN solution would fail
and your subselect would work?  Both are using "table1.ModelCode =
Table2.ModelCode" so NULL's would mess them up similarly, would they not?

My understanding is also that the LEFT JOIN is significantly faster.


Show quoteHide quote

First of all, nullable keys are useless. :) But there are other circumstances
where comparing by nullable columns would be needed.

The question is how to handle null values (from a business perspective).
JOINs eliminate them automatically when they're not handled by the ON clause.

Example:
drop table    #t1
go

drop table    #t2
go

create table    #t1
    (
    c1    int        null
    ,c2    varchar(8)    null
    )
go

create table    #t2
    (
    c1    int        null
    ,c2    varchar(8)    null
    )
go

insert    #t1
    (
    c1
    ,c2
    )
    select    1 as c1
        ,'kjsd' as c2
    union all
    select    2
        ,'ftuh'
    union all
    select    null
        ,'jklmdc'
go

insert    #t2
    (
    c1
    ,c2
    )
    select    1 as c1
        ,'kjshjgwe' as c2
    union all
    select    3
        ,'riuewf'
    union all
    select    null
        ,'nuwiedfh'
go

select    *
    from    #t1

select    *
    from    #t2

select    *
    from    #t1
        inner join    #t2
                on    #t2.c1 = #t1.c1

select    *
    from    #t1
    where    (not exists (
            select    *
                from    #t2
                where    (#t2.c1 = #t1.c1)
            ))
go

BTW: this particular issue has been discussed in this newsgroup before.
Can't recall what exactly was the subject then, but we ended up comparing the
cases using a similar script.


ML

---
http://milambda.blogspot.com/

Ah, crap! Must be one of those days.

Here's the correct script:

drop table    #t1
go

drop table    #t2
go

create table    #t1
    (
    c1    int        null
    ,c2    varchar(8)    null
    )
go

create table    #t2
    (
    c1    int        null
    ,c2    varchar(8)    null
    )
go

insert    #t1
    (
    c1
    ,c2
    )
    select    1 as c1
        ,'kjsd' as c2
    union all
    select    2
        ,'ftuh'
    union all
    select    null
        ,'jklmdc'
go

insert    #t2
    (
    c1
    ,c2
    )
    select    1 as c1
        ,'kjshjgwe' as c2
    union all
    select    3
        ,'riuewf'
    union all
    select    null
        ,'nuwiedfh'
go

select    *
    from    #t1

select    *
    from    #t2

select    #t1.*
    from    #t1
        left join    #t2
                on    #t2.c1 = #t1.c1

select    *
    from    #t1
    where    (not exists (
            select    *
                from    #t2
                where    (#t2.c1 = #t1.c1)
            ))
go


ML

---
http://milambda.blogspot.com/

how to pass in a long string to stored procedure
Need Help With a Simple Query
Error converting data type varchar to numeric
Check int value
Strange characters with Bulk Load