SELECT

From InterBase
Jump to: navigation, search

Go Up to Statement and Function Reference (Language Reference Guide)


Retrieves data from one or more tables. Available in gpre, DSQL, and isql.

Syntax

SELECT [TRANSACTION transact] [DISTINCT | ALL] {* | <val> [, <val> ]} [INTO :var [, :var ]]
  FROM <tableref> [, <tableref> ]
[WHERE <search_condition>]
[GROUP BY col [COLLATE collation] [, col [COLLATE collation] ] [HAVING <search_condition>] 
[UNION [ALL] select_expr][PLAN <plan_expr>]
[ORDER BY <order_list>]
[ROWS value [TO upper_value] [BY step_value][PERCENT][WITH TIES]]
[FOR UPDATE [OF col [, col ]]];
val = {col [array_dim] 
  | :variable | constant | expr 
  | funct | udf ([val [, val ]]) 
  | NULL | USER | RDB$DB_KEY | ? }
  [COLLATE collation] [AS alias]

array_dim = [[x:]y [, [x:]y ]]

constant = num | 'string' | charsetname 'string'

funct = COUNT (* | [ALL] val | DISTINCT val)
  | SUM ([ALL] val | DISTINCT val)
  | AVG ([ALL] val | DISTINCT val)
  | MAX ([ALL] val | DISTINCT val)
  | MIN ([ALL] val | DISTINCT val)
  | CAST (val AS data_type)
  | UPPER (val)
  | GEN_ID (generator, val)

tableref = <joined_table> | <table_primary>

joined_table = tableref join_type JOIN tableref
  ON search_condition | (joined_table)
  
join_type = [INNER] JOIN
  | {LEFT | RIGHT | FULL } [OUTER]}

search_condition = val operator {val | (select_one)}
  | val [NOT] BETWEEN val AND val
  | val [NOT] LIKE val [ESCAPE val]
  | val [NOT] IN (val [, val ] | select_list)
  | val IS [NOT] NULL
  | val {>= | <=} val
  | val [NOT] {= | < | >} val
  | {ALL | SOME | ANY} (select_list)
  | EXISTS (select_expr)
  | SINGULAR (select_expr)
  | val [NOT] CONTAINING val
  | val [NOT] STARTING [WITH] val
  | (search_condition)
  | NOT search_condition
  | search_condition OR search_condition
  | search_condition AND search_condition

operator = {= | < | > | <= | >= | !< | !> | <> | !=}

table_primary = [{table | view | procedure} [[AS] alias]] | <derived_table>
  
derived_table = query_expression [AS] alias

plan_expr = [JOIN | [SORT] [MERGE]] ({plan_item | plan_expr}
  [, {plan_item | plan_expr} ])

plan_item = {table | alias}
  {NATURAL | INDEX (index [, index ]) | ORDER index}

order_list = {col | int} [COLLATE collation]
  [ASC[ENDING] | DESC[ENDING]]
  [, order_list ]
Argument Description

<expr>

A valid SQL expression that results in a single value.

<select_one>

A SELECT on a single column that returns exactly one value.

<select_list>

A SELECT on a single column that returns zero or more rows.

<select_expr>

A SELECT on a list of values that returns zero or more rows.

Argument Description

TRANSACTION transact

Name of the transaction under control of which the statement is executed; SQL only.

SELECT [DISTINCT | ALL]

Specifies data to retrieve

  • DISTINCT prevents duplicate values from being returned.
  • ALL, the default, retrieves every value.

{* | <val> [, <val> ]}

The asterisk (*) retrieves all columns for the specified tables.

<val> [, <val> ] retrieves a list of specified columns, values, and expressions.

INTO :<var> [, <var> ]

Singleton select in embedded SQL only; specifies a list of host-language variables into which to retrieve values.

FROM <tableref> [, <tableref> ]

List of tables, views, stored procedures or derived tables from which to retrieve data; list can include joins and joins can be nested.

<joined_table>

A table reference consisting of a JOIN.

<join_type>

Type of join to perform. Default: INNER.

<table_primary>

Name of an existing table, view, stored procedure or a derived table.

alias

Alternate name for a table, view, or column.

<derived_table>

A result set of a SELECT query that you can use in the FROM clause. See Derived Tables (SELECT FROM SELECT) for more information and examples.

WHERE <search_condition>

  • Specifies a condition that limits rows retrieved to a subset of all available rows.
  • A WHERE clause can contain its own SELECT statement, referred to as a subquery.

GROUP BY col [, col ]

