Re: Select In From Clause

Jonathan Leffler wrote in article <5opfk1$cn4@cssun.mathcs.emory.edu>... >>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> > I tried the following as a single SQL statement using, DBACCESS, ODBCTE32.EXE, and an app using RogueWave. The only one in which it worked was DBACCESS ( the other two reported "The specified table (t0) is not in the database" ). Anyone know how to work around this? SELECT JOB_ID AS C0 FROM JOBS INTO TEMP T0; SELECT JOB_ID AS C0 FROM EMPLOYEE INTO TEMP T1; SELECT T0.C0, T1.C0 FROM T0, T1 WHERE T0.C0 = T1.C0;