You are viewing an older version of this section. View current production version.
SELECT
Retrieves data from a table.
Syntax
[with_clause]
SELECT
[ALL | DISTINCT | DISTINCTROW]
select_expr [[AS] alias], ...
[FROM table_references
[WHERE expr]
[GROUP BY {col_name | expr | position}, ...]
[HAVING expr]
[ORDER BY {col_name | expr | position} [ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[INTO OUTFILE 'file_name' export_options]
]
Remarks
export_options
clauses are documented in the SELECT … INTO OUTFILE below.with_clause
is documented on the WITH Common Table Expressions page.- If you are querying against a columnstore table with a FULLTEXT index defined, see MATCH and HIGHLIGHT for syntax and examples on how to do full text search queries.
- Non-aggregated data can also be transformed into a pivot table output format. See PIVOT for syntax and examples.
Aggregations for Expression Syntax
MemSQL supports the following aggregations for expression syntax in SELECT
statements:
COUNT(*) -- This returns the count of rows matching the query
COUNT(expr) -- This returns the count of rows matching the query where the expression is non-NULL
COUNT(DISTINCT expr) -- This returns the count over distinct non-NULL values of the expression
APPROX_COUNT_DISTINCT(expr) -- This returns an approximation of the number of distinct values of the expression
SUM(expr) -- This returns the sum value of the expression
SUM(DISTINCT expr) -- This returns the sum of the distinct values of the expression
AVG(expr) -- This returns the average value of the expression
AVG(DISTINCT expr) -- This returns the average of the distinct values of the expression
MAX(expr) -- This returns the maximum value of the expression
MIN(expr) -- This returns the minimum value of the expression
STDDEV_POP(expr) -- This is the population standard deviation
STD(expr) -- This is a synonym for STDDEV_POP
STDDEV(expr) -- This is a synonym for STDDEV_POP
STDDEV_SAMP(expr) -- This is the sample standard deviation
VAR_POP(expr) -- This is the population variance
VARIANCE(expr) -- This is a synonym for VAR_POP
VAR_SAMP(expr) -- This is the sample variance
GROUP_CONCAT(expr) -- This concatenates the non-NULL values of the expression into a string
GROUP_CONCAT(DISTINCT expr) -- This concatenates the distinct non-NULL values of the expression into a string
All aggregate functions exclude NULLs from their computations. For example, SELECT COUNT(c) FROM t returns the number of rows in t where c is not NULL. SELECT AVG(c) FROM t returns the average of the non-NULL c’s in t.
COUNT(*) is equivalent to COUNT(1). It counts every row matching the query, even rows whose column data contain nothing but NULLs.
JOIN and Subqueries
Syntax
MemSQL supports the following JOIN
and subquery syntax for the table_reference
part of SELECT
statements:
table_reference:
table_factor | join_table
table_factor:
tbl_name [[AS] alias]
| (subquery) [AS] alias
join_table:
table_reference {LEFT | RIGHT | FULL} [OUTER] JOIN table_factor join_condition
| table_reference [INNER | CROSS] JOIN table_factor [join_condition]
| table_reference NATURAL {LEFT | RIGHT} [OUTER] JOIN table_factor
| table_reference STRAIGHT_JOIN table_factor [join_condition]
| table_reference, table_factor
join_condition:
ON conditional_expr
Remarks
-
This command must be run on the master aggregator or a child aggregator node (see Node Requirements for MemSQL Commands)
-
STRAIGHT_JOIN
forces tables to be joined in the order in which they are listed in theFROM
clause -
FULL OUTER JOIN
requires the join condition to be an equality. Also, the right argument to a full outer join cannot be a reference table. -
MemSQL doesn’t yet support nested joins. However, in many cases you can rewrite queries that seem to require a nested join without one. For example, the following query is not supported:
Nested join: not allowed
... FROM tableA LEFT JOIN (tableB LEFT JOIN tableC on tableB.col = tableC.col) ON tableA.col = tableB.col
The previous query can be rewritten without a nested join in the following way:
Rewritten as two left joins
... FROM tableA LEFT JOIN tableB ON tableA.col = tableB.col LEFT JOIN tableC ON tableB.col = tableC.col
Examples
memsql> SELECT * FROM my_MemSQL_table WHERE col = 1;
memsql> SELECT COUNT(*), user_name, page_url from clicks, users, pages
-> WHERE clicks.user_id = users.user_id AND pages.page_id = clicks.page_id
-> GROUP BY users.user_id, pages.page_id
-> ORDER BY COUNT(*) DESC;
+- ---------+- ----------+- -------------------------------+
| COUNT(*) | user_name | page_url |
+- ---------+- ----------+- -------------------------------+
| 5 | jake | memsql.com |
| 2 | john | http://www.memsql.com/download |
| 1 | jake | docs.memsql.com |
| 1 | jake | memsql.com |
| 1 | jake | http://www.memsql.com/download |
+- ---------+- ----------+- -------------------------------+
5 rows in set (0.00 sec)
SELECT t1.*, t2.* FROM t1 FULL OUTER JOIN t2 ON t1.a = t2.a;
SELECT … INTO OUTFILE
SELECT ... INTO OUTFILE
formats and writes the results of a SELECT
query to a text file. The export_options
are very similar to the parsing options used with LOAD DATA
.
export_options:
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
Remarks
- By default, MemSQL writes the file to the directory specified in the global variable
tmpdir
(The same asdatadir
by default). To specify another location, enter the absolute path to the file as thefile_name
parameter. - The default text formatting, used when the user omits the
FIELDS
andLINES
blocks, is to separate values with tabs (\t
) and rows with newlines (\n
).
Example
The following query writes the contents of table_name to a file in the home directory of username. The file is formatted as a comma-separated values (CSV) file.
memsql> SELECT * FROM table_name INTO OUTFILE '/home/username/file_name.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'