Groups related rows based on common column values; used in conjunction with HAVING. To learn about the enhancements to GROUP BY introduced in InterBase 2017 Update 1 Refer to: Enhancements to GROUP BY and ORDER BY.

COLLATE collation

Specifies the collation order for the data retrieved by the query.

HAVING <search_condition>

Used with GROUP BY; specifies a condition that limits the grouped rows returned.

UNION [ALL]

  • Combines the results of two or more SELECT statements to produce a single, dynamic table without duplicate rows.
  • The ALL option keeps identical rows separate instead of folding them together into one.

PLAN <plan_expr>

Specifies the query plan that should be used by the query optimizer instead of one it would normally choose.

<plan_item>

Specifies a table and index method for a plan.

ORDER BY <order_list>

Specifies columns to order, either by column name or ordinal number in the query, and the sort order (ASC or DESC) for the returned rows. To learn about the enhancements to ORDER BY introduced in InterBase 2017 Update 1 Refer to: Enhancements to GROUP BY and ORDER BY.

ROWS <value>

[TO <upper_value>]

[BY <step_value>]

[PERCENT][WITH TIES]

  • <value> is the total number of rows to return if used by itself.
  • <value> is the starting row number to return if used with TO.
  • <value> is the percent if used with PERCENT.
  • <upper_value> is the last row or highest percent to return.
  • If <step_value> = <n>, returns every <n>th row, or <n> percent rows.
  • PERCENT causes all previous ROWS values to be interpreted as percents.
  • WITH TIES returns additional duplicate rows when the last value in the ordered sequence is the same as values in subsequent rows of the result set; must be used in conjunction with ORDER BY.

FOR UPDATE

Specifies columns listed after the SELECT clause of a DECLARE CURSOR statement that can be updated using a WHERE CURRENT OF clause.

Description

SELECT retrieves data from tables, views, or stored procedures. Variations of the SELECT statement make it possible to:

  • Retrieve a single row or part of a row from a table. This operation is referred to as a singleton select.
Note: In embedded applications, all SELECT statements that occur outside the context of a cursor must be singleton selects.
  • Retrieve multiple rows, or parts of rows, from a table.
    • In embedded applications, multiple row retrieval is accomplished by embedding a SELECT within a DECLARE CURSOR statement.
    • In isql, SELECT can be used directly to retrieve multiple rows.
  • Retrieve related rows, or parts of rows, from a join of two or more tables.
  • Retrieve all rows, or parts of rows, from union of two or more tables.
  • Return portions or sequential portions of a larger result set; useful for Web developers, among others.

All SELECT statements consist of two required clauses (SELECT, FROM), and possibly others (INTO, WHERE, GROUP BY, HAVING, UNION, PLAN, ORDER BY, ROWS).

For more information on how to use SELECT in isql, see the Operations Guide. For a complete explanation of SELECT and its clauses, see the Embedded SQL Guide.

Derived Tables (SELECT FROM SELECT)

A derived table is the result set of a SELECT query that you can use in the FROM clause. You may find it useful to think of a derived table as a view with statement-level scope. This allows you the expressive flexibility to use a view-like structure without defining a database schema view, or allows the user to obtain the same benefit in an ad-hoc query without requiring a database administer to create a view definition.

You can use derived tables in triggers and stored procedures as well as user applications, but you must have proper access privileges on the underlying base tables and views accessed by a derived table.

Dynamic SQL and isql support derived table syntax, Embedded SQL does not support derived table syntax. For further info on Derived Tables refer to SQL Derived Table Support

Examples With Derived Tables

  1. The following simple example shows how you can use derived tables:
    SELECT elj.job_code,
           elj.job_title
    FROM   ( SELECT job_code,
                    job_title
             FROM   job
             WHERE  max_salary < 50000 ) AS elj;
    

    The statement queries the EMPLOYEE table for entry-level jobs.

  2. The following is a more complex statement using derived tables:
    SELECT emp.emp_no,
           emp.full_name,
           emp.job_code,
           job.job_grade,
           job.job_title
    FROM   ( SELECT emp_no,
                    full_name,
                    job_code,
                    job_grade,
                    job_country
             FROM   employee ) AS emp,
           ( SELECT job_code,
                    job_grade,
                    job_country,
                    job_title
             FROM   job ) AS job
    WHERE  ( emp.job_code = job.job_code ) AND
           ( emp.job_grade = job.job_grade ) AND
           ( emp.job_country = job.job_country ) AND
           ( emp.job_country = 'USA' );
    
  3. The following example shows a derived table with a subquery:
    SELECT eid,
           ename
    FROM   ( SELECT e.emp_no,
                    e.full_name
             FROM   employee e
             WHERE  e.job_country =
                    ( SELECT e1.job_country
                      FROM   employee e1
                      WHERE  emp_no = 144 ) ) AS emp (eid, ename);
    

