Re: Simple SQL?

Isaac Blank wrote:
>
> 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.

Oh dear. I don't know if I would pass. :-) I tend to see duplicates as something unrelational and usually assume that something is wrong if duplicates are seen as meaningful.

> 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.

No? What was wrong with Goran's solution? Or mine?

-- 
  Jan Hidders