SQL Bible [Electronic resources]

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],...];

"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],...];