SQL statement "not in"

kylewilk · #1 (**permalink**) 05-07-04, 16:20

I have a couple tables and I need to compare some information. If the two columns I am comparing are the same length and I use the "not in" statement, everything is fine, but if they differ in length it won't work for some reason. Any ideas?
Here is the code I'm using:

select a.SOP from labor as a
where a.SOP not in (select b.tSOP from torch as b);

This works fine when column SOP and tSOP have the same amount of fields, but not if SOP if shorter than tSOP.

Pat Phelan · #2 (**permalink**) 05-07-04, 16:32

I think you really want

Code:

SELECT a.SOP
   FROM labor as a
   WHERE NOT EXISTS (select *
      from torch as b
      WHERE b.tSOP = a.SOP);

-PatP

sundialsvcs · #3 (**permalink**) 05-09-04, 22:25

I don't know the exact answer to your question in the exact context you're referring to... but I do observe that there might be a better way to solve that query.

Code:

SELECT sop FROM labor a 
LEFT JOIN torch b USING (a.sop = b.tsop)
WHERE b.tsop IS NULL

(extemporaneous coding... use only as directed.)

blindman · #4 (**permalink**) 05-10-04, 00:40

Confusing post. Your first paragraph talks about the problem occuring when the two fields are not the same lenghth. Your last paragraph says the problem occurs when the columns don't have the "same amount of fields". I'm going to assume that is a misprint, and that your problem is that the two columns are different lengths.

Given columnA varchar(10) and columnB varchar(20), any values in the two tables can be directly compared as long as the value in columnB is less than or equal to 10 characters. If it is 11 characters long, it can never match a value from columnA.

You should start by rewriting your statement like this to take advantage of any indexing on the columns.

select labor.SOP
from labor
left outer join torch on labor.SOP = torch.tSOP
where torch.tSOP is null

This will find EXACT MATCHES, based on length and characters, ignoring any trailing spaces (for char columns). If you want columnA varchar(10) against the first 10 characters of columnB varchar(20), use this:

select labor.SOP
from labor
left outer join torch on labor.SOP = left(torch.tSOP, 10)
where torch.tSOP is null

..but you will lose the benefits of any index on torch.tSOP.

Another general solution, not knowing the lengths of labor.SOP or torch.tSOP, is this:

select labor.SOP
from labor
left outer join torch on labor.SOP like 'torch.tSOP%'
where torch.tSOP is null

I hope one of these posts has been helpful, and if not then please post again and explain your problem more clearly.

Internet Services

Web Development

Digital Marketing

Consumer Tech