SELECT Statements

Select statements are used to manipulate data in Arroyo. The general form of that statement is:

[WITH with_query [, ...]]
SELECT select_expr [, ...]
FROM from_item
[JOIN join_item [, ...]]
[WHERE condition]
[GROUP BY grouping_element [, ...]]
[HAVING condition]

WITH clause

The with clauses allow you to give names to subquery which you can then reference. The syntax for a with clause is:

WITH query_name AS (subquery) [,...]

For example, using the nexmark source, you can create datasets for bids and price and then join.

WITH bids AS
    (SELECT bid.auction AS auction, bid.price AS price
        FROM nexmark where bid is not null),
auctions AS
    (SELECT auction.id AS id
        FROM nexmark where auction is not null)
SELECT * FROM bids bids
        JOIN auctions auctions
            ON bids.auction = auctions.id;

SELECT clause

The select cause is a comma-separated list of expressions, with an optional alias.

Column names must be unique.

SELECT select_expr [, ...]

FROM clause

The FROM clause specifies the primary source of data. It will be either a table name or subquery. The table name can be either a saved source, a table created in the WITH clause or a table created via CREATE TABLE and inserted into. Tables can be given aliases, but will default to their name as the alias for things like joins.

FROM from_item

JOIN clause

The JOIN clause allows you to join multiple tables together.

See the join documentation for more details.

WHERE clause

The