SQL Server Performance

Using NOT IN option in where clause

Discussion in 'SQL Server 2005 General Developer Questions' started by Varsha, Feb 18, 2008.

  1. Varsha New Member

    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!!
  2. Adriaan New Member

    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.
  3. Varsha New Member

    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.
  4. Adriaan New Member

    Do the data types in the columns match?
  5. Varsha New Member

    Yes it does.
  6. Adriaan New Member

    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.
  7. Jack Vamvas Member

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

    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...
  9. bhushanvinay New Member

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

Share This Page