Re: Simple SQL?

Try with this data:

SELECT A1, A2 from A

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

0          0
0          0
0          1
1          0
2          0
2          2

SELECT B1 FROM B B1

"Mikito Harakiri" <nospam_at_newsranger.com> wrote in message news:SRcO6.2122$r4.113543_at_www.newsranger.com...
> 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
> ----------
> 0
> 3
>
> 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:56 CEST