mySQL versus Standard SQL
This document highlights some of the differences between the SQL standard
and the SQL dialect of mySQL. Please share with us any additional differences
that you may find.
mySQL has a very limited form of HAVING clause.
Instead of evaluating the HAVING condition within each
group, mySQL treats HAVING as a selection on the output tuples.
Thus, you can only refer in the HAVING clause to attributes that appear
in the SELECT clause. Recent versions of mySQL allow you to refer to
aggregates in the SELECT clause by their formula [e.g., AVG(salary)]
rather than by an alias established in the SELECT clause by (e.g.)
mySQL does not support views.
However, unlike some other SQL implementations, mySQL does support fully
nested subqueries in the FROM clause. These subqueries can serve as
views in many situations, although they do not provide the ability of a
view to serve as a macro, with its definition reused in many queries.
The INTERSECT and EXCEPT operators of SQL are not supported in mySQL.
We suggest that instead, you use a join with all corresponding
attributes equated in place of an intersection.
For instance, to get the intersection of R(a,b) and S(a,b), write:
SELECT DISTINCT *
WHERE EXISTS (SELECT * FROM S WHERE R.a = S.a AND R.b = S.b);
To get the set difference, here is a similar approach using a subquery:
SELECT DISTINCT *
WHERE NOT EXISTS (SELECT * FROM S WHERE R.a = S.a AND R.b = S.b);
Note that both these expressions eliminate duplicates, but that is in
accordance with the SQL standard.
There are some discrepancies between the standard and how the ANY and
ALL operators are used in SQL. Only "=" seems to be handled completely
correctly. Here is a concrete example and the responses mySQL gives.
The query, about a Sells(bar, beer, price) relation, is:
ANY and ALL
SELECT * FROM Sells
WHERE price Op Quant(SELECT price FROM Sells);
Here, Op is one of the comparisons, and Quant is either ANY or ALL.
(1) mySQL gives an incorrect result, which in each of these cases is the
same as what the other of ANY and ALL gives.
(2) mySQL gives an incorrect result for both ANY and ALL. For each
operator, the result is the same independent of whether ANY or ALL is
used. For <, the result is several tuples with low, but different
prices, and for > it is the other tuples in the relation Sells, i.e.,
some of the tuples with high, but different prices.
This document was written originally by
Jeff Ullman in the Winter of 2004.