Re: Simple SQL?

You are not saying that sql with IN and EXIST constructs is more powerful than without them, arent't you? Because if I use joins with MINUS operation I cover 'NOT IN' and 'NOT EXISTS' perfectly well. And I don't think I undertand Issak's comment why unique key is important. Here is my set of data:

SELECT A1, A2 from A

A1 A2
---------- ----------

0          0
0          1
1          0
2          0
2          2

SELECT B1 FROM B B1

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

A1 A2
---------- ----------

0          0
0          1

Here natural join works just fine:

SELECT a.a1,a.a2
FROM a,b
WHERE a.a1 = b.b1

A1 A2
---------- ----------

0          0
0          1

'NOT IN' example:

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

A1 A2
---------- ----------

1          0
2          0
2          2

SELECT a.a1,a.a2
FROM a
minus
SELECT a.a1,a.a2
FROM a,b
WHERE a.a1 = b.b1

A1 A2
---------- ----------

1          0
2          0
2          2

Some counterexample data, please.

BTW, somebody already mentioned that putting correlated subquery into 'select' part of the clause makes it somehow less relational. C.J.Date widely uses this construct in his campaign against 'GROUP BY':

select deptno,
(select sum(sal) from emp e where e.deptno = deptno) from emp

This construct is goofy, however, as you can't put arbitrary 'select' subquery. It works much like procedural 'if': whenever inner select happens to return a single row and single column, dbms converts it into a value.

On the other hand, this is the only way to express 'GROUP BY' that I know (in case of general aggregates, of course, as 'MAX', for example, can be expressed through correlated subquery in 'where' clause, as Jan demonstrated in the other thread).

In article <sfbO6.11$EM6.19342_at_news.pacbell.net>, Isaac Blank says...
>
>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:50 CEST