NOT IN SQL Statement please

[Bill]

Hi,

I am struggling with the syntax of a 'not in' statement. Can anyone help
please with a simple generic statement based on table1, table2 and
fieldname.

I need to find records where fieldname exists in table2 that are not in
table1.

Thanks for any help.
Bill

 

[Ken Snell [MVP]]

Why try to use an IN clause at all?

SELECT table2.*
FROM table2 LEFT JOIN table1
ON table2.fieldname = table1.fieldname;

 

[Bill]

Thanks Ken, sometimes I miss the obvious. Will give that a try.
Bill.

 

[Tom van Stiphout]

That's called a subselect. Here goes:
select * from table2 where fieldname not in (select fieldname from
table1)

-Tom.
Microsoft Access MVP

 

[Bill]

Ken, your sql translates into this for my tables.

SELECT [Systems known to ICT].*
FROM [Systems known to ICT] LEFT JOIN [Systems from SM Returns]
ON ([Systems known to ICT].system=[Systems from SM Returns].system);

The return is all the records from [Systems known to ICT]

Anything I'm missing cos I cant spot it!

Regards.
Bill

 

[Bill]

Trying it now!

That's called a subselect. Here goes:
select * from table2 where fieldname not in (select fieldname from
table1)

-Tom.
Microsoft Access MVP

 

[Bill]

Sorted.
Many thanks.
Bill

That's called a subselect. Here goes:
select * from table2 where fieldname not in (select fieldname from
table1)

-Tom.
Microsoft Access MVP

 

[Ken Snell [MVP]]

Yeah, dumb me. Left out the important part:

SELECT table2.*
FROM table2 LEFT JOIN table1
ON table2.fieldname = table1.fieldname
WHERE table1.fieldname IS NULL;

Sorry!
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Ken, your sql translates into this for my tables.

SELECT [Systems known to ICT].*
FROM [Systems known to ICT] LEFT JOIN [Systems from SM Returns]
ON ([Systems known to ICT].system=[Systems from SM Returns].system);

The return is all the records from [Systems known to ICT]

Anything I'm missing cos I cant spot it!

Regards.
Bill

Thanks Ken, sometimes I miss the obvious. Will give that a try.
Bill.

 

[John Spencer]

SELECT Table2.*
FROM Table2 LEFT JOIN Table1
ON Table2.FieldName = Table1.FieldName
WHERE Table1.FieldName is Null

Using a NOT IN statement would generally be much slower, but

SELECT Table2.*
FROM Table2
WHERE Somefield NOT IN
(SELECT Somefield
FROM Table1
WHERE SomeField is not Null)

The reason for the is not null statement is that if there is a null
returned by the sub-query then when the test is done SomeField = NULL
the result will be indeterminate and it is not known if is the value is
or is not in the set of records and therefor you will get all the
records back.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[John Spencer]

Ok, I take that back. At least in Access 2003 I don't seem to need to
eliminate nulls in the sub-query. Must be some other SQL product that
I've seen the behavior in or perhaps an earlier version.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Sylvain Lafontaine]

This is for SQL-Server when the option ansi_null is set to true. For
Access/JET, comparaisons like « 2 = null » and « 2 <> null » evaluate to
False and True respectively and not to both Unknown/False like it is with
SQL-Server.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[vanderghast]

That should also be the case for Jet:


SELECT d FROM ds WHERE d NOT IN(SELECT null FROM ds)


should return no row at all.


Indeed
x NOT IN( y1, y2, y3) i
s the same as:
x <> y1 AND x <> y2 AND x <> y3
and if one of the comparison evaluates to NULL, given that the AND propagate
that null, no record will be returned.


On the other hand, with JET, if your list is constant, rather than based on
SELECT, the NULL are logically removed by the evaluation service:

? eval( " 4 IN (null, 2, 3)" )
0


Vanderghast, Access MVP

 

[vanderghast]

^ should have been

? eval(" 4 NOT IN(null, 2, 3)")
-1

while was expected:

4<>null AND 4<>2 AND 4<>3
= null AND true AND true
= null



Vanderghast, Access MVP