DQL Statements

Data query language (DQL) is comprised of SELECT statements only. A SELECT statement can be single-table (selecting records from one table only) or multitable (selecting rows from more than one table, usually using some kind of join).

Single table select

The BNF notation for single table select follows:

SELECT [ALL | DISTINCT] 
{[<qualifier>.]<column_name> | * | <expression>} 
    [AS <column_alias>],...
FROM <tablg_or_view_name> | <inline_view> [<table_alias>] 
[WHERE <predicate>]
[GROUP BY [<qualifier>.]<column_name>,... 
    [HAVING <predicate>]]
[ORDER_BY [<qualifier>.]<column_name> | <column_number> 
    [ASC | DESC],...];

Multitable SELECT

A multitable SELECT can be done using either "new" or "old" syntax; also, there are slightly different syntaxes for inner and outer joins.

"New" syntax (inner join)

SELECT [ALL | DISTINCT] 
{[<qualifier>.]<column_name> | * | <expression>} 
    [AS <column_alias>],...
FROM <table_or_view_name> | <inline_view> [<table_alias>] 
   [INNER | NATURAL | CROSS] JOIN
     <table_or_view_name> | <inline_view> [<table_alias>]
   [ON [<qualifier>.]<column_name> 
          <join_condition> 
       [<qualifier>.]<column_name>]
   [[INNER | NATURAL | CROSS] JOIN
     <table_or_view_name> | <inline_view> [<table_alias>]
   [ON [<qualifier>.]<column_name> 
          <join_condition> 
       [<qualifier>.]<column_name>],...]
[WHERE <predicate>]
[GROUP BY [<qualifier>.]<column_name>,... 
    [HAVING <predicate>]]
[ORDER_BY [<qualifier>.]<column_name> | <column_number> 
    [ASC | DESC],...];

"New" syntax (outer join)

SELECT [ALL | DISTINCT] 
{[<qualifier>.]<column_name> | * | <expression>} 
  [AS <column_alias>],...
FROM <table_or_view_name> | <inline_view> [<table_alias>] 
  {[LEFT | RIGHT | FULL [OUTER]} JOIN
     <table_or_view_name> | <inline_view> [<table_alias>] 
  {ON [<qualifier>.]<column_name> 
          <join_condition> 
       [<qualifier>.]<column_name>}
  [{[LEFT | RIGHT | FULL [OUTER]} JOIN
     <table_or_view_name> | <inline_view> [<table_alias>] 
  {ON [<qualifier>.]<column_name> 
          <join_condition> 
       [<qualifier>.]<column_name>},...]
[WHERE <predicate>]
[GROUP BY [<qualifier>.]<column_name>,... 
    [HAVING <predicate>]]
[ORDER_BY [<qualifier>.]<column_name> | <column_number> 
    [ASC | DESC],...];

Note

The "new" syntax for inner and outer joins can be combined in a single query; that is, you might want to join to tables using an inner join and then join the resulting set with another table using an outer join, and so on.

"Old" syntax (inner join)

SELECT [ALL | DISTINCT] 
{[<qualifier>.]<column_name> | * | <expression>} 
    [AS <column_alias>],...
FROM <table_or_view_name> | <inline_view> [<table_alias>] 
[WHERE [<qualifier>.]<column_name> 
           join_condition 
       [<qualifier>.]<column_name>
[AND [<qualifier>.]<column_name>
           join_condition 
     [<qualifier>.]<column_name>],...
[AND <predicate>],...]
[GROUP BY [<qualifier>.]<column_name>,... 
    [HAVING <predicate>]]
[ORDER_BY [<qualifier>.]<column_name> | <column_number> 
    [ASC | DESC],...];

Note

The "old" syntax for outer join is implementation-specific; please see Chapter 9 or refer to vendor documentation.