Q

Finding the correct subquery for the "not in" condition

Sybase expert Mich Talebzadeh explains the correct subquery for the "not in" condition with a recommendation to use the "not exists" shortcut.

Can you please see if the following query is correct? If not, could you provide the right syntax for a subquery which contains the "not in" condition?

select bid from bp where bid not in (select bid from bp_hist)l

Let us first create table bp and insert a few rows into it:

  1. create table bp (bid int not null, bname varchar(30) null)
  2. go
  3. insert bp values (1, 'a')
  4. insert bp values (2,'b')
  5. insert bp values (3,'c')
  6. go

  7. (1 row affected)
    (1 row affected)
    (1 row affected)
  8. insert bp values (4,'d')
  9. go

  10. (1 row affected)

Now create a historical table bp_hist:

  1. select * into bp_hist from bp where bid <= 3
  2. go

  3. (3 rows affected)

Now try the first syntax:

  1. select bid from bp where bid not in (select bid from bp_hist)
  2. go

  3. bid
    -----------
    4

As you can see, the syntax is correct. However, a more efficient way of doing this query is to use the NOT EXISTS clause in the query as shown below:

  1. select bid from bp a where not exists(select 1 from bp_hist b where a.bid = b.bid)
  2. go

  3. bid
    -----------
    4
This was last published in December 2005

Dig Deeper on Open source databases

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataCenter

SearchServerVirtualization

SearchCloudComputing

SearchEnterpriseDesktop

Close