one part of a UNION (I'll have a look at the code later to check if this is the case). Really,
a UNION should consist of two or more SELECTs, so this is not the expected behaviour. The way this REALLY
should be interpreted would be as a subquery followed by an ORDER BY. But as 4.0 doesn't have
subqueries, this is not an option. But in 4.1 it is. I just tested it in 4.1, and rightly so, duplicates
are NOT removed from this:
(SELECT ...) ORDER BY ...;
There is another way to write this query, which is like this:
SELECT av.c1 FROM (SELECT c1 FROM t1) av ORDER BY av.c1;
In this case "av" is an alias for the subquery (this is sometimes called an anonymous view, which is why
I give it the alias "av"). An then, if we add a LIMIT clause to this, we get:
SELECT av.c1 FROM (SELECT c1 FROM t1 LIMIT 3) av ORDER BY av.c1;
The first construct, without the leading SELECT, is also, as far as I can interpret SQL-92/99, a standard SQL construct. But I
think there might be a debate on this. The latter two construct ARE clearly SQL-92 compatible though (with the obvious
exception of the LIMIT clause of course).
And by the way, in a UNION, there is no need to put parenteses around the unioned queries in the general case. So
(SELECT .....) UNION (SELECT....) [ORDER BY ....]
Is the same as
SELECT ..... UNION SELECT.... [ORDER BY ....]
I say in the general case, as there are cases when the parenteses are required, in particular when the individual
SELECT is followed by a MySQL specific construct or keyword. If I remember things correctly for example,
this
(SELECT .... ORDER BY...) UNION (SELECT... ORDER BY...) [ORDER BY ....]
will require the parenteseses, but this construct is a MySQL extension to the standard (an ORDER BY
is not part of a "query specification" which is this form of a subquery). And yes, I know that the above query is
a bit meaningless :-)
Anyway, to summarize my view on this. An alternative way to achieve the requested operation is (which is fully SQL-92/99
except for the LIMIT clause):
SELECT av.c1 FROM (SELECT c1 FROM t1 LIMIT 3) av ORDER BY av.c1;
But this is available in 4.1 only. In 4.0 you can write:
(SELECT c1 FROM t1 LIMIT 3) ORDER BY c1;
Although this later syntax does not seem to work properly in 4.1.1 right now. (the LIMIT clause in this case has no
effect, I get all rows back. Also note that the syntax doesn't allow for an alias for the anonymous view in this case).
And neither of these constructs has anything to to with a UNION or a UNION ALL, really, except the latter is
interpreted as being part of something like that in 4.0 (or so it seems).
And now I close the SQL-92 standard docs. It is saturday after all and a beautiful day outside!
Anders Karlsson
--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED])
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer
/_/ /_/\_, /___/\___\_\___/ Stockholm
<___/ www.mysql.com Cellphone: +46 708 608121Jigal van Hemert wrote:
I find by experiment that (select * from FOO order by a desc limit 10) order by a; removes duplicates, but, if I drop the second order clause, (select * from FOO order by a desc limit 10); duplicates are retained.
Why is the first a union, but not the second? Just curious.
On http://dev.mysql.com/doc/mysql/en/UNION.html you can see a comment by Keith Ivey about this. Apparantly it's caused by the fact that (SELECT .....) UNION (SELECT....) [ORDER BY ....] is the syntax for a UNION. If you leave the first table out, you're left with: (SELECT ....) ORDER BY... The fact that there are parentheses and an ORDER BY outside these parentheses seems to make it a UNION.
If you leave out the ORDER BY..., it's just a query with parentheses around it.
The manual states that if you do not use the keyword ALL with the UNION, it's considered to be DISTINCT. So, leaving out the UNION keyword entirely automatically makes it using DISTINCT.
Regards, Jigal.
From: "Keith C. Ivey" <[EMAIL PROTECTED]>
DuBois: This one-query union syntax doesn't allow you to use the ALL
keyword after UNION (since the UNION keyword isn't even there). That
means it will always eliminate duplicate rows (like DISTINCT). That
hasn't come up when I've used it, since I've never been selecting
result sets that could contain duplicate rows, but it's something to
keep in mind.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
