Syntax:
simple-select: SELECT [ ALL | DISTINCT ] result-spec [ KEEP | DROP result-column-spec[, result-column-spec]* ] [ FROM source-item-list ] [ WHERE boolean-expression ] [ GROUP BY expression[, expression]* [ HAVING boolean-expression ] [QUALIFY boolean-expr ] compound-select: simple-select [UNION | UNION ALL | APPEND | INTERSECT | EXCEPT simple-select]* [ ORDER BY sort-expr-list] [ LIMIT integer-value [ (OFFSET | ,) integer-value ] ] compound-select (with sampling): SELECT [ ALL | DISTINCT ] result-spec [ FROM source-table[, source-table]* SAMPLE sample-spec [ REPEAT non-negative-integer-value] [ WITH [ MINIMAL ] REPLACEMENT [ HITS ] ] [ WEIGHT expression ] [ SEED non-negative-integer-value ] [ SAMPLE_WEIGHT AS column-name ] [ SAMPLE_OBSPROB AS column-name ] [ SAMPLE_EXPECTED_HITS AS column-name ] [ SAMPLE_HITS AS column-name ] [ SAMPLE_NO AS column-name ] [ ORDER BY sort-expr-list] [ LIMIT integer-value [ (OFFSET | ,) integer-value ] ] result-spec: result-columns[, result-columns]* result-columns: [table-name.]* | REGEXP regular-expression | expr [ [ AS ] result-column-name ] result-column-spec: result-column-name | REGEXP regular-expression source-item-list: source-item[, source-item]* source-item: source-table [merge-join-op]* [simple-join-op]* [ AS table-alias ] source-table: [catalog-name.]table-name | compound-select [ AS table-alias ] simple-join-op: , | [ NATURAL ] [ LEFT | FULL ] [ CROSS ] JOIN source-table join-condition merge-join-op: [ LEFT | FULL ] [ CROSS ] MERGE JOIN source-table join-condition [ AND boolean-expression ] join-condition [ ON boolean-epression ] | [ USING (id-list) ] sort-expr-list: expr [ sort-order ][, expr [ sort-order ]]* sort-order: [ COLLATE collation-name ] [ ASC | DESC ] sample-spec: rows-to-sample [ STRATA expression ] | CASE [ expression ] case-sample-spec [ case-sample-spec ]* [ ELSE rows-to-sample ] END rows-to-sample: non-negative-integer-value [ MINROWS non-negative-integer-value ] | RATE non-negative-value [ MINROWS non-negative-integer-value ] case-sample-spec: WHEN expression THEN rows-to-sample
The SELECT statement is used to query the database. The result of SELECT is zero or more rows of data where each row has a fixed number of columns. The number of columns in the result is specified by the expression list between the SELECT and FROM keywords. Any arbitrary expression can be used as a result. Below is a list of some useful constructions:
If the result expression is * then all columns of all tables are substituted for that one expression. If the expression is the name of a table followed by .* then the result is all columns in that table.
It is also possible to use regular expressions to define the result variable list, i.e.
SELECT REGEXP regular-expression FROM ...
See the description of the REGEXP operator for more details.
The construction
SELECT *- column-name
keeps all the columns except the one specified after *-.
The construction
SELECT table-name.var-name FROM table-name
will assign the name var-name to the returned column.
The DISTINCT keyword causes a subset of result rows to be returned, in which each result row is different. NULL values are not treated as distinct from each other. The default behavior is that all result rows are returned, which can also be made explicit with the keyword ALL.
To restrict the columns returned by the first part of the SELECT statement, the KEEP or DROP clause can be used. With KEEP only the columns with names matching the strings or regular expressions listed after the keyword will be used in subsequent stages of processing the select statement. With DROP> the specified columns will be removed from further processing.
The query is executed against one or more tables specified after the FROM keyword. If multiple tables names are separated by commas, then the query is against the cross join of the various tables. The full SQL-92 join syntax can also be used to specify joins. A sub-query in parentheses may be substituted for any table name in the FROM clause. The entire FROM clause may be omitted, in which case the result is a single row consisting of the values of the expression list.
The GROUP BY clause causes one or more rows of the result to be combined into a single output row. This is especially useful when the result contains aggregate functions. The expressions in the GROUP BY clause do not have to be expressions that appear in the result.
The HAVING and QUALIFY clauses are similar to WHERE except that HAVING and QUALIFY apply after grouping has occurred. The HAVING and QUALIFY expressions may refer to values, including aggregate functions, that are not in the result. The QUALIFY expression may additionally use window functions.
The ORDER BY clause causes the output rows to be sorted. The argument of ORDER BY is a list of expressions that are used as the key for the sorting. The expressions do not have to be a part of the result for a simple SELECT, but in a compound SELECT each sort expression must exactly match one of the result columns. Each sort expression may be optionally followed by the COLLATE keyword and the name of a collating function used for ordering text and/or the ASC or DESC keywords to specify the sort order.
The expressions used in the ORDER BY clause may also refer to window functions. However, it is not possible to use window functions in ORDER BY clause which is a part of the OVER () clause which defines the partitioning and ordering for window functions.
The LIMIT clause places an upper bound on the number of rows returned in the result. A negative LIMIT indicates no upper bound. The optional OFFSET following LIMIT specifies how many rows to skip at the beginning of the result set. In a compound query, the LIMIT clause may only appear on the final SELECT statement. The limit is applied to the entire query not to the individual SELECT statement to which it is attached. Note that if the OFFSET keyword is used in the LIMIT clause, then the limit is the first number and the offset is the second number. If a comma is used instead of the OFFSET keyword, then the offset is the first number and the limit is the second number. This seemingly contradictive behavior is intentional: it maximizes compatibility with legacy SQL database systems.
A compound SELECT is formed from two or more simple SELECT statements connected by one of the operators UNION, UNION ALL, APPENDINTERSECT or EXCEPT. In a compound SELECT, all the constituent SELECT statementss must specify the same number of result columns. There may only be a single ORDER BY clause at the end of a compound SELECT statement. The UNION, UNION ALL and APPEND operators combine the results of the SELECT statements to the right and left into a single big table. The difference is that in UNION all result rows are distinct while in UNION ALL there may be duplicates. With APPEND the tables (queries) are joined on the basis of column names and not their order. NULL is inserted whenever a column is missing in any of the source tables/queries
The INTERSECT operator takes the intersection of the results of the left and right SELECT statements. EXCEPT takes the result of the left SELECT after removing the results of the right SELECT. When three or more SELECT statementss are connected into a compound, they group from left to right.
The JOIN clause combines records from several tables on the basis of the defined conditions. There are two (not exclusive) variants of the JOIN clause.
if ... JOIN ... ON ... is used then all the matching-columns will appear in the output table.
Example 43.15. JOIN ... ON
table 'TAB1':
id1 a b
1011 'ALICE' 20
1011 'HELEN' 25
1012 'MARGARET' 30
1013 'MEGAN' 40
None 'OLIVIA' 50
table 'TAB2':
id2 c d
1011 'PAUL' 100
1012 'PETER' 200
1012 'JOHN' 500
1013 'ADAM' 300
1015 'JAMES' 400
sql:
REPLACE TABLE TAB3 AS
SELECT * FROM TAB1
JOIN TAB2
ON id1 = id2
sql TAB3_out:
SELECT * FROM TAB3
print TAB3_out
Output:
id1 | a | b | id2 | c | d | +----+----------+----+------+-------+-----+-- 1011 | ALICE | 20 | 1011 | PAUL | 100 | 1011 | HELEN | 25 | 1011 | PAUL | 100 | 1012 | MARGARET | 30 | 1012 | PETER | 200 | 1012 | MARGARET | 30 | 1012 | JOHN | 500 | 1013 | MEGAN | 40 | 1013 | ADAM | 300 |
the ... JOIN USING() ... requires common names for the matching columns. That is why only one common column of the matching pair will appear in the output table.
Example 43.16. Using clause
table 'TAB1':
id a b
1011 'ALICE' 20
1011 'HELEN' 25
1012 'MARGARET' 30
1013 'MEGAN' 40
None 'OLIVIA' 50
table 'TAB2':
id c d
1011 'PAUL' 100
1012 'PETER' 200
1012 'JOHN' 500
1013 'ADAM' 300
1015 'JAMES' 400
sql:
REPLACE TABLE TAB3 as
SELECT * FROM TAB1 AS t1
JOIN TAB2 AS t2
USING(id)
sql TAB3_out:
SELECT * from TAB3
print TAB3_out
Output:
id | a | b | c | d | +----+----------+----+-------+-----+-- 1011 | ALICE | 20 | PAUL | 100 | 1011 | HELEN | 25 | PAUL | 100 | 1012 | MARGARET | 30 | PETER | 200 | 1012 | MARGARET | 30 | JOHN | 500 | 1013 | MEGAN | 40 | ADAM | 300 |
There are several types of joining, as described in the following subsections.
... NATURAL JOIN ... - combines tables using all the columns that have the same names; it doesn't require any joining conditions.
Example 43.17. Natural join, example 1
table 'TAB1':
id a b
1011 'ALICE' 20
1011 'HELEN' 25
1012 'MARGARET' 30
1013 'MEGAN' 40
None 'OLIVIA' 50
table 'TAB2':
id c d
1011 'PAUL' 100
1012 'PETER' 200
1012 'JOHN' 500
1013 'ADAM' 300
1015 'JAMES' 400
sql:
REPLACE TABLE TAB3 AS
SELECT * FROM TAB1
NATURAL JOIN TAB2
sql TAB3_out:
SELECT * FROM TAB3
print TAB3_out
Output:
id | a | b | c | d | +----+----------+----+-------+-----+-- 1011 | ALICE | 20 | PAUL | 100 | 1011 | HELEN | 25 | PAUL | 100 | 1012 | MARGARET | 30 | PETER | 200 | 1012 | MARGARET | 30 | JOHN | 500 | 1013 | MEGAN | 40 | ADAM | 300 |
Example 43.18. Natural join, example 2
table 'TAB1':
id a b
1011 'ALICE' 20
1011 'HELEN' 25
1012 'MARGARET' 30
1013 'MEGAN' 40
None 'OLIVIA' 50
table 'TAB2':
id a d
1011 'ALICE' 100
1012 'MARGARET' 200
1012 'MARGARET' 500
1013 'ADAM' 300
1015 'JAMES' 400
sql:
REPLACE TABLE TAB3 AS
SELECT * FROM TAB1
NATURAL JOIN TAB2
sql TAB3_out:
SELECT * FROM TAB3
print TAB3_out
Output:
id | a | b | d | +----+----------+----+-----+-- 1011 | ALICE | 20 | 100 | 1012 | MARGARET | 30 | 200 | 1012 | MARGARET | 30 | 500 |
... LEFT JOIN ... ON (USING) ... - contains all the records from the 'left' table and the matched record from the 'right' one. In the case when there are no fitting records in the 'right' table the output table will still contain record from the 'left' table and null columns from the 'right'.
Example 43.19. Left join, example 1
table 'TAB1':
id a b
1011 'ALICE' 20
1011 'HELEN' 25
1012 'MARGARET' 30
1013 'MEGAN' 40
None 'OLIVIA' 50
table 'TAB2':
id c d
1011 'PAUL' 100
1012 'PETER' 200
1012 'JOHN' 500
sql:
REPLACE TABLE TAB3 AS
SELECT * FROM TAB1 # or select * from TAB1 as t1
LEFT JOIN TAB2 # or left join TAB2 as t2
USING(id) # or t1.id = t2.id
sql TAB3_out:
SELECT * FROM TAB3
print TAB3_out
Output:
id | a | b | c | d | +----+----------+----+-------+------+-- 1011 | ALICE | 20 | PAUL | 100 | 1011 | HELEN | 25 | PAUL | 100 | 1012 | MARGARET | 30 | PETER | 200 | 1012 | MARGARET | 30 | JOHN | 500 | 1013 | MEGAN | 40 | None | None | None | OLIVIA | 50 | None | None |
Example 43.20. Left join, example 2
table 'TAB1':
id a b
1011 'ALICE' 20
1011 'HELEN' 25
1012 'MARGARET' 30
1013 'MEGAN' 40
None 'OLIVIA' 50
table 'TAB2':
id c d
10110 'PAUL' 100
10120 'PETER' 200
10120 'JOHN' 500
10130 'ADAM' 300
10150 'JAMES' 400
sql:
REPLACE TABLE TAB3 AS
SELECT * FROM TAB1
LEFT JOIN TAB2
USING(id)
sql TAB3_out:
SELECT * FROM TAB3
print TAB3_out
Output:
id | a | b | c | d | +----+----------+----+------+------+-- 1011 | ALICE | 20 | None | None | 1011 | HELEN | 25 | None | None | 1012 | MARGARET | 30 | None | None | 1013 | MEGAN | 40 | None | None | None | OLIVIA | 50 | None | None |
... FULL JOIN ... ON (USING) ... - contains all the records from all the joined tables. Nulls are filled in for missing matches.
Example 43.21. Full join
table 'TAB1':
id a b
1011 'ALICE' 20
1011 'HELEN' 25
1012 'MARGARET' 30
1013 'MEGAN' 40
None 'OLIVIA' 50
table 'TAB2':
id c d
1011 'PAUL' 100
1012 'PETER' 200
1012 'JOHN' 500
1013 'ADAM' 300
1015 'JAMES' 400
sql:
REPLACE TABLE TAB3 AS
SELECT * FROM TAB1
FULL JOIN TAB2
USING(id)
sql TAB3_out:
SELECT * FROM TAB3
print TAB3_out
Output:
id | a | b | c | d | +----+----------+------+-------+------+-- 1011 | ALICE | 20 | PAUL | 100 | 1011 | HELEN | 25 | PAUL | 100 | 1012 | MARGARET | 30 | PETER | 200 | 1012 | MARGARET | 30 | JOHN | 500 | 1013 | MEGAN | 40 | ADAM | 300 | None | OLIVIA | 50 | None | None | 1015 | None | None | JAMES | 400 |
... CROSS JOIN ... - contains the cartesian product of the joined tables, i.e. each record from 'left' table is matched with each record from 'rigth' table.
Example 43.22. Cross join
table 'TAB1':
id1 a b
1011 'ALICE' 20
1011 'HELEN' 25
1012 'MARGARET' 30
1013 'MEGAN' 40
None 'OLIVIA' 50
table 'TAB2':
id2 c d
1011 'PAUL' 100
1012 'PETER' 200
1012 'JOHN' 500
1013 'ADAM' 300
1015 'JAMES' 400
sql:
REPLACE TABLE TAB3 AS
SELECT * FROM TAB1
CROSS JOIN TAB2
sql x:
SELECT * FROM TAB3
print x
Output:
id1 | a | b | id2 | c | d | +----+----------+----+------+-------+-----+-- 1011 | ALICE | 20 | 1011 | PAUL | 100 | 1011 | ALICE | 20 | 1012 | PETER | 200 | 1011 | ALICE | 20 | 1012 | JOHN | 500 | 1011 | ALICE | 20 | 1013 | ADAM | 300 | 1011 | ALICE | 20 | 1015 | JAMES | 400 | 1011 | HELEN | 25 | 1011 | PAUL | 100 | 1011 | HELEN | 25 | 1012 | PETER | 200 | 1011 | HELEN | 25 | 1012 | JOHN | 500 | 1011 | HELEN | 25 | 1013 | ADAM | 300 | 1011 | HELEN | 25 | 1015 | JAMES | 400 | 1012 | MARGARET | 30 | 1011 | PAUL | 100 | 1012 | MARGARET | 30 | 1012 | PETER | 200 | 1012 | MARGARET | 30 | 1012 | JOHN | 500 | 1012 | MARGARET | 30 | 1013 | ADAM | 300 | 1012 | MARGARET | 30 | 1015 | JAMES | 400 | 1013 | MEGAN | 40 | 1011 | PAUL | 100 | 1013 | MEGAN | 40 | 1012 | PETER | 200 | 1013 | MEGAN | 40 | 1012 | JOHN | 500 | 1013 | MEGAN | 40 | 1013 | ADAM | 300 | 1013 | MEGAN | 40 | 1015 | JAMES | 400 | None | OLIVIA | 50 | 1011 | PAUL | 100 | None | OLIVIA | 50 | 1012 | PETER | 200 | None | OLIVIA | 50 | 1012 | JOHN | 500 | None | OLIVIA | 50 | 1013 | ADAM | 300 | None | OLIVIA | 50 | 1015 | JAMES | 400 |
... MERGE JOIN ... forces a different joining algorith to be used, which, instead of using indexes, relies on the two joined tables being sorted in the same manner. A MERGE JOIN command requires a USING or an ON clause. MERGE JOIN can be used with any other type of join operation (i.e. LEFT, FULL, CROSS) with the exception of NATURAL.
In one SELECT command it is possible to MERGE JOIN up to 32 different tables if running the 32 bit GDBase engine, and up to 64 tables in the case of the 64 bit engine.
It is possible to specify additional joining conditions after MERGE JOIN ... USING and MERGE JOIN ... ON, e.g:
... MERGE JOIN table-1 USING (column-1) AND column-2 == column-3
PRAGMA TABLE_ORDER(table-name)
MERGE JOIN ... USING(column-1, column-2, column-3, ...) will join the two tables in a manner similar to ordinary JOIN ... USING clause. The extra conditions imposed on the tables and columns in order for MERGE JOIN to work are:
When used with MERGE JOIN, the ON clause must use columns from exactly two tables.
If column-1, column-2, ... are the names of columns appearing in the ON clause, then the same restrictions on the sorting and sorting sequence of the joined tables apply as in the case of MERGE JOIN ... USING().
When used with the FULL join type, MERGE JOIN can be used to join up to 32 (with 32 bit engine) or 64 (with 64 bit engine) different tablesin one SELECT statement, unlike the ordinary FULL JOIN, which can join only 2 tables.
MERGE JOIN can be used together with another JOIN (with and without MERGE) commands in the same SELECT statement.
The MERGE JOIN operation can be combined with the TRANSFORM clause, allowing for further transformation of the resulting table using Gython commands.
The following clauses in a SELECT statement are compatible with MERGE JOIN:
| WHERE | ORDER BY | UNION ALL |
| GROUP BY | LIMIT | INTERSECT |
| HAVING | UNION | EXCEPT |
The following additional restrictions on using MERGE JOIN apply:
CREATE VIEW View-1 AS SELECT * FROM Table-1 MERGE JOIN Table-2 USING(Column-1) LEFT JOIN Table-3 ON Table-2.Column-1 < Table-3.Column-2 AND Table-2.Column-1 > 1
SELECT * FROM Table-1 MERGE JOIN (Table-2 JOIN Table-3 USING(Column-1)) USING(Column-1)
SELECT * FROM (SELECT * FROM Table-1 ORDER BY Column-1) MERGE JOIN Table-2 USING(Column-1)
The SELECT ... FROM ... SAMPLE ... variant of the SELECT statement can be used to randomly sample rows from the table.
A SELECT ... SAMPLE statement can also be used as a subquery and the input table for a SELECT ... SAMPLE statement can also be a subquery.
A SELECT ... FROM ... SAMPLE ... statement can only include LIMIT, OFFSET and ORDER BY subclauses. Other functionalities can be obtained with subqueries.
To sample 100 rows without replacement use e.g.
SELECT ... FROM ... SAMPLE 100
The result of such a select statement will also include a column named SAMPLE_OBSPROB.
To sample 100 rows with replacement use e.g.
SELECT ... FROM ... SAMPLE 100 WITH REPLACEMENT
To use an alterative sampling algorithm use
SELECT ... FROM ... SAMPLE 100 WITH MINIMAL REPLACEMENT
The result of such a select statement will also include a column named SAMPLE_EXPECTED_HITS with the expected number of repetitions of the given row in the result table. If the keyword REPLACEMENT is followed by HITS then the result will additionally include a column named SAMPLE_HITS indicating, how many times the given row is actually repeated in the sample.
By default every row (or every row specified by the CASE or STRATA clause) is sampled with the same probability. This behavior can be changed by calculating the weight for each row using the WEIGHT clause. The result table always contains a column named SAMPLE_WEIGHT, which specifies the weight with which the given row has been sampled.
To specify sampling strata use the STRATA keyword followed by an expression. Each value to which the expression evaluates will correspond to a different strata. For example, a statement like the one below:
SELECT ... FROM ... SAMPLE 100 STRATA geneder=female
will select a sample with 200 rows, in which 100 rows will have the column gender with the value of female and the remaining 100 rows will have a different value in this column.
It is also possible to sample only from a specified subsets of the data using CASE WHEN ... THEN ... ELSE clause. For instance, the statement
SELECT ... FROM ... SAMPLE CASE WHEN (age > 18 AND age < 26) THEN 20 WHEN (age >= 26 AND age < 32 ) THEN 40 WHEN (age >= 32 AND age < 50) THEN 60 ELSE 50 END
will sample 20 rows for which the column age has values between 18 and 26, 40 rows for which the column age has values from 26 to 31, 60 rows for age between 31 and 50, and 50 other rows.
Alternatively, the CASE keyword can also be followed by an expression:
SELECT ... FROM ... SAMPLE CASE floor(age / 20) WHEN 0 THEN 0 WHEN 1 THEN 20 WHEN 2 THEN 40 WHEN 3 THEN 30 ELSE 20 END
The number of rows to sample can be specified either as a non-negative integer number or as the fraction of the given strata or subset, using the RATE keyword followed by a number between 0 and 1 (1 results 100% (i.e. all) of the rows being sampled). The optional MINROWS keyword forces at least a given number of rows to be sampled, even if it results in a greater than specified sampling rate for the given strata.
To obtaine a number of independent samples from a given dataset use the REPEAT keyword followed by the number of independent samples to generate. All samples will follow the same specification as to division into strata and number of sampled rows. The resulting table will contain a column named SAMPLE_NOspecifying to which sample the given row belongs.
The result table of SELECT ... FROM ... SAMPLE ... by default includes the following additional columns:
This column is created when sampling without replacemenet. It contains the probability of sampling the given row, taking into account weight and division into strata.
This column is created instead of SAMPLE_ONSPROBwhen sampling with replacement. It contains the expectancy of the number of the row's occurences in the sample.
This column contains the weight with which the row is sampled.
When sampling with replacement is used, rows which are sampled multiple times will not be repeated in the output table. Instead, an additional column SAMPLE_HITS is created with integer values specifying how many times has the row been sampled.
When multiple samples are created (i.e. the REPEAT keyword is used) the SAMPLE_NO column contains the number of sample to which the row belongs.
The columns created by the SELECT ... SAMPLE statement can be renamed using the SAMPLE_WEIGHT AS ... etc. instructions at the end of the SELECT statement.
It is possible to remove some or all of these columns from the result table with the appropriate use of the standard KEEP and DROP clauses.
A single select statement is executed in the following order:
Single SELECT statements. First all single select statements (select-statement) are processed. The clauses are processed in the following order:
| MERGE JOIN -> | JOIN -> | WHERE -> | GROUP BY -> | HAVING -> | OVER -> | QUALIFY -> | DISTINCT |
Compounding of SELECT statements. At this stage the simple SELECT statements are combined together according to the rules specified by UNION, UNION ALL, INTERSECT and EXCEPT keywords. The compounding is performed from left to right, with the output of the earlier compounding operation being treated as input for the subsequent compounding operation.
Compound select statement. At this stage the result of compounding is subjected to the following clasues in the specified order:
| ORDER BY -> | LIMIT (and OFFSET) |