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

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
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.



Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: