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>

 

lock_hint :

READ UNCOMMITTED

 

<orderby_for_condition> ::=

ORDERBY_NUM() { BETWEEN int AND int } |

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

    IN ( int, ...)

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 allows only unique values in the query result set. For example, when there are multiple olympic records whose host_nation values are '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'