Retrieving Data

Retrieving data from Crate is done by using a SQL SELECT statement. The response to a SELECT query contains the column names of the result, the actual result rows as a two-dimensional array of values, the row count and the duration.

A simple select:

cr> select name, position from locations order by id limit 2;
+-------------------+----------+
| name              | position |
+-------------------+----------+
| North West Ripple | 1        |
| Arkintoofle Minor | 3        |
+-------------------+----------+
SELECT 2 rows in set (... sec)

If the ‘*’ operator is used, all columns defined in the schema are returned for each row:

cr> select * from locations order by id limit 1 offset 1;
+----...-+--------------...-+----+-------------+--...-+--...-+----------+------------------...-+
| date   | description      | id | information | kind | name | position | race                 |
+----...-+--------------...-+----+-------------+--...-+--...-+----------+------------------...-+
| 308... | Motivated by ... | 10 |        NULL | P... | A... | 3        | {"description": ...} |
+----...-+--------------...-+----+-------------+--...-+--...-+----------+------------------...-+
SELECT 1 row in set (... sec)

Aliases can be used to change the output name of the columns:

cr> select name as n
... from locations
... where name = 'North West Ripple';
+-------------------+
| n                 |
+-------------------+
| North West Ripple |
+-------------------+
SELECT 1 row in set (... sec)

FROM Clause

The FROM clause is used to reference the relation this select query is based upon. Can be a single table, many tables, table JOIN or Sub Select

A table is referenced by schema and table name and can optionally be aliased. If the table t is only referenced by table name, Crate assumes the table doc.t was meant. Schemas that were newly created using CREATE TABLE must be referenced explicitly.

The two following queries are equivalent:

cr> select name, position from locations
... order by name desc nulls last limit 2;
+-------------------+----------+
| name              | position |
+-------------------+----------+
| Outer Eastern Rim |        2 |
| North West Ripple |        1 |
+-------------------+----------+
SELECT 2 rows in set (... sec)
cr> select doc.locations.name as n, position from doc.locations
... order by name desc nulls last limit 2;
+-------------------+----------+
| n                 | position |
+-------------------+----------+
| Outer Eastern Rim |        2 |
| North West Ripple |        1 |
+-------------------+----------+
SELECT 2 rows in set (... sec)

A table can be aliased for the sake of brevity too:

cr> select name from doc.locations as l
... where l.name = 'Outer Eastern Rim';
+-------------------+
| name              |
+-------------------+
| Outer Eastern Rim |
+-------------------+
SELECT 1 row in set (... sec)

Joins

Note

Crate currently supports only a limited set of JOINs. See the Joins for current state.

DISTINCT Clause

If DISTINCT is specified, one unique row is kept. All other duplicate rows are removed from the result set:

cr> select distinct date from locations order by date;
+---------------+
| date          |
+---------------+
| 308534400000  |
| 1367366400000 |
| 1373932800000 |
+---------------+
SELECT 3 rows in set (... sec)

WHERE Clause

A simple where clause example using an equality operator:

cr> select description from locations where id = '1';
+---------------------------------------...--------------------------------------+
| description                                                                    |
+---------------------------------------...--------------------------------------+
| Relative to life on NowWhat, living on... a factor of about seventeen million. |
+---------------------------------------...--------------------------------------+
SELECT 1 row in set (... sec)

Comparison Operators

Usual comparison operators to be used in the where clause are available for all simple data types:

Operator Description
< less than
> greater than
<= less than or equal to
>= greater than or equal to
= equal
<> not equal
!= not equal - same as <>
like matches a part of the given value
~ regular expression match
!~ negated regular expression match
between a BETWEEN x and y: shortcut for a >= x AND a <= y

For strings a lexicographical comparison is performed based on the Lucene TermRangeQuery:

cr> select name from locations where name > 'Argabuthon' order by name;
+------------------------------------+
| name                               |
+------------------------------------+
| Arkintoofle Minor                  |
| Bartledan                          |
| Galactic Sector QQ7 Active J Gamma |
| North West Ripple                  |
| Outer Eastern Rim                  |
+------------------------------------+
SELECT 5 rows in set (... sec)

For details please refer to the Apache Lucene site.

Number and date field comparison behave as expected from standard SQL. The following example uses one of the supported ISO date formats:

cr> select date, position from locations where date <= '1979-10-12' and
... position < 3 order by position;
+--------------+----------+
| date         | position |
+--------------+----------+
| 308534400000 | 1        |
| 308534400000 | 2        |
+--------------+----------+
SELECT 2 rows in set (... sec)

