Problem solve Get help with specific problems with your technologies, process and projects.

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

Dig Deeper on Linux servers

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.

Please create a username to comment.