code: Can't Copy and Paste this? Click here for a copy-and-paste friendly version of this code!
Terms of Agreement:
By using this code, you agree to the following terms...
You may use
this code in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
You MAY NOT redistribute this code (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
You may link to this code from another website, but ONLY if it is not wrapped in a frame.
You will abide by any additional copyright restrictions which the author may have placed in the code or code's description.
--**************************************
-- Name: aNother way to do NOT IN queries
-- Description:This code is a transformation for NOT IN queries
-- By: Eli Leiba
--
-- Assumes:instead of using a "Not in" that works slowly
I used an Outer join and a IS null AND not is NULL
conditions to filter the output
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=397&lngWId=5--for details.--**************************************
-- This query returns all the site ids that
-- are in project and not in site
select site_id from
project where site_id not in (select site_id from site);
--This is the Transformation I used (works faster)
select a.site_id
from project a, site s
where a.site_id = s.site_id(+) and
s.site_id is nulland
a.site_id is not null ;
This is good for all to know, but I would also suggest that when you run a query similar to this that you check both ways to see which prouces the least server overhead. (If this comment was disrespectful, please report it.)
I'm not much on sql, but wondered if you used to Not In to compare a value from another table. I don't believe this would work. In my example I use not in to see if a value is in another table, ie; account number.
Regards Joe (If this comment was disrespectful, please report it.)
Yes, the 'not exists' is faster as it will only hit the database once, while the NOT IN checks each record. I would assume the not null methid checks each record as well.
(If this comment was disrespectful, please report it.)
I believe that these two solutions are even more better then all the proposed onces.
SELECT site_id FROM project a WHERE NOT EXISTS (SELECT 'x' FROM site b WHERE b.site_id=a.site_id)
OR
SELECT site_id FROM project a WHERE NOT IN (SELECT site_id FROM site b WHERE b.site_id=a.site_id)
(If this comment was disrespectful, please report it.)
10/17/2002 9:14:09 AM:
Yes the 2 solutions from Sushil Daga are as good as the outer join proposed by Eli Leiba if it exists a index on b.site_id. Otherwise the Leiba solution is the best. (If this comment was disrespectful, please report it.)
For those of you who have the privilege of working with db2/400, V5R2 (but is it really of privilege ?), check out the LEFT EXCEPTION JOIN syntax.
BTW, I don't know if others (microsoft, oracle, etc.) support this syntax.
CVM. (If this comment was disrespectful, please report it.)
12/26/2004 7:22:27 AM:
Im not much into sql. Im a freshe to this. I tried some queries and on the default tables(emp and dept) of oracle. Ur query is nice. but u can use "select deptno from dept minus (select deptno from emp) [default tables of oracle] (If this comment was disrespectful, please report it.)
hi when i try using the above code of urs, i get the following error:
Server: Msg 170, Level 15, State 1, Line 3 Line 3: Incorrect syntax near ')'.
what could be the reason...
S. Saravana Perumal (If this comment was disrespectful, please report it.)
Add Your Feedback
Your feedback will be posted below and an email sent to
the author. Please remember that the author was kind enough to
share this with you, so any criticisms must be stated politely, or they
will be deleted. (For feedback not related to this particular code, please
click here instead.)
By 9 Users
2/4/2002 6:46:31 AM: 