Additional Notes on Derived Tables

  • Derived tables can be nested.
  • Derived tables can be unions and can be used in unions. They can contain aggregate functions, subselects and joins, and can themselves be used in aggregate functions, subselects and joins. They can also be or contain queries on selectable stored procedures.

Additional Notes on SELECT

  • When declaring arrays, you must include the outermost brackets, shown below in bold. For example, the following statement creates a 5 by 5 two-dimensional array of strings, each of which is 6 characters long:
    my_array = varchar(6)[5,5]
    
  • Use the colon (:) to specify an array with a starting point other than 1. The following example creates an array of integer that begins at 10 and ends at 20:
    my_array = integer[20:30]
    
  • In SQL and isql, you cannot use val as a parameter placeholder (like ?).
  • In DSQL and isql, val cannot be a variable.
  • You cannot specify a COLLATE clause for Blob columns.
  • You cannot specify a GROUP BY clause for Blob and array columns.

Examples

  1. The following isql statement selects columns from a table:
    SELECT job_grade,
           job_code,
           job_country,
           max_salary
    FROM   project;
    
  2. The next isql statement uses the * wildcard to select all columns and rows from a table:
    SELECT *
    FROM   countries;
    
  3. The following embedded SQL statement uses an aggregate function to count all rows in a table that satisfy a search condition specified in the WHERE clause:
    EXEC SQL
    SELECT COUNT (*)
    INTO   :cnt
    FROM   country
    WHERE  population > 5000000;
    
  4. The next isql statement establishes a table alias in the SELECT clause and uses it to identify a column in the WHERE clause:
    SELECT c.city
    FROM   cities c
    WHERE  c.population < 1000000;
    
  5. The following isql statement selects two columns and orders the rows retrieved by the second of those columns:
    SELECT city,
           state
    FROM   cities
    ORDER  BY state;
    
  6. The next isql statement performs a left join:
    SELECT    city,
              state_name
    FROM      cities c
    left join states s
    ON        s.state = c.state
    WHERE     c.city starting WITH 'San';
    
  7. The following isql statement specifies a query optimization plan for ordered retrieval, utilizing an index for ordering:
    SELECT   *
    FROM     cities
    PLAN (cities ORDER cities_1)
    ORDER BY city;
    
  8. The next isql statement specifies a query optimization plan based on a three-way join with two indexed column equalities:
    SELECT *
    FROM   cities c,
           states s,
           mayors m
    WHERE  c.city = m.city
    AND    c.state = m.state PLAN
    join   (state NATURAL, cities INDEX dupe_city, mayors INDEX mayors_1);
    
  9. The next example queries two of the system tables, RDB$CHARACTER_SETS and RDB$COLLATIONS to display all the available character sets, their ID numbers, number of bytes per character, and collations. Note the use of ordinal column numbers in the ORDER BY clause.
    SELECT rdb$character_set_name,
           rdb$character_set_id,
           rdb$bytes_per_character,
           rdb$collation_name
    FROM   rdb$character_sets
           join rdb$collations
             ON rdb$character_sets.rdb$character_set_id = rdb$collations.rdb$character_set_iefd
    ORDER  BY 1,4;
    
  10. The following examples reward the best performing sales people and terminate the least performing members of the sales team. The examples show how a Web developer, for example, could split the result set in half for display purposes.
    SELECT   salesman,
             sales_dollars,
             sales_region
    FROM     salespeople
    ORDER BY sales_dollars DESC
    rows 1 TO 50;
    
    SELECT   salesman,
             sales_dollars,
             sales_region
    FROM     salespeople
    ORDER BY sales_dollars DESC
    rows 50 TO 100 WITH ties;
    
  11. Reward the best 100 performing salesmen with a 15 percent bonus:
    UPDATE salespeople
    SET      sales_bonus = 0.15 * sales_dollars
    order BY sales_dollars DESC
    ROWS 100 WITH ties;
    
  12. Eliminate the worst five percent of the sales force:
    DELETE
    FROM     salespeople
    order by sales_dollars
    ROWS 5 percent WITH ties;
    

Enhancements to GROUP BY and ORDER BY

InterBase 2017 Update 1 introduces enhancements to GROUP BY and ORDER BY syntax that are not necessarily SQL standard, but nonetheless allow expressive behavior of grouping and ordering query result sets. For more information refer to: Enhancements to GROUP BY and ORDER BY.

See Also


Advance To: