[thelist] Need a SQL pro

Matt Warden mwarden at gmail.com
Thu Aug 26 19:01:52 CDT 2004


On Thu, 26 Aug 2004 10:24:08 +1000, Ken Schaefer <ken.schaefer at gmail.com> wrote:
> How many times has it been pointed out that you should tell us what
> DBMS you are using?

Indeed. We have no idea if your DBMS supports subqueries. Like, if you
are using a less-than-recent version of MySQL, then your DBMS does not
support subqueries and you need to use an explicit SQL join.



> Something like (this is not tested, and I've typed it directly into
> gmail's web interface, so there are probably typos):
> 
> SELECT
>    a.AuctionID
> FROM
>    table1 AS a
> WHERE NOT EXISTS
>    (
>       SELECT
>          NULL
>       FROM
>          table2 AS b
>       WHERE
>          a.auctionID = b.auctionID
>    )

But, if this works, then your DBMS does support subqueries and you
should use the uncorrelated version:

SELECT auctionid
FROM table1
WHERE auctionid NOT IN
       (SELECT auction ID from table2);

Otherwise, the DBMS has to join table1 and table2, which is
computationally expensive and no better than the explicit SQL join
version.

Btw, this question has come up two or three times in the last few
weeks. Search the archives for a more developed discussion about the
"best" way to accomplish this.



-- 
Matt Warden
Miami University
Oxford, OH
http://mattwarden.com


This email proudly and graciously contributes to entropy.


More information about the thelist mailing list