SQL SELECT Clause

The SELECT clause is used to retrieve data from tables. You can use the SELECT clause to retrieve,

• a single line, or part of a line, from a table, referred to as a singleton select
• multiple rows, or parts of rows, from a table
• related rows, or parts of rows, from a join of two or more tables

The syntax is as follows:

SELECT [FIRST number_of_rows] [DISTINCT | ALL] * | field | parameter [AS correlation_name | correlation_name], [field | parameter ...]

FROM table_reference [AS correlation_name | correlation_name]

[[[[INNER | [LEFT | RIGHT | FULL] OUTER JOIN] table_reference [AS correlation_name | correlation_name] ON join_condition]

[WHERE predicates]

[GROUP BY group_list]

[HAVING predicates]

[ORDER BY order_list]

[UNION [ALL] [SELECT...]]

The SELECT clause defines the list of items returned by the SELECT statement. The SELECT clause uses a comma-separated list composed of table fields, literal values, and field or literal values modified by functions. You cannot use parameters in this list of items. Use an asterisk to retrieve values from all fields. Fields in the field list for the SELECT clause can come from more than one table, but can only come from those tables listed in the FROM clause.

The FROM clause identifies the table from which data is retrieved.

The following example retrieves data for two fields in all rows of a table:

  SELECT CustomerCode, CustomerName from ARCUST

You can use the AS keyword to specify a field correlation name, or alternately you can simply just specify the field correlation name after the selected field. The following example uses both methods to give each selected field a more descriptive name in the query result set:

  SELECT ARCUST.CustomerCode AS "Customer Code",

    ARCUST.CustomerName AS "Customer Name",

    INHEAD.DocumentNo AS "Invoice Number",

    SUM(INLINE.QuantitySupplied) "Total Qty"

  FROM ARCUST LEFT OUTER JOIN INHEAD ON ARCUST.CUSTOMERCODE = INHEAD.CUSTOMERCODE

  LEFT OUTER JOIN INLINE ON INHEAD.DOCUMENTID = INLINE.DOCUMENTID

  WHERE ARCUST.CustomerName like '%Furn%'

  GROUP BY ARCUST.CustomerCode, ARCUST.CustomerName, INHEAD.DocumentNo

  ORDER BY ARCUST.CustomerCode

Use DISTINCT to limit the retrieved data to only distinct rows. The distinctness of rows is based on the combination of all of the fields in the SELECT clause fields list.

Sub query for select fields

Sub-queries are often referred to as sub-selects, as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A sub-query is executed by enclosing it in a set of parentheses.

  Select

  FieldA,

  FieldB,

  FieldC,

  (Select ......From .... Where ...),

  From

  TableA

For example:

  Select ARCUST.CustomerCode,

  (Select Sum(TransactionAmount) from ARTRAN Where ARTRAN.CustomerCode = ARCUST.CustomerCode)

  From ARCUST

Note: You can manipulate SQL Query performance using indices. See SQL Select Clause Indexing.