Listing Columns to Retrieve with SELECT

From InterBase

Go Up to Understanding Data Retrieval with SELECT


A list of columns to retrieve must always follow the SELECT keyword in a SELECT statement. The SELECT keyword and its column list is called a SELECT clause.

Retrieving a List of Columns

To retrieve a subset of columns for a row of data, list each column by name, in the order of desired retrieval, and separate each column name from the next by a comma. Operations that retrieve a subset of columns are often called projections.

For example, the following SELECT retrieves three columns: 

EXEC SQL
SELECT EMP_NO, FIRSTNAME, LASTNAME
INTO :emp_no, :fname, :lname
FROM EMPLOYEE WHERE EMP_NO = 2220;

Retrieving All Columns

To retrieve all columns of data, use an asterisk (*) instead of listing any columns by name. For example, the following SELECT retrieves every column of data for a single row in the EMPLOYEE table: 

EXEC SQL
SELECT *
INTO :emp_no, :fname, :lname, :phone_ext, :hire, :dept_no,
:job_code, :job_grade, :job_country, :salary, :full_name
FROM EMPLOEE WHERE EMP_NO = 1888;
Important:
You must provide one host variable for each column returned by a query.

Eliminating Duplicate Columns with DISTINCT

In a query returning multiple rows, it may be desirable to eliminate duplicate columns. For example, the following query, meant to determine if the EMPLOYEE table contains employees with the last name, SMITH, might locate many such rows: 

EXEC SQL
DECLARE SMITH CURSOR FOR
SELECT LAST_NAME
FROM EMPLOYEE
WHERE LAST_NAME = 'Smith';

To eliminate duplicate columns in such a query, use the DISTINCT keyword with SELECT. For example, the following SELECT yields only a single instance of “Smith”: 

EXEC SQL
DECLARE SMITH CURSOR FOR
SELECT DISTINCT LAST_NAME
FROM EMPLOYEE
WHERE LAST_NAME = 'Smith';

DISTINCT affects all columns listed in a SELECT statement.

Retrieving Aggregate Column Information

SELECT can include aggregate functions, functions that calculate or retrieve a single, collective numeric value for a column or expression based on each qualifying row in a query rather than retrieving each value separately. The following table lists the aggregate functions supported by InterBase:

Aggregate functions in SQL
Function Purpose

AVG()

Calculates the average numeric value for a set of values.

MIN()

Retrieves the minimum value in a set of values.

MAX()

Retrieves the maximum value in a set of values.

SUM()

Calculates the total of numeric values in a set of values.

COUNT()

Calculates the number of rows that satisfy the query’s search condition (specified in the WHERE clause).

For example, the following query returns the average salary for all employees in the EMPLOYEE table: 

EXEC SQL
SELECT AVG(SALARY)
INTO :avg_sal
FROM EMPLOYEE;

The following SELECT returns the number of qualifying rows it encounters in the EMPLOYEE table, both the maximum and minimum employee number of employees in the table, and the total salary of all employees in the table: 

EXEC SQL
SELECT COUNT(*), MAX(EMP_NO), MIN(EMP_NO), SUM(SALARY)
INTO :counter, :maxno, :minno, :total_salary
FROM EMPLOYEE;

If a field value involved in an aggregate calculation is NULL or unknown, the entire row is automatically excluded from the calculation. Automatic exclusion prevents averages from being skewed by meaningless data.

Note:
Aggregate functions can also be used to calculate values for groups of rows. The resulting value is called a group aggregate. For more information about using group aggregates, see Grouping Rows with GROUP BY.

Multi-table SELECT Statements

When data is retrieved from multiple tables, views, and select procedures, the same column name may appear in more than one table. In these cases, the SELECT statement must contain enough information to distinguish similarly-named columns from one another.

To distinguish column names in multiple tables, precede those columns with one of the following qualifiers in the SELECT clause:

  • The name of the table, followed by a period. For example,EMPLOYEE.EMP_NO identifies a column named EMP_NO in the EMPLOYEE table.
  • A table correlation name (alias) followed by a period. For example, if the correlation name for the EMPLOYEE table is EMP, then EMP.EMP_NO identifies a column named EMP_NO in the EMPLOYEES table.

Correlation names can be declared for tables, views, select procedures and derived tables in the FROM clause of the SELECT statement. For more information about declaring correlation names, and for examples of their use, see Declaring and Using Correlation Names.

Advance To:

Retrieved from "http://docwiki.embarcadero.com/InterBase/2020/e/index.php?title=Listing_Columns_to_Retrieve_with_SELECT&oldid=31166"