SELECT

❗️
This is a legacy Apache Ignite documentation
The new documentation is hosted here: https://ignite.apache.org/docs/latest/

Retrieve data from a table or multiple tables.

SELECT
    [TOP term] [DISTINCT | ALL] selectExpression [,...]
    FROM tableExpression [,...] [WHERE expression]
    [GROUP BY expression [,...]] [HAVING expression]
    [{UNION [ALL] | MINUS | EXCEPT | INTERSECT} select] 
    [ORDER BY order [,...]]
    [{ LIMIT expression [OFFSET expression] 
    [SAMPLE_SIZE rowCountInt]} | {[OFFSET expression {ROW | ROWS}] 
    [{FETCH {FIRST | NEXT} expression {ROW | ROWS} ONLY}]}]

DISTINCT - specifies to remove duplicate rows from a result set.
GROUP BY - groups the result by the given expression(s).
HAVING - filters rows after grouping.
ORDER BY - sorts the result by the given column(s) or expression(s).
LIMIT and FETCH FIRST/NEXT ROW(S) ONLY - limits the number of rows returned by the query (no limit if null or smaller than zero).
OFFSET - specifies how many rows to skip.
UNION, INTERSECT, MINUS, EXPECT - combines the result of this query with the results of another query.
tableExpression - Joins a table. The join expression is not supported for cross and natural joins. A natural join is an inner join, where the condition is automatically on the columns with the same name.

tableExpression = [[LEFT | RIGHT]{OUTER}] | INNER | CROSS | NATURAL] 
JOIN tableExpression 
[ON expression]

LEFT - LEFT JOIN performs a join starting with the first (left-most) table and then any matching second (right-most) table records.
RIGHT - RIGHT JOIN performs a join starting with the second (right-most) table and then any matching first (left-most) table records.
OUTER - Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table's rows are retained (left, right, or both).
INNER - An inner join requires each row in the two joined tables to have matching column values.
CROSS - CROSS JOIN returns the Cartesian product of rows from tables in the join.
NATURAL - The natural join is a special case of equi-join.
ON - Value or condition to join on.

SELECT queries can be executed against both replicated and partitioned data.

When executed against fully replicated data, Ignite will send a query it to a single cluster node and run it over the local data there.

On the other hand, if a query is executed over partitioned data, then the execution flow will be the following:

The query will be parsed and split into multiple map queries and a single reduce query.
All the map queries are executed on all the nodes where required data resides.
All the nodes provide result sets of local execution to the query initiator (reducer) that, in turn, will accomplish the reduce phase by properly merging provided result sets.

Ignite supports collocated and non-collocated distributed SQL joins. Furthermore, if the data resides in different tables (aka. caches in Ignite), Ignite allows for cross-table joins as well.

Joins between partitioned and replicated data sets always work without any limitations.

However, if you join partitioned data sets, then you have to make sure that the keys you are joining on are either collocated or you switched on the non-collocated joins parameter for a query.

Refer to Distributed Joins page for more details.

SQL queries with ORDER BY clause do not require loading the whole result set to a query initiator (reducer) node in order to complete the sorting. Instead, every node where a query will be mapped to will sort its own part of the overall result set and the reducer will do the merge in a streaming fashion.

The same optimization is implemented for sorted GROUP BY queries - there is no need to load the whole result set to the reducer in order to do the grouping before giving it to an application. In Apache Ignite, partial result sets from the individual nodes can be streamed, merged, aggregated, and returned to the application gradually.

Retrieve all rows from Person table:

Get all rows in the alphabetical order:

SELECT * FROM Person ORDER BY name;

Calculate a number of Persons from a specific city:

SELECT city_id, COUNT(*) FROM Person GROUP BY city_id;

Join data stored in Person and City tables:

SELECT p.name, c.name
	FROM Person p, City c
	WHERE p.city_id = c.id;

Java, .NET, C++ users can execute SELECT queries using native APIs:

Updated over 5 years ago

❗️This is a legacy Apache Ignite documentation

❗️
This is a legacy Apache Ignite documentation