We can combine SQL operations with the SELECT statement. The following operations are supported:
|DISTINCT||Return only distinct (different) values|
|LIMIT||Select a limited number of records|
|AS||Give a table, or a column in a table, a temporary name|
|ORDER BY||Sort the result set in ascending or descending order|
|GROUP BY||Groups rows that have the same values into summary rows|
|JOIN||Combine rows from two or more tables|
|HAVING||Used in conjunction with GROUP BY aggregate functions|
select [distinct] ns from t (left|right|inner|cross) join ..join q on f(t)=g(q) where c group by 1,g having h order by 1,o asc|desc limit n
select distinct returns only distinct (different) values.
Return a unique list of vendor names in the trips table:
.s.e"SELECT DISTINCT vendor FROM trips;"
limit can be used to select a limited number of records, partition-by-partition. This is useful on large tables to reduce the impact on performance.
limit with an
offset is not yet available.
Return only the first 5 records from a table:
// LIMIT .s.e"SELECT * FROM TRIPS LIMIT 5;"
as can be used to give a table, or a column in a table, a temporary name.
- Renaming the column:
.s.e"SELECT vendor as company FROM trips"
order by is used to sort the result set in ascending or descending order.
This keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.
Sort the records in ascending order:
.s.e" SELECT * FROM trips ORDER BY payment_type;"
Sort the records in descending order:
.s.e" SELECT * FROM trips ORDER BY payment_type DESC;"
order by expressions may be column names, ordinal numbers of the output columns, or arbitrary expressions.
group by groups rows that have the same values for one or more columns or expressions into summary rows.
This statement is often used with aggregate functions.
Return a count of the number of trips by a column value:
.s.e"SELECT payment_type, COUNT(payment_type) AS count_per_type FROM trips GROUP BY payment_type"
group by expressions may be column names, ordinal numbers of the output columns, or arbitrary expressions.
The following aggregates are supported:
sum total avg last count max count(*) min first sum
t:( s:`AAPL`GOOG`BARC`VOD; p:13.0 12.1 4.0 5.4; a:1 2 3 4) s)select avg(p), sum(a), min(s), max(s) from t p a s ------------- 8.625 10 AAPL
q:( s:`AAPL`AAPL`GOOG`GOOG; p:4?10.0) s)select count(distinct s) from q s - 2
Aggregates and distinct
max are all supported with
join clause is used to combine rows from two or more tables, based on a related column between them.
Join types supported include
Joins may be nested, but
lateral are not yet implemented.
Returns all records from the left-hand table, and the matched records from the right-hand table.
Join two tables based on matching values in a column:
.s.e"SELECT * FROM trips LEFT JOIN cash_credit ON trips.payment_type = cash_credit.payment_type;"
No matching records
Where there were no matches, records in the left-hand table are returned but joined fields are blank.
Returns all records from the right-hand table, and the matching records from the left-hand table.
Join two tables based on matching values in a column, returning all records in the right-hand table:
.s.e"SELECT * FROM trips RIGHT JOIN cash_credit ON trips.payment_type = cash_credit.payment_type;"
Returns records that have matching values in both tables.
Join two tables based on matching values in a column, only returning records that are ones in both tables:
.s.e"SELECT * FROM trips INNER JOIN cash_credit ON trips.payment_type = cash_credit.payment_type;"
cross join returns a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause is used along with
cross join. This kind of result is called as Cartesian Product.
Cross join two tables:Each row from the first table joins with each row of the second table such that:
cross_tab:.s.e"SELECT * FROM cash_credit CROSS JOIN vendors;"
table number rows cash_credit x vendors y cash_credit CROSS JOIN vendors x*y
SQL has support for common table expressions. These queries take the form:
with t1 as (select...), t2 as (select... t1) select... t2
The results are materialized unless they can be expressed as a union of operations on individual partitions.
Scalar subqueries and correlated subqueries are supported.
The following operators support subquery arguments:
< [not] in > [not] exists =
The following operators are supported for combining queries:
union[all] intersect[all] except[all]