Re: Select In From Clause

>From: Jeremy Rickard <Jeremy@jbdr.demon.co.uk> >Date: Tue, 24 Jun 1997 19:28:34 +0100 >X-Informix-List-Id: <news.39612> > >Chez David <dmitchell@SagentTech.com> writes: > >>Anyone know if Informix ( 7.x ) supports SQL of this form? SQL Server and >>Oracle seem to. >> >>SELECT >>T0.C0, T1.C0 >>FROM >>( SELECT JOB_ID AS C0 FROM JOBS ) T0, >>( SELECT JOB_ID AS C0 FROM EMPLOYEE ) T1 >>WHERE >>T0.C0 = T1.C0 As far as I know, the answer is that Informix does not support such queries. >Does anyone want it to?! Yes, I do. For one thing, this is necessary to claim compliance with SQL-92 at other than the entry level. And, for another, it opens the way to some more complex queries, using operators such as NATURAL JOIN, JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, UNION, INTERSECT, EXCEPT, CROSS JOIN, etc. Of these, you can simulate LEFT or RIGHT OUTER JOIN in Informix, but not the FULL OUTER JOIN. You cannot do the UNION, EXCEPT or INTERSECT stuff. CROSS JOIN is a Cartesian Product by any other name. It also opens the way to more powerful views, potentially -- how many times have UNION views been requested? >Personally, I think this could lead to some very ugly SQL queries. Can >you think of any examples that couldn't be re-written as a "normal" >query without looking at least as elegant? Well, to give an example from 'Understanding the New SQL: A Complete Guide' by J Melton and A R Simon (Morgan Kaufmann 1993, ISBN 1-55860-245-3), you can do: SELECT * FROM t1 UNION t2; This is a lot neater than: SELECT * FROM t1 UNION SELECT * FROM t2; And there are a whole pile of other things you can do, far more complex than what I've just shown. For instance, either of t1 or t2 could itself be a complex SELECT statement. Not having to use temporary tables should be advantageous to the optimizer too -- in general, the bigger the chunks of work you give it to do, the easier it is for it to work magic and optimize the performance. The hypothetical optimizer might decide to create some sort of temp table internally, but it is better to let it decide that than to tie its hands and force it to use a temporary table. >If this is really needed then I think it would be "prettier" to define >T0 and T1 as temporary tables (using WITH or whatever construct >is/becomes the standard for this). > >What's the general opinion on this form? Well, I don't know about the general opinion, but I'm in favour of it. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>