For a detailed explanation of the supported ISO date formats please refer to the joda date_optional_time site.

For custom date types, or defined date formats in the object mapping the corresponding format should be used for a comparison. Otherwise the operation may fail.

Predicates

Predicates are expressions that evaluate to boolean.

While scalar functions that evaluate to boolean can be used in place of a predicate, a predicate cannot be used everywhere, e.g. in the result columns of a select statement. The semantics of a predicate usually differ from function semantics.

e.g. A function is resolved by its name and the type of its arguments. A reference can be interchanged with the value it evaluates to (given as a literal) in a function argument and the function resolves to the same value. Predicates can bear different semantics, e.g. enforce the usage of a column reference.

The following predicates extend the range of possible conditions to use in the where clause.

Predicate Description
IS NOT NULL field is not null and not missing
IS NULL field is null or missing
MATCH Predicate Perform a search on an indexed column. See Fulltext Search or Geo Search for usage.

Note

Not all predicates can utilize the index efficiently. For example is null can’t use the index at all and will therefor perform worse than other operators.

Regular Expressions

Operators for matching using regular expressions.

Operator Description Example
~ Matches regular expression, case sensitive
'foo' ~ '.*foo.*'
~* Matches regular expression, case insensitive
'Foo' ~* '.*foo.*'
!~ Does not match regular expression, case sensitive
'Foo' !~ '.*foo.*'
!~* Does not match regular expression, case insensitive
'foo' !~* '.*bar.*'

The ~ operator can be used to match a string against a regular expression. It returns true if the string matches the pattern, false if not, and NULL if string is NULL.

To negate the matching, use the optional ! prefix. The operator returns true if the string does not match the pattern, false otherwise.

The regular expression pattern is implicitly anchored, that means that the whole string must match, not a single subsequence. All unicode characters are allowed.

If using PCRE features in the regular expression pattern, the operator uses the regular expression engine of the Java standard library java.util.regex.

If not using PCRE features in the regular expression pattern, the operator uses Lucene Regular Expressions, which are optimized for fast regular expression matching on Lucene terms.

Lucene Regular Expressions are basically POSIX Extended Regular Expressions without the character classes and with some extensions, like a metacharacter # for the empty string or ~ for negation and others. By default all Lucene extensions are enabled. See the Lucene documentation for more details.

Note

Since case-insensitive matching using ~* or !~* implicitly uses the regular expression engine of the Java standard library, features of Lucene Regular Expressions do not work there.

Examples:

cr> select name from locations where name ~ '([A-Z][a-z0-9]+)+'
... order by name;
+------------+
| name       |
+------------+
| Aldebaran  |
| Algol      |
| Altair     |
| Argabuthon |
| Bartledan  |
+------------+
SELECT 5 rows in set (... sec)
cr> select 'matches' from sys.cluster where
... 'gcc --std=c99 -Wall source.c' ~ '[A-Za-z0-9]+( (-|--)[A-Za-z0-9]+)*( [^ ]+)*';
+-----------+
| 'matches' |
+-----------+
| matches   |
+-----------+
SELECT 1 row in set (... sec)
cr> select 'no_match' from sys.cluster where 'foobaz' !~ '(foo)?(bar)$';
+------------+
| 'no_match' |
+------------+
| no_match   |
+------------+
SELECT 1 row in set (... sec)

LIKE

Crate supports the LIKE operator. This operator can be used to query for rows where only part of a columns value should match something. For example to get all locations where the name starts with ‘Ar’ the following query can be used:

cr> select name from locations where name like 'Ar%' order by name asc;
+-------------------+
| name              |
+-------------------+
| Argabuthon        |
| Arkintoofle Minor |
+-------------------+
SELECT 2 rows in set (... sec)

The following wildcard operators are available:

% A substitute for zero or more characters
_ A substitute for a single character

The wildcard operators may be used at any point in the string literal. For example a more complicated like clause could look like this:

cr> select name from locations where name like '_r%a%' order by name asc;
+------------+
| name       |
+------------+
| Argabuthon |
+------------+
SELECT 1 row in set (... sec)

In order so search for the wildcard characters themselves it is possible to escape them using a backslash:

cr> select description from locations
... where description like '%\%' order by description asc;
+-------------------------+
| description             |
+-------------------------+
| The end of the Galaxy.% |
+-------------------------+
SELECT 1 row in set (... sec)

Note

