Ask the Expert

Finding the correct subquery for the "not in" condition

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

    Requires Free Membership to View

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 first published in December 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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: