Real Software Forums
http://forums.realsoftware.com/

Need help with exclusive query
http://forums.realsoftware.com/viewtopic.php?f=3&t=46344
Page 1 of 1

Author:  Dale [ Tue Dec 25, 2012 1:22 am ]
Post subject:  Need help with exclusive query

Well, either it's the season or old age creeping in on me again but I'm having trouble coming up with a sql query that I really should know.

Basically, I have two tables in the RealSqlDatabase. (There are actually more but only these two are involved at the moment.) One table has a complete set of data in one of its columns while the other table has a partial set in its column. What I am trying to remember is a simple sql query to retrieve the data that is in column 1 but is NOT in column 2.

Right now, I'm using a NOT IN clause mated with a sub-select but there's got to be a better way to do it. Isn't there?

Thanks.
- Dale

Author:  timhare [ Tue Dec 25, 2012 4:14 am ]
Post subject:  Re: Need help with exclusive query

Use NOT EXISTS with a subquery. Or use a join and select for WHERE table2.column1 IS NULL.

Author:  DaveS [ Tue Dec 25, 2012 9:43 am ]
Post subject:  Re: Need help with exclusive query

SELECT *
FROM tablea A
WHERE NOT EXISTS(SELECT 8
FROM table B
WHERE a.myfield=b.myfield)

Author:  Dale [ Tue Dec 25, 2012 3:35 pm ]
Post subject:  Re: Need help with exclusive query

Thanks. That's basically what I'm doing except that I'm using NOT IN rather than NOT EXISTS. Is there a reason to prefer one over the other?

- Dale

Author:  timhare [ Tue Dec 25, 2012 11:40 pm ]
Post subject:  Re: Need help with exclusive query

NOT IN has to return a (potentially) large result and then search it for a match. NOT EXISTS just searches, so it only does half the work. Plus, it can quit early if it finds a match.

Of course, just about everything will be cached, so the real difference lies in whether the column in table B is indexed. Then it makes a difference in performance. Otherwise, it's probably a wash.

Author:  DaveS [ Wed Dec 26, 2012 1:05 am ]
Post subject:  Re: Need help with exclusive query

and "SELECT 8" vs "SELECT *" reduces the caching to almost nothing.. because the optimizer creates a very small return vector, where "*" would create a buffer the size required to manipulate entire records.

and why "8" everyone asks? why not "1" or "2"... it makes no difference... it just has to be "something"

but "8" is most common because it is the "unshifted" key for "*" :)

Author:  Dale [ Wed Dec 26, 2012 3:23 pm ]
Post subject:  Re: Need help with exclusive query

Thanks, guys. In this case, the records are fairly small and there are only a couple of thousand in each table so space and performance are not really an issue. I did change it from NOT IN to NOT EXISTS since I assume it doesn't like to do extra work any more than I do. :)

- Dale

Page 1 of 1 All times are UTC - 5 hours
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/