Other Expected Improvements - MySQL

There are numerous other fixes, enhancements, and additions planned for future versions of MySQL. In this section, we’ll take a brief look at some of these, particularly those that are of interest with regard to optimizing database schemas and queries or speeding up general performance.

Full (Outer) Joins

Full joins may be supported in MySQL 5.0 or 5.1. Full joins, which return a NULL for any column in one table that isn’t matched in the other, are discussed in Chapter 5.

User Variables

User variables in MySQL 5.0 have already been changed in that the names are no longer case sensitive. Prior to this, @MYVAR and @myvar were treated as separate variables; beginning with version 5.0, they’ll be regarded as the same variable.

Another planned change is to allow user variables to be updated in UPDATE statements. For example, if this is done, the following would be possible:

UPDATE mytable SET @myvar := col1 + col2;

Currently, this can be done only in a SELECT query. It’s also likely that user variables will eventually be usable in statements having GROUP BY clauses, like so:

SELECT id, @count := COUNT(*) FROM products GROUP BY category_id;

SET Functions

Two new functions for working with SET columns are planned. These are ADD_TO_SET() and REMOVE_FROM_SET() . Suppose that we have a users table containing a SET column defined as

language SET('English,German,Spanish')

Were this function to be added, it would then be possible to update the column definition with something like this:

ALTER TABLE users
MODIFY language ADD_TO_SET('Portuguese', language);

and this:

ALTER TABLE users
MODIFY language REMOVE_FROM_SET('German', language);

without the need to reiterate all the elements in the set.

Group Functions

The SQL standard provides for three functions that allow you to find out very quickly whether or not any one, some, or all of a set of values is true:

• ANY() : This function returns TRUE if one and only one value in the set is true; if no values in the set are true or if more than one value is true, then the function returns FALSE.
  
• SOME() : This function returns TRUE if at least one value in a set of values is true.
  
• EVERY() : This function returns TRUE if and only if all values in a set are true.

Here are some examples, noting that the query SELECT price > 100 FROM products; will return a set of 1s and 0s, that is, TRUE and FALSE values:

# If this query returns 1 (TRUE) then we know only one product has a pric e
# greater than 100.00:
SELECT ANY(SELECT (price > 100) FROM products);
# If there are some products (possibly all of them, but at least one of
# them) having a price greater than 100.00, then this query will return 1
# (TRUE), otherwise it will return 0 (FALSE):
SELECT SOME(SELECT price > 100 FROM products);
# If *all* products in the table have prices greater than 100.00, then this
# query will return 1 (TRUE):
SELECT ALL(SELECT price > 100 FROM products);

According to the SQL standard, these functions should work only with sets of Boolean values; however, this could work in MySQL with other types of sets as well, since MySQL interprets 0 as FALSE and any other number or string value as TRUE.