Re: Simple SQL?

Jan,

thanks for your support.

    Even a simple IN in the where clause can cause some grief. Whenever I interview potential database developer candidates, I always ask the same question: rewrite the following query so that it produces the same result and has a better chance of performing faster (of course, I intentionally omit DDL, anticipating that the person asks if there are any unique/primary keys defined on the columns involved, and then I tell them there are none)

SELECT a.a1,a.a2
FROM a
WHERE a.a1 IN (SELECT b.b1 FROM b)

    The whole purpose is to find out if the person knows how to use EXISTS. You'd be amazed to learn how many people try to use straight joins and have difficulty understanding that in the absence of unique constraints on columns involved neither simple join nor (desperately trying to improve the situation) SELECT DISTINCT won't work.

Also, like I've said, take a look at the query that actually started this thread. Look how ugly all those join-based solutions get, and I do not think anyone has come up with a correct query yet.

Isaac

"Jan Hidders" <hidders_at_REMOVE.THIS.win.tue.nl> wrote in message news:9eagsv$qmh$1_at_news.tue.nl...
> Pierre G. Boutquin wrote:
> > "Isaac Blank" <izblank_at_yahoo.com> wrote in message
> > news:u9XN6.91$At.114013_at_news.pacbell.net...
> > > First, not every SELECT with subqueries can be converted to a
> > > join
> >
> > Hmmm. Are you sure your assertion is correct?
>
> I think he is. :-) Especially 'NOT IN' and 'NOT EXISTS' subqueries can
> usually not be expressed with joins.
>
> --
> Jan Hidders
>
Received on Sun Jul 22 2001 - 01:24:48 CEST