Re: Is this a bug or a limitation of the SQL language

The problems with

> |select * from emp
> |where salary between
> |(select salary from emp where ename = 'Larry')
> |and
> |(select salary from emp where ename = 'John')
 

partly stem from SQL being insufficiently _abstract_: there are unnecessarily many ways to express the same query.

After some experience with Prolog, I now resort to a predicate-calculus-like notation at the thinking-on-paper stage, e.g.

	answer( A, B, C, D, E, F, G, H) :-
		emp( _, 'Larry', _, _, _, S1, _, _),
		emp( _, 'John', _, _, _, S2, _, _),
		emp( A, B, C, D, E, F, G, H),
		between( S1, S2, F).

Now I'm not seriously proposing that as a query language, and maybe I'm just familiar with that idiom anyway, but it seems closer to the truth of what I want, with fewer premature evaluation decisions. (the less said about Prolog's efficiency in this case, the better :-)

A better example:

	answer( A) :-
		table1( A),
		not table2( A).

which has as least three SQL equivalents (and three plans under Oracle V6):

	select A from TABLE1
	minus select A from TABLE2;

		projection
		 \--minus
		     \--sort(unique)
		     |   \--table access(full) TABLE1
		     \--sort(unique)
		         \--table access(full) TABLE2

	select A from TABLE1 X
	where A not in ( select A from TABLE2 where A =X.A );

		filter
		 \--table access(full) TABLE1
		 \--table access(full) TABLE2

	select A from TABLE1
	where not exists ( select A from TABLE2 );

		filter
		 \--table access(full) TABLE1
		 \--index(range scan) TABLE2_INDEX

I want to be able to say WHAT I WANT, without having to decide HOW TO GET IT.

  __   __    Paul Singleton (Mr)           JANET: paul_at_uk.ac.keele.cs
 |__) (__    Computer Science Dept.        other: paul_at_cs.keele.ac.uk
 |  .  __).  Keele University, Newcastle,    tel: +44 (0)782 621111 x7355
          Staffs ST5 5BG, ENGLAND         fax: +44 (0)782 713082