6.1.2. The SELECT Columns List

The columns list contains one or more comma-separated value expressions. Each expression provides a value for one output column. Alternatively, * (“select star”) can be used to stand for all the columns in a relation (i.e. a table, view or selectable stored procedure).

Syntax

SELECT
  [...]
  [DISTINCT | ALL] <output-column> [, <output-column> ...]
  [...]
  FROM ...
<output-column> ::=
    [<qualifier>.]*
  | <value-expression> [COLLATE collation] [[AS] alias]
<value-expression> ::=
    [<qualifier>.]table-column
  | [<qualifier>.]view-column
  | [<qualifier>.]selectable-SP-outparm
  | <literal>
  | <context-variable>
  | <function-call>
  | <single-value-subselect>
  | <CASE-construct>
  | any other expression returning a single
    value of a Firebird data type or NULL
<qualifier> ::= a relation name or alias

Description

It is always valid to qualify a column name (or “*”) with the name or alias of the table, view or selectable SP to which it belongs, followed by a dot (‘.’). For example, relationname.columnname, relationname.*, alias.columnname, alias.*. Qualifying is required if the column name occurs in more than one relation taking part in a join. Qualifying “*” is always mandatory if it is not the only item in the column list.

The column list may optionally be preceded by one of the keywords DISTINCT or ALL:

• DISTINCT filters out any duplicate rows. That is, if two or more rows have the same values in every corresponding column, only one of them is included in the result set

• ALL is the default: it returns all of the rows, including duplicates. ALL is rarely used; it is supported for compliance with the SQL standard.

A COLLATE clause will not change the appearance of the column as such. However, if the specified collation changes the case or accent sensitivity of the column, it may influence:

• The ordering, if an ORDER BY clause is also present and it involves that column

• Grouping, if the column is part of a GROUP BY clause

• The rows retrieved (and hence the total number of rows in the result set), if DISTINCT is used

Examples of SELECT queries with different types of column lists

A simple SELECT using only column names:

select cust_id, cust_name, phone
  from customers
  where city = 'London'

A query featuring a concatenation expression and a function call in the columns list:

select 'Mr./Mrs. ' || lastname, street, zip, upper(city)
  from contacts
  where date_last_purchase(id) = current_date

A query with two subselects:

select p.fullname,
  (select name from classes c where c.id = p.class) as class,
  (select name from mentors m where m.id = p.mentor) as mentor
from pupils p

The following query accomplishes the same as the previous one using joins instead of subselects:

select p.fullname,
  c.name as class,
  m.name as mentor
  join classes c on c.id = p.class
from pupils p
  join mentors m on m.id = p.mentor

This query uses a CASE construct to determine the correct title, e.g. when sending mail to a person:

select case upper(sex)
    when 'F' then 'Mrs.'
    when 'M' then 'Mr.'
    else ''
  end as title,
  lastname,
  address
from employees

Querying a selectable stored procedure:

select * from interesting_transactions(2010, 3, 'S')
  order by amount

Selecting from columns of a derived table. A derived table is a parenthesized SELECT statement whose result set is used in an enclosing query as if it were a regular table or view. The derived table is shown in bold here:

select fieldcount,
  count(relation) as num_tables
from (select r.rdb$relation_name as relation,
        count(*) as fieldcount
      from rdb$relations r
        join rdb$relation_fields rf
          on rf.rdb$relation_name = r.rdb$relation_name
      group by relation)
group by fieldcount

Asking the time through a context variable (CURRENT_TIME):

select current_time from rdb$database

For those not familiar with RDB$DATABASE: this is a system table that is present in all Firebird databases and is guaranteed to contain exactly one row. Although it wasn’t created for this purpose, it has become standard practice among Firebird programmers to select from this table if you want to select “from nothing”, i.e., if you need data that are not bound to a any table or view, but can be derived from the expressions in the output columns alone. Another example is:

select power(12, 2) as twelve_squared, power(12, 3) as twelve_cubed
  from rdb$database

Finally, an example where you select some meaningful information from RDB$DATABASE itself:

select rdb$character_set_name from rdb$database

As you may have guessed, this will give you the default character set of the database.

See also

Functions, Aggregate Functions, Context Variables, CASE, Subqueries