Queries with a like clause can be quite slow. Especially if the like clause starts with a wildcard character. Because in that case CRATE has to iterate over all rows and can’t utilize the index. For better performance consider using a fulltext index.

NOT

NOT negates a boolean expression:

[ NOT ] boolean_expression

The result type is boolean.

expression result
true false
false true
null true

Note

This is not compliant with the SQL standard and doesn’t adhere to the three-valued-logic. According to the standard NOT ( NULL ) should actually return NULL.

IN

Crate also supports the binary operator IN, which allows you to verify the membership of left-hand operand in a right-hand set of expressions. Returns true if any evaluated expression value from a right-hand set equals left-hand operand. Returns false otherwise:

cr> select name, kind from locations
... where (kind in ('Star System', 'Planet'))  order by name asc;
 +---------------------+-------------+
 | name                | kind        |
 +---------------------+-------------+
 |                     | Planet      |
 | Aldebaran           | Star System |
 | Algol               | Star System |
 | Allosimanius Syneca | Planet      |
 | Alpha Centauri      | Star System |
 | Altair              | Star System |
 | Argabuthon          | Planet      |
 | Arkintoofle Minor   | Planet      |
 | Bartledan           | Planet      |
 +---------------------+-------------+
 SELECT 9 rows in set (... sec)

IS NULL

Returns TRUE if expr evaluates to NULL. Given a column reference it returns TRUE if the field contains NULL or is missing.

Use this predicate to check for NULL values as SQL’s three-valued logic does always return NULL when comparing NULL.

expr:expression of one of the supported Data Types supported by crate.
cr> select name from locations where race is null order by name;
+------------------------------------+
| name                               |
+------------------------------------+
|                                    |
| Aldebaran                          |
| Algol                              |
| Allosimanius Syneca                |
| Alpha Centauri                     |
| Altair                             |
| Argabuthon                         |
| Galactic Sector QQ7 Active J Gamma |
| North West Ripple                  |
| Outer Eastern Rim                  |
| NULL                               |
+------------------------------------+
SELECT 11 rows in set (... sec)
cr> select count(*) from locations where name is null;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
SELECT 1 row in set (... sec)

IS NOT NULL

Returns TRUE if expr does not evaluate to NULL. Additionally, for column references it returns FALSE if the column does not exist.

Use this predicate to check for non-NULL values as SQL’s three-valued logic does always return NULL when comparing NULL.

expr:expression of one of the supported Data Types supported by crate.
cr> select name from locations where race['interests'] is not null;
+-------------------+
| name              |
+-------------------+
| Arkintoofle Minor |
| Bartledan         |
+-------------------+
SELECT 2 rows in set (... sec)
cr> select count(*) from locations where name is not null;
+----------+
| count(*) |
+----------+
|       12 |
+----------+
SELECT 1 row in set (... sec)

ANY (array)

The ANY (or SOME) operator allows to search for elements within arrays. This allows to query for rows where an element of an array is, for example, equal to or greater than some expression.

The following example returns any row where the array race['interests'] contains an element ‘netball’:

cr> select race['name'], race['interests'] from locations
... where 'netball' = ANY(race['interests']);
+----------------+-----------------------------------------+
| race['name']   | race['interests']                       |
+----------------+-----------------------------------------+
| Bartledannians | ["netball", "books with 100.000 words"] |
+----------------+-----------------------------------------+
SELECT 1 row in set (... sec)
cr> select race['name'], race['interests'] from locations
... where 'books%' LIKE ANY(race['interests']);
+----------------+-----------------------------------------+
| race['name']   | race['interests']                       |
+----------------+-----------------------------------------+
| Bartledannians | ["netball", "books with 100.000 words"] |
+----------------+-----------------------------------------+
SELECT 1 row in set (... sec)

It can also be used on arrays:

cr> select name, race['interests'] from locations
... where name = ANY(ARRAY['Bartledan', 'Algol'])
... order by name asc;
+-----------+-----------------------------------------+
| name      | race['interests']                       |
+-----------+-----------------------------------------+
| Algol     | NULL                                    |
| Bartledan | ["netball", "books with 100.000 words"] |
+-----------+-----------------------------------------+
SELECT 2 rows in set (... sec)

This way it can be used as a shortcut for name = 'Bartledan' OR name = 'Algol' or any other ANY comparison.

Syntax of the ANY Operator:

expression operator ANY | SOME (array)

