SQL exclusive Join?

[gashcud]

Hi,
i have a silly question and i'm sure the answer is just too simple.

i have two tables with primary key and forign key, what i want to achieve is to get all the rows back where there are no matches. e.g.

Table 1 Table 2
Name ID Company ID
John 1 Sun 1
Steve 2 Oracle 2
Hary 3 Apple 4


so now the result of the query should be Hary because it does not mactch a INNER JOIN query.

how can i achieve this?

thanx in advance

 

[dnoeth]

If t1.id is not nullable:
select * from t1
where t1.id not in
(select id from t2)

or

select * from t1
where not exists
(select * from t2
where t1.id = t2.id)

or

select t1.*
from t1 left outer join t2 on t1.id = t2.id
where t2.id is null

Dieter

 

[dnoeth]

"If t1.id is not nullable:"

Sorry,
If t2.id is not nullable:

 

[flowcontrol]

dnoeth:

I don't think it matters whether or not t2.id is nullable. The t2.id in the resulting recordset is just a column in the recordset; it is not the column in table t2. The NOT NULL constraint in the table does not apply to the recordset.

BTW, gashcud, the "outer join/not null" construct is much, much faster than "not in".

select t1.*
from t1 left outer join t2 on t1.id = t2.id
where t2.id is null

 

[dnoeth]

"I don't think it matters whether or not t2.id is nullable."

If there's a NULL returned by the NOT IN-subquery the answer set is always empty, because NOT IN is equal to
col <> value1 AND col <> value2 AND col <> NULL
and this is evaluated to UNKNOWN/FALSE

Dieter

 

[r937]

good one, dieter

i've always preferred NOT EXISTS ( SELECT ... myself

but there are parts of this question i am having trouble with

the premise was given that the tables are related via a foreign key

if Hary 3 is the primary, then where did Apple 4 come from? does it matter whether t2.id is not nullable?

but if Apple 4 is the primary, where did Hary 3 come from?

one of them shouldn't be there

also, so far, solutions are all left joins, with Table1 as the left table

maybe they should be right joins with Table1 as the left table


<voice type=&quot;curly&quot;>
i'm tryna think, but nothing happens
</voice>

rudy

 

[gashcud]

thanks guys, the &quot;where null&quot; thing did the job for me

yrs
gashcud