UltraLite Database User's Guide
Dynamic SQL
Dynamic SQL statements
Use this statement to retrieve information from the database.
SELECT [ DISTINCT ] [ FIRST | TOP n ] select-list
[ FROM table-expression ]
[ WHERE search-condition ]
[ GROUP BY group-by-expression,...group-by-expression ]
[ ORDER BY order-by-expression,...order-by-expression ]
table-expression :
table-name [ [ AS ] correlation-name ]
| table-expression { join-operator table-expression [ ON join-condition ] ,... }
| ( table-expression, ... )
join-operator :
, (ON condition not allowed)
| CROSS JOIN (ON condition not allowed)
| INNER JOIN
| JOIN (requires ON phrase)
| LEFT OUTER JOIN
order-by-expression :
{ integer | expression } [ ASC | DESC ]
DISTINCT All (the default) returns all rows that satisfy the clauses of the SELECT statement. If DISTINCT is specified, duplicate output rows are eliminated. Many statements take significantly longer to execute when DISTINCT is specified, so you should reserve DISTINCT for cases where it is necessary.
FIRST or TOP You can explicitly retrieve only the first row of a query or the first n rows of a query. These keywords are principally for use with ORDER BY queries.
select-list The select-list is a list of expressions, separated by commas, specifying what will be retrieved from the database. An asterisk (*) means select all columns of all tables in the FROM clause. Subqueries are not allowed in the select-list.
An alias name can be specified following an expression in the select-list to represent that expression. The alias name can then be used elsewhere in the query, such as in the WHERE clause or ORDER BY clause.
FROM clause Rows are retrieved from the tables and views specified in the table-expression.
ON condition The ON condition is specified for a single join operation and indicates how the join is to create rows in the result set. A WHERE clause is used to restrict the rows in the result set, after potential rows have been created by a join. For INNER joins restricting with an ON or WHERE is equivalent. For OUTER joins, they are not equivalent.
WHERE clause This clause limits the rows that are selected from the tables named in the FROM clause. It can be used to restrict rows between multiple tables.
Although both the ON phrase (which is part of the FROM clause) and the WHERE clause restrict the rows in the result set, they differ in that the WHERE clause is applied at a later stage of query execution. The ON phrase is part of the join operation between tables, while the WHERE clause is applied after the join is complete. In some queries, a condition can be specified in a WHERE clause or in the ON phrase with the same net result, but in other cases the results differ. For example, for outer joins, a condition specified in a WHERE clause gives different results from the same condition specified in the ON phrase.
GROUP BY clause You can group by columns, alias names, or functions. The result of the query contains one row for each distinct set of values in the named columns, aliases, or functions. All NULL-containing rows are treated as a single set. The resulting rows are often referred to as groups since there is one row in the result for each group of rows from the table list. Aggregate functions can then be applied to these groups to get meaningful results.
A group-by-expr is a (non-aggregate) expression written exactly the same as one of the expressions in the select-list.
When GROUP BY is used, the select-list and ORDER BY expressions must not reference any identifier that is not named in the GROUP BY clause. The exception is that the select-list may contain aggregate functions.
ORDER BY clause This clause sorts the results of a query. Each item in the ORDER BY list can be labeled as ASC for ascending order (the default) or DESC for descending order. If the expression is an integer n, then the query results will be sorted by the nth item in the select list.
The only way to ensure that rows are returned in a particular order is to use ORDER BY. In the absence of an ORDER BY clause, UltraLite returns rows in whatever order is most efficient. This means that the appearance of result sets may vary depending on when you last accessed the row and other factors.
The SELECT statement is used for retrieving results from the database.
How many employees are there?
SELECT count(*) FROM employee