The ANY operator allows to apply an operator on the elements of an array. The expression is evaluated and compared to each element of the array using the operator. The comparison must yield a boolean result.

  • The result of ANY is true if any comparison returns true.
  • The result of ANY is false if no comparison returns true, or an array contains no elements.
  • The result of ANY is NULL if either the expression or the array is null. The result is also null, if no true comparison is obtained and any element of the array is null.

Note

The following is not supported by the ANY operator:

  • ‘is null’ and ‘is not null’ as operator.
  • Arrays of type object.
  • Objects as expressions.

Negating ANY

One important thing to notice when using ANY is that negating the ANY operator does not behave as negating normal comparison operators.

The following query can be translated to get all rows where race[‘interests’] has at least one element that equals ‘netball’:

cr> select race['name'], race['interests'] from locations
... where 'netball' = ANY(race['interests']);
+----------------+-----------------------------------------+
| race['name']   | race['interests']                       |
+----------------+-----------------------------------------+
| Bartledannians | ["netball", "books with 100.000 words"] |
+----------------+-----------------------------------------+
SELECT 1 row in set (... sec)

The following query using the negated operator != can be translated to get all rows where race[‘interests’] has at least one element that does not equal ‘netball’. As you see, the result is the same in this case:

cr> select race['name'], race['interests'] from locations
... where 'netball' != ANY(race['interests']);
+----------------+-----------------------------------------+
| race['name']   | race['interests']                       |
+----------------+-----------------------------------------+
| Minories       | ["baseball", "short stories"]           |
| Bartledannians | ["netball", "books with 100.000 words"] |
+----------------+-----------------------------------------+
SELECT 2 rows in set (... sec)

Negating the = query from above is totally different. It can be translated to get all rows where race[‘interests’] has no value that equals ‘netball’:

cr> select race['name'], race['interests'] from locations
... where not 'netball' = ANY(race['interests']) order by race['name'];
+--------------+-------------------------------+
| race['name'] | race['interests']             |
+--------------+-------------------------------+
| Minories     | ["baseball", "short stories"] |
+--------------+-------------------------------+
SELECT 1 row in set (... sec)

The same behaviour (though different comparison operations involved) holds true for operators

  • LIKE and NOT LIKE
  • all other comparison operators (excluding IS NULL and IS NOT NULL)

Limits

As unlimited SELECT queries could break your cluster if the matching rows exceed your node’s RAM, SELECT statements are limited by default to 10000 rows. You can expand this limit by using an explicit LIMIT-clause. But you are encouraged to make use of a windowing using LIMIT and OFFSET to iterate through all the results of a potentially large resultset instead of expanding the default limit.

When using the PostgreSQL wire protocol, there is no implicit 10000 row limit on SELECT statements.

Inner/Nested Objects

Crate supports an object data type, used for simple storing a whole object into a column and it’s even possible to select and query for properties of such objects.

Select a property of an inner object:

cr> select name, race['name'] from locations where name = 'Bartledan';
+-----------+----------------+
| name      | race['name']   |
+-----------+----------------+
| Bartledan | Bartledannians |
+-----------+----------------+
SELECT 1 row in set (... sec)

Query for a property of an inner object:

cr> select name, race['name'] from locations
... where race['name'] = 'Bartledannians';
+-----------+----------------+
| name      | race['name']   |
+-----------+----------------+
| Bartledan | Bartledannians |
+-----------+----------------+
SELECT 1 row in set (... sec)

Inserting objects:

cr> insert into locations (id, name, position, kind, race)
... values ('DO', 'Dornbirn', 14, 'City', {name='Vorarlberger',
...     description = 'Very nice people with a strange accent',
...     interests = ['mountains', 'cheese', 'enzian']}
... );
INSERT OK, 1 row affected (... sec)

Object Arrays

Arrays in crate can only be queried for containment using the ANY (array) operator. One exception are object arrays. As you can access fields of Inner/Nested Objects using subscript expressions, you can access fields of object arrays. As an object array is no object, you won’t get the value for a single field, but an array of all the values of that field for all objects in that object array.

Examples:

