SQL quiery: excluding from BIG table those ones who are in SMALL

[irinnew]

Hi all,

I have two tables BIG and SMALL.
I need all fields of just those members from BIG table which are not in the SMALL table
In other words I need to exclude those ones from BIG who have the same IDs in SMALL
I have a common field ID

Could you help me with this

Thanks,

Irin

 

[Zathras]

Something like this?
[tt]
select BIG.* from BIG
left outer join SMALL on SMALL.FIELDID=BIG.FIELDID
where SMALL.SOMEOTHERSMALLFIELD is null
[/tt]

 

[dnoeth]

As long as both id columns are NOT NULL:
select *
from big
where id not in
(select id from small)


Always working:
select *
from big
where not exists
(select *
from small
where big.id = small.id
)


Or:
select big.*
from big left join small
on big.id = small.id
where small.id is null

Dieter

 

[irinnew]

Zathras,

I am just wondering...Is there any specific reason why the very bottom condition is someothersmallfield is

null.....rather than SMALL.FIELDID is null?

Thank you!

Irin

 

[Zathras]

When I tried that (some years ago and I can't remember which DBMS it was), the DBMS plugged in the value from the left table so that even though it was in fact null in the (non-matching) right-hand table, it (temporarily) contained the value from the left-hand table and so would not test as a NULL.

When you think about it, it doesn't seem logical to ask the DBMS to match FIELDID from one table with FIELDID from another at the same time you want one of them to be NULL. In other words, it would be like asking SMALL.FIELDID to be both equal to BIG.FIELDID and NULL at the same time.

So the bottom line is, it may work for some DBMS but not for others. If it works today, will it still work after a conversion to another DBMS? (which of course is the point of trying to stay with pure ANSI SQL when possible. I just wish I knew how.)

 

[r937]

When you think about it, it doesn't seem logical to ask the DBMS to match FIELDID from one table with FIELDID from another at the same time you want one of them to be NULL. In other words, it would be like asking SMALL.FIELDID to be both equal to BIG.FIELDID and NULL at the same time.

actually, it does make sense, because it's not "at the same time"


think of it like the following steps in this sequence:
1) perform the join on given conditions
2) for unmatched rows, set columns from right table to null
3) execute WHERE clause, including test for null in join column

the join column is legitimately the only column you can safely test in the WHERE clause for an unmatched row null column

r937.com | rudy.ca

 

[Zathras]

r937, all well and good, but the DBMS I was using at the time didn't work quite that way. It seemed to want to make a virtual record that had the join fields populated in all rows and only the non-join fields had NULL values.

the join column is legitimately the only column you can safely test in the WHERE clause for an unmatched row null column

-- not quite sure why you say that. Any column from the unmatched table should test correctly for NULL.

My point is simply that despite whatever the ANSI standard of the day is, it may or may not work as expected in any given DBMS. Caveat queryer.

 

[r937]

any column from the unmatched row will be null, yes, that's true

but a non-join column may be null on matched rows, too

that's why i say that ;-)

r937.com | rudy.ca

 

[Zathras]

Ah, right. I forgot. Not just any column can be used. It has to be a column known always to have a value. Best bet is to use one defined with NOT NULL (or equivalent expression depending on the DBMS).

Some things I take for granted and forget to be as explicit as I should. Thanks for the clarification.

 

[r937]

that's why i suggest it should always be a join column that's tsted for null

a column from the right table that is specified in the ON clause must not be null if it is to join with a column of the left table, since null is not equal to anything

in other words, even if the right table consisted entirely of columns that allow null (e.g. you're not joining on a PK), you would never get any row with a null in the join column to "match"

so because the join condition is left.foo=right.foo, then the only way right.foo will have a null value in the result set is if there was no matching row for left.foo

if there are any rows with right.foo null, those will never match anything, and they will also never be included in the result set, either

r937.com | rudy.ca