Overview

Description

The SELECT statement specifies columns that you want to retrieve from a table.

Syntax

SELECT [ <qualifier> ] <select_expressions>

    [ { TO | INTO } <variable_comma_list> ]

    [ FROM <extended_table_specification_comma_list> ]

    [ WHERE <search_condition> ]

    [ GROUP BY {col_name | expr} [ ASC | DESC ],...[ WITH ROLLUP ] ]

    [ HAVING  <search_condition> ]

    [ ORDER BY {col_name | expr} [ ASC | DESC ],... [ FOR <orderby_for_condition> ] ]

    [ LIMIT [offset,] row_count ]

    [ USING INDEX { index name [,index_name,...] | NONE }]

 

<qualifier> ::= ALL | DISTINCT | DISTINCTROW | UNIQUE

 

<select_expressions> ::= * | <expression_comma_list> | *, <expression_comma_list>

 

<extended_table_specification_comma_list> ::=

<table specification> [ {, <table specification> | <join table specification> }... ]

 

<table_specification> ::=

 <single_table_spec> [ <correlation> ] [ WITH (lock_hint) ]|

 <metaclass_specification> [ <correlation> ] |

 <subquery> <correlation> |

 TABLE ( <expression> ) <correlation>

 

<correlation> ::= [ AS ] <identifier> [ ( <identifier_comma_list> ) ]

 

<single_table_spec> ::= [ ONLY ] <table_name> |

                      ALL <table_name> [ EXCEPT <table_name> ]

 

<metaclass_specification> ::= CLASS <class_name>

 

<join_table_specification> ::=

[ INNER | { LEFT | RIGHT } [ OUTER ] ] JOIN <table specification> ON <search condition>

 

<join_table_specification2> ::=

CROSS JOIN <table_specification>

 

lock_hint :

READ UNCOMMITTED

 

<orderby_for_condition> ::=

ORDERBY_NUM() { BETWEEN int AND int } |

    { { = | =< | < | > | >= } int } |

    IN ( int, ...)

• qualifier: A qualifier. It can be omitted. When omitted, it is set to ALL.
  • ALL: Retrieves all records of the table.
  • DISTINCT: Retrieves only records with unique values without allowing duplicates. DISTINCT, DISTINCTROW, and UNIQUE are used interchangeably.
• <select_expression>:
  • *: By using SELECT * statement, you can retrieve all the columns from the table specified in the FROM clause.
  • expression_comma_list: expression can be a path expression (ex.: tbl_name.col_name), variable or table name. All general expressions including arithmetic operations can also be used. Use a comma (,) to separate each expression in the list. You can specify aliases by using the AS keyword for columns or expressions to be queried. Specified aliases are used as column names in GROUP BY, HAVING, ORDER BY, and FOR clauses. The position index of a column is assigned based on the order in which the column was specified. The starting value is 1.
  • As AVG, COUNT, MAX, MIN, or SUM, an aggregate function that manipulates the retrieved data can also be used in the expression. As the aggregate function returns only one result, you cannot specify a general column which has not been grouped by an aggregate function in the SELECT column list.
• table_name. *: Specifies the table name and using * has the same effect as specifying all columns for the given table.
• variable: The data retrieved by the select_expression can be stored in more than one variable.
• [:]identifier: By using the :identifier after TO (or INTO), you can store the data to be retrieved in the ':identifier' variable.
• <single_table_spec>
  • If the superclass name is specified after ONLY keyword, only the superclass is selected, and the subclass which inherits this superclass is excluded.  
  • If the superclass name is specified after ALL keyword, all of the super class and the subclass are selected.
  • The list of subclasses after EXCEPT keyword can be specified for excluding them.

Example 1

The following example shows how to retrieve host countries of the Olympic Games without any duplicates. This example is performed on the olympic table of demodb. The DISTINCT or UNIQUE keyword makes the query result unique. For example, when there are multiple olympic records of which each host_nation value is 'Greece', you can use such keywords to display only one value in the query result.

SELECT DISTINCT host_nation FROM olympic;

  host_nation

======================

  'Australia'

  'Belgium'

  'Canada'

  'Finland'

  'France'

...

Example 2

The following example shows how to define an alias to a column to be queried and sort the result record by using the column alias in the ORDER BY clause. At this time, the number of the result records is limited to 5 by using the LIMIT clause and FOR ORDERBY_NUM().

SELECT host_year as col1, host_nation as col2 FROM olympic ORDER BY col2 LIMIT 5;

         col1  col2

===================================

         2000  'Australia'

         1956  'Australia'

         1920  'Belgium'

         1976  'Canada'

         1948  'England'

 

SELECT CONCAT(host_nation, ', ', host_city) AS host_place FROM olympic

ORDER BY host_place FOR ORDERBY_NUM() BETWEEN 1 AND 5;

  host_place

======================

  'Australia,  Melbourne'

  'Australia,  Sydney'

  'Belgium,  Antwerp'

  'Canada,  Montreal'

  'England,  London'