cr> select name, information['population'] from locations
... where information['population'] is not null
... order by name;
+-------------------+---------------------------+
| name              | information['population'] |
+-------------------+---------------------------+
| North West Ripple | [12, 42]                  |
| Outer Eastern Rim | [5673745846]              |
+-------------------+---------------------------+
SELECT 2 rows in set (... sec)
cr> select information from locations
... where information['population'] is not null
... order by name;
+----------------------------------------------------...-----------------------+
| information                                                                  |
+----------------------------------------------------...-----------------------+
| [{"evolution_level": 4, "population": 12}, {"evolu...": 42, "popul...": 42}] |
| [{"evolution_level": 2, "population": 5673745846}]                           |
+---------------------------------------------------...------------------------+
SELECT 2 rows in set (... sec)
cr> insert into locations (id, name, position, kind, information)
... values (
...   'B', 'Berlin', 15, 'City',
...   [{evolution_level=6, population=3600001},
...   {evolution_level=42, population=1}]
... );
INSERT OK, 1 row affected (... sec)
cr> refresh table locations;
REFRESH OK, 1 row affected (... sec)
cr> select name from locations where 4 < ANY (information['evolution_level'])
... order by name;
+-------------------+
| name              |
+-------------------+
| Berlin            |
| North West Ripple |
+-------------------+
SELECT 2 rows in set (... sec)

Selecting Array Elements

Array elements can be selected directly using a integer value greater than or equal to 1. The maximum supported array index is 2147483648. Using an index greater than the actual array size results in a NULL value.

cr> select name, information[1]['population'] as population from locations
... where information['population'] is not null
... order by name;
+-------------------+------------+
| name              | population |
+-------------------+------------+
| Berlin            |    3600001 |
| North West Ripple |         12 |
| Outer Eastern Rim | 5673745846 |
+-------------------+------------+
SELECT 3 rows in set (... sec)

Note

Only 1 array notation inside a subscript expression is supported, e.g. following won’t work:

select information[1][tags][1] from locations;

Data Aggregation

Crate supports aggregations by using the aggregation functions listed below on SELECT statements.

Aggregation works on all the rows that match a query or on all matching rows in every distinct group of a GROUP BY statement. Aggregating SELECT statements without GROUP BY will always return one row, as they do an aggregation operation on the matching rows as one group.

See also

Aggregation

Name Arguments Description Return Type
ARBITRARY column name of a primitive typed column (all but object) Returns an undefined value of all the values in the argument column. Can be NULL. the input column type or NULL if some value of the matching rows in that column is NULL
AVG / MEAN column name of a numeric or timestamp column Returns the arithmetic mean of the values in the argument column. NULL-values are ignored. double or NULL if all values of all matching rows in that column are NULL
COUNT(*) star as parameter or as constant Counts the number of rows that match the query. long
COUNT column name Counts the number of rows that contain a non NULL value for the given column. long
COUNT(DISTINCT col) column name Counts the number of distinct values for the given column that are not NULL. long
GEOMETRIC_MEAN column name of a numeric or timestamp column Computes the geometric mean for positive numbers. double or NULL if all values of all matching rows in that are NULL or if a value is negative.
MIN column name of a numeric, timestamp or string column Returns the smallest of the values in the argument column in case of strings this means the lexicographically smallest. NULL-values are ignored the input column type or NULL if all values in that matching rows in that column are NULL
MAX column name of a numeric, timestamp or string column Returns the biggest of the values in the argument column in case of strings this means the lexicographically biggest. NULL-values are ignored the input column type or NULL if all values of all matching rows in that column are NULL
STDDEV column name of a numeric or timestamp column Returns the standard deviation of the values in the argument column. NULL-values are ignored. double or NULL if all values are NULL or we got no value at all
SUM column name of a numeric or timestamp column Returns the sum of the values in the argument column. NULL-values are ignored. double or NULL if all values of all matching rows in that column are NULL
VARIANCE column name of a numeric or timestamp column Returns the variance of the values in the argument column. NULL-values are ignored. double or NULL if all values are NULL or we got no value at all

Note

Aggregations can only be applied to columns with a plain index, which is the default for all primitive type columns. For more information, please refer to Plain index (Default).

Some Examples:

