How to get the difference between two lists?

[Frank D. Engel, Jr.]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

That depends on how you write the queries, how busy the server is, how 
much information needs to be shuttled between the client and the 
server, and various other factors.

Also, an ad hoc query against an SQL server needs to be parse, planned, 
and executed by the server each time it is sent.  A complex query may 
be better handled as a stored procedure, so that it can be pre-planned; 
this will cut down on execution time later on.  If you are dealing with 
a large amount of information, processing it on the server may cut down 
on time spent transmitting info across a slower network which will 
later be eliminated anyway.  Additionally, depending on the operations 
being performed, the server may be able to take advantage of indexes, 
etc. that the client will not have access to.

Now as far as using the SQL server to maintain lists, if you can store 
each entry as a separate row in a table, for example (based on 
PostgreSQL and untested; may or may not be the most efficient queries 
in some cases):

CREATE TABLE myLists
(
     listID INTEGER NOT NULL,
     listValue TEXT
);

CREATE INDEX ndxMyLists ON myLists (listID, listValue);



Now to retrieve a list in sorted order by value:

SELECT listValue FROM myLists WHERE (listID = 1) ORDER BY listValue;


To perform an intersection operation between two lists:

SELECT DISTINCT listValue FROM myLists WHERE (listID = 1) AND 
(listValue IN (SELECT listValue FROM myLists WHERE (listID = 2))))


To perform a union operation between two lists:

(SELECT listValue FROM myLists WHERE (listID = 1)) UNION (SELECT 
listValue FROM myLists WHERE (listID = 2))

**or**

SELECT DISTINCT listValue FROM myLists WHERE ((listID = 1) OR (listID = 
2))


To perform a set difference:

SELECT listValue FROM myLists WHERE (listID = 1) AND NOT (listValue IN 
(SELECT listValue FROM myLists WHERE (listID = 2)))

Note that this set difference is only different from my suggestion for 
intersection by a single NOT operator...


I missed the original post, what else did you need to be able to do?

BTW, one of the issues with using mySQL like this is its lack of 
support for stored procedures, or have they added this capability yet?  
Even pure SQL stored procedures would be fine for most of this stuff...

On Apr 4, 2005, at 4:11 PM, jbv wrote:

>>
>> I like this one, Ken.  In general, I'd reconsider the overall design.
>> Specifically, I'd think about queries that do what you want.  If the
>> set has to be a single value, then I'd consider a blob that represents
>> a bit set as the db representation.
>>
>
> well, I don't know...
> I've been heavily using Rev cgi and mySQL during the past
> few months, and I found out that extracting raw data from the
> db and processing them (sorting, comparing...) in Transcript
> is most of the time much faster than writing sophisticated
> SQL code...
> JB
>
> _______________________________________________
> use-revolution mailing list
> use-revolution at lists.runrev.com
> http://lists.runrev.com/mailman/listinfo/use-revolution
>
>
- -----------------------------------------------------------
Frank D. Engel, Jr.  <fde101 at fjrhome.net>

$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten 
Son, that whosoever believeth in him should not perish, but have 
everlasting life.
$
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (Darwin)

iD8DBQFCUaYR7aqtWrR9cZoRAviWAJ9SzSDGdg3JJNPdmCoOka8RVk7otwCfdB1I
5YwzbHHISddnuNB20nd3fR4=
=FSMG
-----END PGP SIGNATURE-----



___________________________________________________________
$0 Web Hosting with up to 200MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at www.doteasy.com