SELECT SQL Command
This is the most frequently used SQL clause. It is destined for fetching data from one or more DBF tables. The syntax is:
FROM Table_List_Item [, ...]
[[JoinType] JOIN] Table [[AS] Local_Alias]
[ON JoinCondition [AND | OR [JoinCondition | FilterCondition] ...]
[WHERE JoinCondition | FilterCondition [AND | OR JoinCondition | FilterCondition] ...]
[GROUP BY Column_List_Item [, ...]]
[HAVING FilterCondition [AND | OR ...]]
[ORDER BY Field_Name [ASC | DESC] [, ...]]
[INTO TABLE | DBF | CURSOR DBF_Name]
It's consist of a few of separate clauses. They are listed below.
SELECT SQL Clause
SELECT [ALL | DISTINCT] [TOP nExpr [PERCENT]] Select_List_Item [AS Column_Name] [, ...]
Parameters
[ALL | DISTINCT]
ALL is a default value. Display all records in the query results. Use DISTINCT to exclude duplicates of any records from the query results. You can use one DISTINCT clause only per a SELECT clause.
[TOP nExpr [PERCENT]]
TOP specifies the number of records a query result contains. In this case, nExpr can be from 1 up to 32,767.
PERCENT specifies percentage of records in the query result. For nExpr you can specify from 0.01 up to 99.99 percent.
DBF Commander sorts records first and then extracts the specified amount (TOP or PERCENT) of records.
Select_List_Item
Specifies one or more items to match and include in the query results. Each item in the list generates one field in the query results. Select_List_Item can be:
- A constant value that appears in every row of the query results.
- An expression that can contain function or subquery.
- [Table_Name.]Select_Item
Select_Item is a field from a table in the FROM clause.
Table_Name specifies a DBF file name (without path and .dbf extension) when you are retrieving two or more Select_Item fields with the same name from multiple DBF files. Using a table name prevents columns from being duplicated. - (Subquery)
Subquery specifies a SQL SELECT statement within another SQL SELECT statement and must be enclosed within parentheses. There is no limit on the number of subqueries per SQL SELECT statement. There is no limit on the nesting depth for subqueries in a SQL SELECT statement.
You can use correlated subqueries up to the immediate parent. A correlated subquery uses fields in the parent query, and the subquery is executed for each candidate record in the parent query. Subqueries can contain TOP clauses for noncorrelated subqueries, multiple join conditions, and GROUP BY clauses. If a subquery does not return any records, it returns NULL.
You can use aggregate functions with a Select_List_Item that is a field or an expression involving a field. When specifying aggregate functions associated with Select_Item, use the following syntax:
More detailed information about aggregate functions you can find below.
Specifies a name for a column in the query output. Column_Name can be an expression, but it cannot contain special characters (spaces, dots, etc.)
Aggregate Functions
You can use aggregate functions with a Select_Item that is a field or an expression involving a field. Also, it can be used within a filter condition in the HAVING clause. However, nesting aggregate functions is not allowed. The following aggregate functions can be used in SQL SELECT statements:
- AVG() - averages a field of numeric data.
- COUNT() or CNT() - counts the number of select items in a specified field. COUNT(*) counts the number of records in the result query.
- MIN() and MAX() - determines the smallest (and largest) value of Select_Item in a column.
- SUM() - totals a field of numeric data.
The following example shows using of aggregate functions:
Field Names
The following rules apply when you specify a column with the AS clause to display results in a separate field:
- If Select_Item is a field with a unique name, the output column name is the field's name.
- If more than one Select_Item has the same name, and the name has 10 or more characters, the name is truncated to add a number. For example, an output column name such as "Department" would appear as "Department" and "Departmen2". If the length of the equal field names less than 10 letter, the column name is padded with underscore ("_"), following with field number. E.g., duplicate field name "Dep" will appears as "Dep______2", "Dep______3", and so on.
Try to use the AS clause to give the column a meaningful name. This helps you to avoid of duplicate field names and further errors in SQL syntax
- If Select_Item is an expression, the output column name appears as Exp_1. Additional output column names appear as Exp_2, Exp_3, and so on. If an aggregate function, such as COUNT(), is used with Select_Item, the output column is named Cnt_FieldName. If more than one Select_Item is used with aggregate functions, the output columns are named AggregateFunction_FieldName with the aggregate function names abbreviated as needed.
Examples
This SQL query selects 3 fields from 2 tables (with aliases "t1" and "t2"). The tables are joined by "cust_id" fields:
FROM customer AS t1, orders AS t2
WHERE t1.cust_id = t2.cust_id
Simple TOP clause example:
FROM customer
ORDER BY company
FROM Clause
The FROM clause specifies one or more tables containing the data that the query retrieves from. The detailed syntax for the FROM clause:
[[JoinType] JOIN] Table [[AS] Local_Alias]
[ON JoinCondition [AND | OR [JoinCondition | FilterCondition] ...]
Parameters
Specifies one or more tables that contain the data the query retrieves from.
Table_List_Item can have the following syntaxes:
- DBF_Name [[AS] Table_Alias]
DBF_Name specifies the name of the DBF file you want to retrieve data from. If DBF_Name contains file name only (without path), this DBF file is being looked at the same directory, from where the last DBF file was opened.
If the table hasn't been opened, DBF Commander opens the file automatically. It remains open when the query is complete. Table_Alias specifies a temporary name for the DBF file. If you specify a table alias, you must use this local alias instead of the file name throughout the SELECT statement. There is no limit on the number of tables and aliases per SELECT statement. You can also specify multiple tables by using one or more JOIN clauses. - (Subquery) AS Subquery_Alias
Subquery specifies a SELECT statement within another SELECT statement.
In the FROM clause, each subquery requires an alias. Subqueries in the FROM clause do not have the restrictions that apply to subqueries in filter conditions and can use any SQL SELECT clauses, including UNION clauses. All subqueries in the FROM clause are executed before the main SELECT statement is evaluted.
[[ JoinType] JOIN ] Table_Name[[AS] Table_Alias]
Specifies a JOIN clause for retrieving data from more than one table. DBF Commander supports nested joins. There is no limit on the number of joins per SELECT statement.
The following table describes the different types of joins you can specify with JoinType:
- INNER - query result contains only rows from a table that match one or more rows in another table. (Default)
- LEFT [OUTER] - query result contains all rows from the table to the left of the JOIN keyword and only matching rows from the table to the right of the JOIN keyword.
- RIGHT [OUTER] - query result contains all rows from the table to the right of the JOIN keyword and only matching rows from the table to the left of the JOIN keyword.
- FULL [OUTER] - query result contains all matching and nonmatching rows from both tables.
In the last 3 cases, the OUTER keyword is optional. You can include it to clarify that an outer join is created.
Specifies conditions on which tables in a SQL SELECT statement are joined or results are filtered. Join conditions can also include filter conditions. For multiple join or filter conditions, you must use the AND or OR operator to connect those conditions. You can include subqueries and nested subqueries in the ON clause.
JoinCondition
Specifies conditions that can contain the following:
- A comparison condition between fields from different tables, for example:
Field_Name1 Comparison Field_Name2
Field_Name1 specifies a field name from one table, and Field_Name2 specifies a field name from another table. - A comparison condition containing expressions involving fields from different tables, for example:
Table1.Field1 + Table2.Field1 Comparison Table3.Field1
The Comparison can be one of the comparison operators: =, <, >, <=, >=, >< (or !=, or # - not equal also), == (exactly equal), LIKE (SQL LIKE operator)
FilterCondition
Specifies a logical expression that describes filter criteria that records must meet to be included in the query results. You can include multiple filter conditions in a query by connecting them with the AND or OR operator. To check for an empty field, use the EMPTY() function.
FilterCondition has the same syntax as JoinCondition. In addition, FilterCondition can specify the following syntaxes:
or
The following additional options available on specifying FilterCondition:
- Expression - Field_Name values must match comparison with this expression in order to be included in the query results.
- LIKE "cExpression" - Field_Name values must match the character expression "cExpression" (case sensitively), which can contain SQL wildcard characters: percent "%" and underscore "_".
The "%" character represents any sequence of any characters in the string, the "_" character represents a single character. E.g.: LIKE "SomeText%" - IS NULL - Field_Name value must be null for inclusion in the query results.
- BETWEEN Start_Range AND End_Range - Field_Name values must be within a specified range of values for inclusion in the query results.
- IN (Value_Set) - Field_Name must contain one of the values or expressions specified in Value_Set. Items in the value set must be separated with commas.
Place values most likely to match in the begin of the Value_Set. This will improve the performance of the SQL query.
WHERE Clause
The WHERE clause specifies join and filter conditions that determine the rows that the query returns. Join operations in the WHERE clause function the same as JOIN operations in the FROM clause. The detailed syntax for the WHERE clause is as follows:
JoinCondition and FilterCondition syntax is similar to described above
Example
Join 2 tables by "cust_id" field for only those records that have an order date earlier than 02/16/2012:
FROM customer t1, orders t2
WHERE t1.cust_id = t2.cust_id AND t2.order_date < {^2012-02-16}
GROUP BY Clause
The GROUP BY clause specifies one or more columns used to group records returned by the SQL query. Field names referenced in the SQL SELECT statement list, except for aggregate expressions, must be included in the GROUP BY clause. Grouping by Memo, General, or Blob fields is not permitted. The detailed syntax for the GROUP BY clause is as follows:
Parameters
Specifies one or more column names used to group records returned by the query. It can be one of the following:
- A field in a table in the FROM clause or a subquery.
- A table alias from the SQL SELECT list.
- A numeric expression indicating the location of the column in the result table. The leftmost column is number 1.
Example
Selects distinct records summed by "order_amt" field, and grouped by "to_country", "to_name", "to_address" fields:
FROM orders
GROUP BY to_country, to_name, to_address
HAVING Clause
The HAVING clause specifies conditions that determines the groups included in the query. If the SQL SELECT statement does not contain aggregate functions, you can use a SQL SELECT statement that contains a HAVING clause without a GROUP BY clause. In this case, the HAVING clause acts like the WHERE clause.
The detailed syntax for the HAVING clause is as follows:
Parameters
The same as FilterCondition in FROM clause, but the HAVING clause can contain aggregate functions.
Example
The same query as in GROUP BY example, but with HAVING clause:
FROM orders
GROUP BY to_country, to_name, to_address
HAVING order_sum>20000
ORDER BY Clause
The ORDER BY clause specifies one or more field names used to sort the query results and the order for sorting the results. The detailed syntax for the ORDER BY clause:
Parameters
Specifies the column name (or it's alias) used to sort the query result set. It cannot be a Blob or General field type.
Also, it can be a numeric expression indicating the number of the column in the result table. The leftmost column is number 1.
Specifies an ascending (or descending) order for the query results. ASC is the default value.
Example
Order by fields "company" (ascending) and price (descending):
FROM customer
ORDER BY company, price DESC
INTO TABLE t2
INTO Clause
The INTO clause specifies a DBF file name to save the final query results to. The detailed syntax for the INTO clause is as follows:
Parameters
On using of TABLE or DBF reserved words, DBF_Name specifies the name of the DBF file.
In case of using the INTO CURSOR clause, the query results are saved to temporary DBF file (cursor) in current user's Application Data directory. For example, "C:\Documents and Settings\username\Application Data\DBF Commander Professional\Query.dbf". After the application is closed, those files is deleted from disk. E.g.:
If you don't use the INTO clause in SQL SELECT statement at all, the temporary cursor "Query.dbf" will contain the result record set.