cr> select count(*) from locations;
+----------+
| count(*) |
+----------+
| 15       |
+----------+
SELECT 1 row in set (... sec)
cr> select count(*) from locations where kind = 'Planet';
+----------+
| count(*) |
+----------+
| 5        |
+----------+
SELECT 1 row in set (... sec)
cr> select count(name), count(*) from locations;
+-------------+----------+
| count(name) | count(*) |
+-------------+----------+
| 14          | 15       |
+-------------+----------+
SELECT 1 row in set (... sec)
cr> select max(name) from locations;
+-------------------+
| max(name)         |
+-------------------+
| Outer Eastern Rim |
+-------------------+
SELECT 1 row in set (... sec)
cr> select min(date) from locations;
+--------------+
| min(date)    |
+--------------+
| 308534400000 |
+--------------+
SELECT 1 row in set (... sec)
cr> select count(*), kind from locations
... group by kind order by kind asc;
+----------+-------------+
| count(*) | kind        |
+----------+-------------+
| 2        | City        |
| 4        | Galaxy      |
| 5        | Planet      |
| 4        | Star System |
+----------+-------------+
SELECT 4 rows in set (... sec)
cr> select max(position), kind from locations
... group by kind order by max(position) desc;
+---------------+-------------+
| max(position) | kind        |
+---------------+-------------+
| 15            | City        |
| 6             | Galaxy      |
| 5             | Planet      |
| 4             | Star System |
+---------------+-------------+
SELECT 4 rows in set (... sec)
cr> select min(name), kind from locations
... group by kind order by min(name) asc;
+------------------------------------+-------------+
| min(name)                          | kind        |
+------------------------------------+-------------+
|                                    | Planet      |
| Aldebaran                          | Star System |
| Berlin                             | City        |
| Galactic Sector QQ7 Active J Gamma | Galaxy      |
+------------------------------------+-------------+
SELECT 4 rows in set (... sec)
cr> select count(*), min(name), kind from locations
... group by kind order by kind;
+----------+------------------------------------+-------------+
| count(*) | min(name)                          | kind        |
+----------+------------------------------------+-------------+
| 2        | Berlin                             | City        |
| 4        | Galactic Sector QQ7 Active J Gamma | Galaxy      |
| 5        |                                    | Planet      |
| 4        | Aldebaran                          | Star System |
+----------+------------------------------------+-------------+
SELECT 4 rows in set (... sec)
cr> select sum(position) as sum_positions, kind from locations
... group by kind order by sum_positions;
+---------------+-------------+
| sum_positions | kind        |
+---------------+-------------+
| 10.0          | Star System |
| 13.0          | Galaxy      |
| 15.0          | Planet      |
| 29.0          | City        |
+---------------+-------------+
SELECT 4 rows in set (... sec)

GROUP BY

Crate supports the group by clause. This clause can be used to group the resulting rows by the value(s) of one or more columns. That means that rows that contain duplicate values will be merged together.

This is useful if used in conjunction with aggregation functions:

cr> select count(*), kind from locations
... group by kind order by count(*) desc, kind asc;
+----------+-------------+
| count(*) | kind        |
+----------+-------------+
| 5        | Planet      |
| 4        | Galaxy      |
| 4        | Star System |
| 2        | City        |
+----------+-------------+
SELECT 4 rows in set (... sec)

Note

All columns that are used either as result column or in the order by clause have to be used within the group by clause. Otherwise the statement won’t execute.

Note

Grouping on multi-value fields doesn’t work. If such a field is encountered during a group by operation an error is thrown.

Note

Grouping can only be applied to columns with a plain index, which is the default for all columns. For more information, please refer to Plain index (Default).

HAVING

The having clause is the equivalent to the where clause for the resulting rows of a group by clause. A simple having clause example using an equality operator:

cr> select count(*), kind from locations
... group by kind having count(*) = 4 order by kind;
+----------+-------------+
| count(*) | kind        |
+----------+-------------+
|        4 | Galaxy      |
|        4 | Star System |
+----------+-------------+
SELECT 2 rows in set (... sec)

The condition of the having clause can refer to the resulting columns of the group by clause. It is also possible to use aggregates in the having clause just like in the result columns:

cr> select count(*), kind from locations
... group by kind having min(name) = 'Berlin';
+----------+------+
| count(*) | kind |
+----------+------+
|        2 | City |
+----------+------+
SELECT 1 row in set (... sec)
cr> select count(*), kind from locations
... group by kind having count(*) = 4 and kind like 'Gal%';
+----------+--------+
| count(*) | kind   |
+----------+--------+
|        4 | Galaxy |
+----------+--------+
SELECT 1 row in set (... sec)

Comparison Operators

The having clause supports the same operators as the WHERE Clause:

Operator Description
< less than
> greater than
<= less than or equal to
>= greater than or equal to
= equal
<> not equal
!= not equal - same as <>
like matches a part of the given value
~ regular expression match
!~ negated regular expression match
between a BETWEEN x and y: shortcut for a >= x AND a <= y

Predicates

The having supports almost the same predicates as the WHERE Clause excpet the match predicate, which is not supported. The following Predicates are supported:

Predicate Description
IS NOT NULL value is not null and not missing
IS NULL value is null or missing