Switch to: V11V10V9V8V7V6V5

SELECT

This command is for retrieving rows from the database and getting the selection of one or many rows or columns from one or many tables. SELECT's main statements are:



select_stmt
	SELECT [DISTINCT | ALL] select_expr [, select_expr ... ] 
	from_statement 
	[ where_statement ] 
	[ group_by_statement ] 
	[ having_statement ]
	[ order_by_statement ] 
	[ select_limit ] 

Note, the order of statements is strict.

	SELECT * FROM T
 
	SELECT ** FROM T
 
	SELECT f1, f2 FROM T
	SELECT T.f1, T.f2 FROM T
 
	SELECT f1 AS 'Name', f2 FROM T
 
	SELECT 1 + 1
  • If there are any blanks in the field name, it should be included into brackets, e.g. [First Name] should be used for “First Name” column name.
  • Star (*) is used for selecting all fields in the table excluding methods (calculated fields) and internal fields (RecID and OID).
  • Double star (**) is used for getting all table fields (including methods and internal fields).
  • If FROM statement's table list refers to more than one table and these tables have same-named fields you should qualify field name with table name - TableName.FieldName - for avoiding ambiguity.
  • Each table has two internal fields - “RecID” and “OID”. You can use it in your queries

SELECT statement

Describes the columns of the resulting cursor. It is a comma-separated list. It can contain field names, constants, expressions. Each select-list element defines the source of the data for the column. Usually it is a reference to a source table field the data is coming from, but can be any other expression. One field will be created for each element of this list in the query results' table. The fields in the result' table will be arranged in the same order as the elements of the selected list.

So, select-list element can represent:

  • field name identifying one of the fields contained in the tables which are listed in FROM

statement. DBMS just takes this field value for each record of the source table and putting it to the corresponding record of the query result table;

  • “pure data” (constants or non-correlated expressions - such as 1+1 ). Each record of query result will contain the same value;
  • expression. DBMS should compute the value placed into the query result using the formula defined in the expression.
  • Star (*). It's select list element which means - all source table's fields, but excluding source table's methods and internal fields.
  • Double star (**). It's select list element which means - all source table fields, including source table's methods and internal fields.
  • ALL. This keywords used by default. It means - select records including duplicates. You can omit this keyword in all cases.
  • DISTINCT. This keyword eliminates duplicate rows from the results of a SELECT statement.

FROM Statement

Contains a list of the tables from which the query retrieving the data. The FROM statement can also contain join specifications, which define the rules for getting join of two or more tables. FROM statement consists of the FROM keyword followed by the comma separated list of the table definitions.

WHERE Statement

The WHERE statement works like source-records filter. It defines the conditions each row in the source tables must meet to qualify for the result. So, resulting table can be blank - if no source rows meet the conditions. WHERE statement consists of WHERE keyword followed by the selection condition. All table records are scrolled one by one and the selection condition is applied to each of them. If the record comes at the selection condition, the value of the field is used to form the current result record. For each of the records the selection condition can have one of three listed values.


WHERE statement consists of WHERE key word followed by the selection condition. All table records are scrolled one by one and the selection condition is applied to each of them. If the record name comes at the selection condition, the value of the field is used form the current record. For each of the records the selection condition can have one of three listed values.

  • If selection condition has TRUE value, the record will be included into query result.
  • If selection condition has FALSE value, the record is deleting out of query result.
  • If selection condition has NULL value, the record is deleting out of query result.


There are five main kind of selection conditions:

GROUP BY Statement

This statement gives the possibility to create row-selection as usual query does. Then perform grouping on values mentioned in the columns of the group_by_list. And finally, select rows with grouping results only - one result row per one source records group.

HAVING Statement

HAVING statement almost every time is used in combination with GROUP BY statement. However syntax of SELECT command doesn't demand it. If HAVING statement is used without GROUP BY statement, DBMS considers whole query result as one group. In other words, aggregative functions contained in HAVING statement, should be applied to one and only one group, and this group consists of all records. In practice HAVING statement is used without corresponding GROUP BY statement very rarely.


Group Selection Conditions.

ORDER BY Statement

It is for sorting query result purpose. This statement contains comma separated list of column names or (and) column order numbers. The first column is the main sorting key; the following columns are the more minor keys.

You can sort the query results by any element of the returning fields' list.

In this statement you can use the ascending or descending sorting order. By default the data is sorting in ascending order. To sort in descending order you should insert DESC key word into the sorting statement.

SELECT city, region, sales
FROM offices
ORDER BY sales DESC

To define the ascending sorting order you should use ASC key word.

If resulting field used for the sorting is an expression - it is possible that it has no name which can be mentioned in the ORDER BY statement. In this case you should use column order number instead of its name.

SELECT city, region, (sales - target)
FROM offices
ORDER BY 3 DESC

In the given example the query result which we've got is sorted according to the third field. The values of this field are the difference between SALES and TARGET fields' values for each office value.

You can use simultaneously names and numbers of the fields in ORDER BY statement as ascending and descending sorting order.