Firebird column alias in the where clause

SELECT¶

Categories:

Query syntax

SELECT can be used either as a statement or as a clause within other statements:

  • The SELECT statement is the most frequently executed SQL statement. It queries the database and fetches a lot of rows.

  • As a clause, SELECT defines the set of columns returned by a query.

See also:

Query syntax.

Syntax¶

[...] SELECT [{ALL | DISTINCT}] {[{ | }.] * | [{ | }.] | [{ | }.] $ | [[AS] ]} [, ...] [...]

For more information about SELECT as a statement and the other clauses in that statement, see Query Syntax.

Parameter¶

Specifies whether duplicates should be removed from the result set:

  • includes all values ​​in the result set.

  • removes duplicate values ​​from the result set.

Default:

The asterisk is an abbreviation to indicate that the output should include all columns of the specified object or all columns of all objects if not qualified with an object name or alias.

or.

Specifies the object identifier or object alias as defined in the FROM clause.

Specifies the column identifier as defined in the FROM clause.

Indicates the position of the column (1-based) as defined in the FROM clause. If a column is referenced from a table, this number cannot exceed the maximum number of columns in the table.

Specifies an expression, such as a math expression, that gives a specific value for a specific row.

Specifies the column alias assigned to the resulting expression. This is used as a display name in a top-level SELECT list and as a column name in an inline view.

Usage notes¶

  • By default, aliases and identifiers are not case-sensitive. To be case-sensitive, enclose them in quotation marks (). For more information, see Object Identifiers.

  • Without an ORDER BY clause, the results returned by SELECT are an unordered set. Running the same query repeatedly on the same tables can result in a different output order each time. If the order matters, use the clause.

  • In addition to being used as an independent statement, SELECT can also be used as a clause in other statements, such as: B. SELECT can also be used in a subquery within a statement.

Examples¶

Below are a few simple examples.

Many examples are included in other parts of the documentation, including detailed descriptions of query syntax.

Some of the queries below use the following tables and data:

CREATETABLEemployee_table (employee_IDINTEGER, last_nameVARCHAR, first_nameVARCHAR, department_IDINTEGER); CREATETABLEdepartment_table (department_IDINTEGER, department_nameVARCHAR);
INSERTINTOemployee_table (employee_ID, last_name, first_name, department_ID) VALUES (101, 'Montgomery', 'Pat', 1), (102, 'Levine', 'Terry', 2), (103, 'Comstock', 'Dana', 2); INSERTINTOdepartment_table (department_ID, department_name) VALUES (1, 'Engineering'), (2, 'Customer Support'), (3, 'Finance');

This example shows how to search for an employee's last name if you know the ID.

SELECTlast_nameFROMemployee_tableWHEREemployee_ID = 101; + ------------ + | LAST_NAME || ------------ || Montgomery | + ------------ +

This example lists all employees and the name of the department in which each employee works. The order in which they are output is based on the department name and, within the individual department, according to the names of the employees. This query is used to relate the information in one table to the information in another table.

SELECTdepartment_name, last_name, first_nameFROMemployee_tableINNERJOINdepartment_tableONemployee_table.department_ID = department_table.department_IDORDERBYdepartment_name, last_name, first_name; + ------------------ + ------------ + - ----------- + | DEPARTMENT_NAME | LAST_NAME | FIRST_NAME || ------------------ + ------------ + ------------ || Customer Support | Comstock | Dana || Customer Support | Levine | Terry || Engineering | Montgomery | Pat | + ------------------ + ------------ + ------------ +

This example shows how to identify a column by column number instead of column name:

SELECT $ 2FROMemployee_tableORDERBY $ 2; + ------------ + | $ 2 || ------------ || Comstock || Levine || Montgomery | + ------------ +

This example shows that the output columns do not have to be taken directly from the tables in the clause. The output columns can be general expressions. This example calculates the area of ​​a circle with radius 2.0. This example also shows how to use a column alias so that the output has a meaningful column name:

SELECTpi () * 2.0 * 2.0ASarea_of_circle; + ---------------- + | AREA_OF_CIRCLE || ---------------- || 12.566370614 | + ---------------- +