Using NOT IN option in where clause

Hi,
I have two tables. Main table is soxbillingrelationship having a column as BRID along with many other columns. The other table is BR having just two columns one is BRID and other is OMS. Now i have to extract all the rows from the table BR which has BRID not existing in Soxbillingrelationship. I tried this query:
select * from BR
where BRID NOT IN (select brid from SoxBillingRelationship)
order by BRID
This is returning around 204 rows whereas totally there are more than 1000 BRID which are not in soxbillingrelationship table.
Thanks for the advice!!

Perhaps this gives you the expected result, minus 204:
SELECT COUNT(*) FROM BR
WHERE BRID IS NULL
... meaning that your BR table has a NULL on many rows. Rows with a null on the BRID column are being ignored in your syntax.

I am sure that there are no nulls as i have only created the other table with some set of records in it and that has no nulls. Even the query given above gives me the same number of rows i.e. 204.

Do the data types in the columns match?

Yes it does.

Is it a fixed-width type, like CHAR?
Post some sample values from BRID on the BR table, that you would expect to be NOT IN the other column.
Also, run the query with IN (instead of NOT IN) and see if those BRID values from the BR table are now listed yes/no.

Try something like :
select * from BR
where ISNULL(BRID,-1) NOT IN (select brid from SoxBillingRelationship)
order by BRID

Hi,
How about use the NOT EXISTS command instead to see if you get the correct results.
SELECT * FROM BR WHERE NOT EXISTS ( SELECT 1 FROM SoxBillingRelationship AS s WHERE s.BRID = BR.BRID )
Regards
Richard...

hope this helps.
SELECT *
FROM BR one
LEFT OUTER JOIN SoxBillingRelationship two on one.BRID = two.brid
WHERE two.brid IS NULL