Want to try Hazelcast 5.0 beta?

We’ve combined the in-memory storage of IMDG with the stream processing power of Jet to bring you an all new Hazelcast platform.

SELECT

Synopsis

SELECT [ * | expression [ [ AS ] expression_alias ] [, ...] ]
FROM table_name [ [ AS ] table_alias ]
[WHERE condition]

Description

The SELECT command retrieves rows from a table. A row is a sequence of expressions defined after the SELECT keyword. Expressions may have optional aliases.

table_name refers to a single IMap data structure. A table may have an optional alias.

An optional WHERE clause defines a condition, that is any expression that evaluates to a result of type boolean. Any row that doesn’t satisfy the condition is eliminated from the result.

Sorting

You can use the standard SQL clauses ORDER BY, LIMIT, and OFFSET to sort and limit the result set.

Note that, you must add sorted indexes to the map object’s fields to be sorted by. See the indexing section for information on adding and configuring indexes. For example, for the SELECT * FROM persons ORDER BY name ASC query, there has to be a sorted index on the name field as shown below:

+

  • XML

  • YAML

  • Java

<hazelcast>
    <map name="default">
        <indexes>
            <index type="SORTED">
                <attributes>
                    <attribute>name</attribute>
                </attributes>
            </index>
    </map>
</hazelcast>
hazelcast:
  map:
    default:
      indexes:
        - type: SORTED
            attributes:
              - "name"
HazelcastInstance hz = Hazelcast.newHazelcastInstance();
IMap map = hz.getMap( "default" );
map.addIndex(new IndexConfig(IndexType.SORTED, "name"));

See the below examples for sorting.

The following statement gets the top five employees ordered by the first_name field and skipping the first three ones:

SELECT
    employee_id, first_name, last_name
FROM
    employees
ORDER BY first_name
LIMIT 5 OFFSET 3;

The following statement gets the top five employees with the highest salaries.

SELECT
    employee_id, first_name, last_name, salary
FROM
    employees
ORDER BY salary DESC
LIMIT 5;

Unsupported Features

The following features are not supported and are planned for future releases:

  • GROUP BY/HAVING

  • JOIN

  • set operators (UNION, INTERSECT, MINUS)

  • subqueries (SELECT …​ FROM table WHERE x = (SELECT …))