SELECT Data not in the table from another

Question

SELECT Data not in the table from another

ykose05

SSC Rookie

Points: 42
More actions
August 1, 2010 at 9:07 am

#223950

Hi all
i m trying to select data from a table not in the antother table. and im little dummy about SQL
my table like this (same table diffirent Dbes)
tbl1
ID(PK),CODE(PK),VALUE1,VALUE2,VALUE3
the code i m using
SELECT * FROM db1..tbl1 AS T1
WHERE T1.ID NOT IN (SELECT ID FROM db2..tbl1)
This one works fine if table have one unique row
in my case i m using someting like
select * from db1..tbl1 as T1
where cast(T1.code as varchar)+cast(T1.ID as varchar) not in
(select cast(code as varchar)+cast(ID as varchar) from db2..tbl1)
and this comes some what funny to me 🙂
there has to be an other easier way for this. and i m searcing for your help.
Thanks

Viewing 8 posts - 1 through 8 (of 8 total)

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

Steve Jones - SSC Editor SSC Guru Points: 738643 More actions · Answer 1

Depending on how the CAST works on the data, you could end up with different values. What you need to do is cast specifically to a size and ensure that the data looks alike.

What data types are these columns?

ykose05 SSC Rookie Points: 42 More actions · Answer 2

data types in the table ID is INT and CODE is varchar.

I converted ID to a varchar and add it to CODE to obtain a unique column.

it works fine but takes some time

i m searching a faster way 🙂

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 3

Instead of trying to use IN and forcing the comparison using character strings, you should convert it to an EXISTS or OUTER JOIN comparison on a correlated query.

For example:

SELECT *

FROM db1.dbo.tbl1 t1

LEFT JOIN db2.db0.tbl1 t2

ON t2.ID = t1.ID

AND t2.code = t1.code

WHERE t2.ID IS NULL;

Or, using NOT EXISTS

SELECT *

FROM db1.dbo.tbl1 t1

WHERE NOT EXISTS (SELECT *

FROM db2.dbo.tbl2 t2

WHERE t2.ID = t1.ID

AND t2.code = t1.code);

The first one might give you additional rows if there are multiple rows in tbl2 that match. You'll have to check each one to see which one performs better.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Steve Jones - SSC Editor SSC Guru Points: 738643 More actions · Answer 4

Make sure you have indexed the columns.

Is there a reason that you cannot check for an AND with both columns? As in Jeffrey's first example.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

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

drew.allen SSC Guru Points: 77014 More actions · Answer 6

I find the EXCEPT clause works well in these situations, particularly as the number of columns you want to compare increases.

SELECT *

FROM db1..tbl1

EXCEPT

SELECT *

FROM db2..tbl1

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

ykose05 SSC Rookie Points: 42 More actions · Answer 7

Thank you for your answers.

EXCEPT and NOT EXISTS works good for me.

I m new to sql so the commands and operators that i know is noting compared to you all.

Thanks again.