Australia photo & PC memo

Numeric Expressions

When numbers are used in an arithmetic or comparison operation, the result may depend on whether it involves exact or approximate values. For example:

   SELECT 1.1+2.2=3.3 as 'exact', 1.1E0+2.2E0=3.3E0 as 'approx.';

The column 'exact' returns true (1), but 'approx' returns false (0).


String Expressions

Double quotes can't be used if the ANSI_QUOTES SQL mode is enabled.
The || operator can be used for string concatenation if the PIPES_AS_CONCAT SQL mode is enabled.

The default character set and collation for literal strings depend on the values of the character_set_connection (the default is latin1) and collation_connection (the default is latin1_swedish_ci) system variables.

A non-binary string can be treated as a binary string by preceding it with the BINARY keyword. For example:

   SELECT BINARY 'Hello' = 'hello'; (returns FALSE(0))

If ether string in a comparison is binary, both strings are treated as binary.
String comparison rules also apply to ORDER BY, GROUP BY and DISTINCT operations.

LENGTH() returns the length of a string in bytes, whereas CHAR_LENGTH() returns the length in characters.
The UPPER() and LOWER() functions perform case conversion only if the argument is a non-binary string. To make the two functions perform case conversion for a binary string, convert it to a non-binary string like below:

   SELECT UPPER((CONVERT(BINARY 'AbCd' USING latin1));

MySQL allows use of LIKE with non-string values by converting non-string values to strings before performing the pattern match.

The unioned column type may be the smallest type that is guaranteed to be large enough to hold values from all tables.
To use ORDER BY and LIMIT clauses to the unioned table, surround each SELECT with parentheses and then add ORDER BY or LIMIT after the last parenthesis. Collumns named in such an ORDER BY should refer to columns in the first SELECT of the statement.

   (SELECT colA, colB FROM tableA)
   UNION
   (SELECT colC, colD FROM tableB)
   ORDER BY colA LIMIT 10;

WITH ROLLUP performs a "super-aggregate" operation.
WITH ROLLUP enables you to use a single query to get both the detailed results as well as the result of the given aggregate functions of all rows, as it is written in the SELECT clause on every single row selected.

MAX determines which value is the greatest based on the string collation (not by the string length).
In general, aggregate functions ignore NULL values except COUNT(*).

GROUP_CONCAT() uses "," as the default string separator.
To change the separator, use a SEPARATOR clause:
   SELECT GROUP_CONCAT(col1 SEPARATOR ' - ') AS ...
To change the concatenation order, add an ORDER BY clause:
   SELECT GROUP_CONCAT(col1 ORDER BY col1 DESC) AS ...
DISITNCT removes duplicates from the set of concatenated strings:
   SELECT GROUP_CONCAT(DISTINCT col1) AS ...