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.

HAVING Clauses
Views
Intersection and Set-Difference
ANY and ALL

HAVING Clauses

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.) AVG(salary)AS avgSalary.

Views

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.

Intersection and Set-Difference

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 *
FROM R
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 *
FROM R
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.

ANY and ALL

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:

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.

Op	ANY	ALL
>=	(1)	Correct
<=	Correct	(1)
=	Correct	Correct
<>	Correct	(1)
<	(2)	(2)
>	(2)	(2)

(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.